Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Query problem (Access XP)

    Here is a copy of the sql for my query.
    SELECT DISTINCTROW [Brazier only].ItemName, Invoice_Totals.DateTime, Sum(Invoice_Itemized.Quantity) AS [Sum Of Quantity]
    FROM Invoice_Totals INNER JOIN ((Inventory INNER JOIN [Brazier only] ON Inventory.ItemNum = [Brazier only].ItemNum) INNER JOIN Invoice_Itemized ON (Inventory.Store_ID = Invoice_Itemized.Store_ID) AND (Inventory.ItemNum = Invoice_Itemized.ItemNum)) ON (Invoice_Totals.Store_ID = Invoice_Itemized.Store_ID) AND (Invoice_Totals.Invoice_Number = Invoice_Itemized.Invoice_Number)
    GROUP BY [Brazier only].ItemName, Invoice_Totals.DateTime;

    I am trying to get the query to give me the total of Item Names sold on each date. For example "Item one, 6/1/2002 Quantity = 22"

    I have had this problem before and can't seem to figure out what it is that I am doing wrong.

    TIA

    Bret

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

    Re: Date Query problem (Access XP)

    You haven't explained what your problem is.

    Do you get an error when opening the query?
    Does the query return no results?
    Does the query return unexpected/wrong results?

    From looking at the SQL, I have the following question - it stems from ignorance of your situation, so it might be stupid: why do you have Store_ID in both Invoice_Totals and Invoice_Itemized?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Query problem (Access XP)

    Wow, I was tired last night. The problem is this query does not return the results as I would like. I want to know a total of each Item sold on each day. I get a listing of each item individually sold on each day. It does not give me the total of all that item sold on each date.
    This is still confusing so I will give an example. If I sell ten apples one at a time throughout the day on 6/20/2002 I want the query to tell me that "6/20/2002, 10, Apples" What I presently get is "6/20/2002, 1, Apples" then "6/20/2002, 1, Appples" so on until all ten are displayed on separate lines.

    I hope I have made this clearer.

    Bret

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

    Re: Date Query problem (Access XP)

    Well, of course, if you have "Apples" (2 p's) in some records and "Appples" (3 p's) in other records, they will be displayed in separate rows in a GroupBy query. But I suppose that is just a typo.

    I can't see anything wrong with your query in itself - with the reservation I stated in my previous post that I don't understand why StoreID is in both Invoice_Totals and Invoice_Itemized. The query should group by item number and date.

    Perhaps, if you can post a stripped-down version of your database (just the relevant tables and query, with just a few records, sensitive information removed), other Loungers can see what goes wrong.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Query problem (Access XP)

    If the date/time field (Invoice_Totals.DateTime) is storing the date AND time, then the query results will show each total as "1", since while many invoices may be same date, it's highly unlikely any two are same exact time. You might want to try using DateValue function in SELECT clause:
    <pre>DateValue(Invoice_Totals.DateTime) AS [Invoice Date]</pre>

    Use same expression in GROUP BY clause and ORDER BY clause, if applicable (i.e., DateValue(Invoice_Totals.DateTime), not the alias).

    The DateValue function will return the date component only of the date/time field and your query results will be grouped accordingly, with totals for each day.

    HTH

Posting Permissions

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