Results 1 to 5 of 5
  1. #1
    quidlat
    Guest

    Combine 2 queries?

    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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 queries?

    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.

  3. #3
    quidlat
    Guest

    Re: Combine 2 queries?

    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

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 queries?

    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.

  5. #5
    quidlat
    Guest

    Re: Combine 2 queries?

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •