Combine 2 queries?

Topic

New Reply

Hi guys!

I was wondering if someone can help me out on this. Ok, I have two queries presently. One tracks people who have not reported their monthly hours. This means that the query outputs people who do not exist in the table called MonthlyReporting (because they have not reported at all). In this select query, Ive created a column with a sql code that equates ssn of the primary table with the ssn of the MonthlyReporting table. This column also has a criteria of FALSE (since we want ssn’s in the primary table with no matching ssn in the MonthlyReporting table).

My second query basically takes a count of a person’s months for which he has reported in the MonthlyReporting table. This is a select query as well, whose criteria under the monthcount column is left up to the user to input (i.e. user is asked to enter number). For example, if user enters 7, this query will output people who have 7 months or less of reporting.

The problem with this is that it cannot show anyone with 0 months reported, since obviously these people have yet to show up in the MonthlyReporting table.

So my question is, and maybe I have just made this more complicated: How can I combine both queries so that a query can output people with a specified number of months or less including 0. So for example, if the user enters 7, the query will display people who have reported 7 months or less, INCLUDING 0 months.

Thanks so much, you guys! I love you! *sniff, sniff.

quiddy

Reply | Quote

Replies

Have you tried a union query. Create two queries from your two tables. The queries must have the same fields in each. You can then join the two queries with a union query.

Reply | Quote

Thanks, but how can the two queries have similar fields?
One query checks for unmatched SSN’s between two tables, while the other checks for unique month count in one table.

Is there a way to make each query have similar fields?

Thanks in advance.
quiddy

Reply | Quote

Copy your query that counts up the number of reports people have made, but leave out the selection criteria for number of reports. This will give you the full count for every reporter. Next, create a new query that will match up your Primary SSN table to the “full count” query for unmatched records; set their count to 0. Create a union query to combine the “full count” query and the zero-report query to give the reporting count for every person. Use your report count selection criteria on this query.

Not a whole lot of finesse, but it should do the job.

Reply | Quote

Hi,
I have one question tho:
How can I check for unmatched ssn and at the same time, update a field to 0? Do I have to make a new field in this new query called reportcount? and if so, is this query then an update query?
I am kind of confused when you said, “set their counts to 0.”

Thanks
Chris

Reply | Quote