2 queries, 1 report

[Pushkinwow]

I have two queries:

One pulls information based on SIN and only returns one record.
The other pulls information by departent and pulls multiple records.

I need to compare all the departmental records with the one SIN record. In each case the user inputs the SIN or the department number.

How to I produce a report that allows me to do this? I assume there is something with crosstab queries etc but dont quite know the direction I should looking at.

Thx

 

[Abulafia]

Can you give more information on the two queries (I have no idea what an SIN is!).

Detail the design of the queries.
Are you looking to join them and find only matching records?

 

[Pushkinwow]

Forgot that everyone isnt Canadian

A SIN is a sociall insurance number (9 digits). The first query allows the user to input a SIN and find out information about a person.

The second query allows the user to input the department number and get the exact same information about everyone in the department.

Now I have figured out that it is possible to use both of these queries as sources in a report but the problem is how it is printed out.

It sorts through the records in the detail section of the report. First it looks for a record from the SIN query and then one from the Department query. So at the end of the report it ends up returning:

the Sin record
a department record
nothing (since there is only one SIN record)
a department record

These last two steps are repeated until there are no more records from the department query to be displayed. Now I need to do a calculation comparing all of the department records with all of the one SIN record. I figure I just need to find someway to repeat that record all the way through the report (some way of making it static or absolute) so then when I do a calculation it can refer the matching SIN record.

That may be a little confusing but let me know if you need more clarifcation.