Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Loop (2003)

    I have a query A that has a query B and table joined with a field called Suppliername from the table having criteria IsNull. Query A will only select rows from the table where the field Suppliername is not included in the query B. I have a date field in query B that is sorted in ascending order and I would now like every time a new month occurs for query A to list any rows from the table where the Suppliername is not in query B. Can anyone tell me how to perform this loop?

    Thanks,

    Kent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    I'm not sure I understand what you want. Do you mean that you want query A to list records for the current month only? Or something else?

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    I was afraid I wasn't very clear. Query B may have 10 records for Jan. and 10 for Feb. etc. As query A is going through the records of query B I want it to list which Suppliernames from the table aren't listed in query B each time the month changes. Probably still not very clear. Let me know.

    Thanks,

    Kent

  4. #4
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    The way I have it set up right now it goes through all of query B and lists the Suppliernames from the table that aren't in query B, but I want it to do that each time the month changes instead of the whole query B.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    What do you mean by "it goes through all of query B"? What is "it"?

  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    Sorry. Query A goes through query B.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    So would you like to see the records for the current month? Or for the previous month? To be more specific, which records would you like to see if you ran the query today (25 January 2006)?
    a) Those for January 2006
    [img]/forums/images/smilies/cool.gif[/img] Those for December 2005
    c) Something else (please specify)

  8. #8
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    Think I better start over. I have a table A that suppliers upload data to each month. There is a field named Supplier in this table. I have table B that list all the suppliers we have with a field named Supplier. I want to run a report that will show me who has not uploaded their data each month, which I can do fine now if I only want one month, but I want to enter a range of months on my form A and be able to print a page for each month showing who has not uploaded yet for each month. Query B uses form A to get the date range then lists all the uploaded data for that date range. Currently Query A compares table
    B with query B to see which supplier has not upload their data and returns records from table B showing the suppliers names. Is there a way to have Query A go through table B each time there is a month change in query B and retrieve the suppliers who have not uploaded. If that makes total nonsense to you I won't be offended and will keep thinking it through.

    Thanks for your help.

    Kent

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    You can do this using a series of queries. In the attached database, I created 4 queries:

    qrtTableA adds calculated fields year, month, and a date field FirstOfMonth
    qryMonths returns the unique FirstOfMonth values
    qryCombinations returns all possible FirstOfMonth - Supplier combinations
    qryMissing returns the Suppliers that do not occur in each month. It uses a date range from the form frmDateRange.

    Note: this is just a rough demo, I didn't build any error checking into the code behind the command button on the form. And in a "real" database, you wouldn't open a query directly.

  10. #10
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    Wow! This is what I was looking for. Thanks so much!!

    Kent

  11. #11
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    Well I thought it was going to work. I set up my queries like you did but my qryMonth has a record for every record from qryTableA and my qryCombination has a record for every record in qryMonth. Any ideas why they don't give just the unique records like yours did? Yours did exactly what I'm looking for.

    Thanks,

    Kent

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    Open qryMonth in design view.
    Click in an empty part of the upper half of the query window.
    Activate the Properties window.
    Set the Unique Values property to Yes.
    Save the query.

  13. #13
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    That did the trick for that. All queries seem to be working now accept the qrymissing query. It shows no data and I've tried several different things. I know that suppliers are missing from certain months but they are not showing in the qrymissing. I'll keep working on it.

    Thanks,
    Kent

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Loop (2003)

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  15. #15
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Loop (2003)

    Here is a copy. Thanks for looking at it.

    Kent

Page 1 of 2 12 LastLast

Posting Permissions

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