Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: query query

  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: query query

    I have a query which shows the number of items sold for the current and previous month for sale items. The query is currently made up of three queries: one which finds the number of items sold for the current month, one which finds the number of items sold for the previous month and one which joins the two together. I can then see the current and previous months figures for any month.

    The problem is the query only shows the items which appear in both months. If a new item is sold in month two but it wasn't sold in month one then it won't appear.

    Has anyone got a solution to this problem please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a full outer join: items sold in both months, plus items sold in first month but not in second, plus items sold in second month but not in first

    since access does not support full outer join syntax, you can get the same results with: (items sold in both months, plus items sold in first month but not in second) UNION ALL (items sold in second month but not in first)

    the first part of the UNION ALL is a left outer join and the second part is a right outer join where you exclude the matches

    you could also get the same results with (left outer join) UNION (right outer join) because here you are relying on UNION to remove the matched rows, which would be returned by both the left and right outer joins

    if this doesn't make sense, show me your existing query, and i'll rewrite it for you

    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2003
    Posts
    6
    Thanks for the help, I kind of follow what your saying but I'm struggling to implement it. Here's the code:

    SELECT tblMonth.MonthNumber, tblItem.[Item Number], tblItem.Description, qryPrevious.Cost, qryCurrent.Cost
    FROM ((tblMonth INNER JOIN qryCurrent ON tblMonth.MonthNumber = qryCurrent.MonthNumber) INNER JOIN qryPrevious ON tblMonth.MonthNumber = qryPrevious.MonthNumber) INNER JOIN tblItem ON (tblItem.[Item Number] = qryPrevious.[Item Number]) AND (qryCurrent.[Item Number] = tblItem.[Item Number]);

    Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    uh oh

    it looks like you have a table of months, and you want a line for every month

    (which makes "current/previous" look weird, since you're then repeating data, but maybe that's just me)

    what you need is a left outer join from the month table to your data

    this would have to go into both qrycurrent and qryprevious

    then you do the inner join on the results

    so i'd have to see both qrycurrent and qryprevious

    are you sure you need to lay it out like that, repeating current/previous?

    or are you restricting the queries by selecting only one month each time you run them? because in that case i can't see why you'd need a month table

  5. #5
    Join Date
    May 2003
    Posts
    6
    It's probably the wrong way of doing things, just my attempt at trying to solve the problem.

    I basically have a table called month which I store fields about the month such as comments etc... At the end of the month I want to do a report which shows the information for the month table, and lists items sold in this month compared to last month e.g.

    Month No: 2

    Comments: sdfdsfsdf

    Item 1: Previous: 100, Current: 150
    Item 2: Previous: 200, Current: 200
    Item 3: Previous: 300, Current: 350

    Maybe you can suggest an effective solution for doing this in a query?

    thanks.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the month number table, with comments, is a good design

    how do you handle comparing january to december of the previous year?

  7. #7
    Join Date
    May 2003
    Posts
    6
    I have a previous month column in the month table, to get round this problem.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you show your month table entries (minus comments, or whatever), for nov 2002 through may 2003

  9. #9
    Join Date
    May 2003
    Posts
    5
    Table Month

    Current, Previous
    11/2002, 10/2002
    12/2002, 11/2002
    01/2003, 12/2002
    02/0223, 01/2003 etc...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, with your existing qryCurrent and qryPrevious queries, the only thing i can suggest is that you take your last query, and change the inner joins to left outer:
    Code:
    SELECT tblMonth.MonthNumber
         , tblItem.[Item Number]
         , tblItem.Description
         , qryPrevious.Cost
         , qryCurrent.Cost
      FROM (
           (
           tblMonth 
    LEFT OUTER
      JOIN qryCurrent 
        ON tblMonth.MonthNumber = qryCurrent.MonthNumber
           ) 
    LEFT OUTER
      JOIN qryPrevious 
        ON tblMonth.MonthNumber = qryPrevious.MonthNumber
           ) 
    LEFT OUTER
      JOIN tblItem 
        ON qryCurrent.[Item Number]  = tblItem.[Item Number]
       AND qryPrevious.[Item Number] = tblItem.[Item Number]
    however, i have a feeling that this isn't going to work because qryCurrent and qryPrevious sometimes -- obviously -- don't find a particular item, and therefore the last join, to get the description, is not going to match the item, because it's built so that it assumes there's a match for both current and previous

    in other words, you need to tear down qryCurrent and qryPrevious and/or join the month table to the item table first (this may need a cross join)


    rudy

  11. #11
    Join Date
    May 2003
    Posts
    5
    Rudy,

    Thanks for your help.

    Thats more a less the stage i got to, where the results of the query are dependant upon a match of an item between qryPrevious and qryCurrent.

    What would be the way to scrap qryPrevious and qryCurrent and do the sql where if the item was in the previous or current month but had no match in the corresponding month to show the total as zero (0) rather than just ommiting the item?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that depends on how those queries work, and i haven't seem them yet, eh

    it depends on whether you want to see all items, or just those items which had something in either current or previous or both

  13. #13
    Join Date
    May 2003
    Posts
    5
    Rudy,

    I just want to see items which appear in the current or previous month. Not every item.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, show the qryCurrent and qryPrevious

  15. #15
    Join Date
    May 2003
    Posts
    5
    qryCurrent:

    SELECT [tblMonth].[MonthNumber], [tblItem].[Item Number], [tblItem].[Description], [tblMonthDetail].[Cost]
    FROM tblMonth INNER JOIN (tblItem INNER JOIN tblMonthDetail ON [tblItem].[Item Number]=[tblMonthDetail].[Item]) ON [tblMonth].[MonthNumber]=[tblMonthDetail].[MonthNumber];

    qryPrevious:

    SELECT tblMonth.MonthNumber, tblMonth.PreviousMonthNumber, tblItem.[Item Number], tblItem.Description, tblMonthDetail.Cost
    FROM tblItem INNER JOIN (tblMonth INNER JOIN tblMonthDetail ON tblMonth.PreviousMonthNumber = tblMonthDetail.MonthNumber) ON tblItem.[Item Number] = tblMonthDetail.Item;


    A basic version of the database in access 2000 is attatched
    Attached Files Attached Files

Posting Permissions

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