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

    Select Query and date (Access XP)

    I am trying to create a select query that will give me the sum of items sold for each day. The items are stored by date and time. Whenever I run the query I get an individual listing of each transaction not a total of all items sold each day. Below is a copy of the SQL.

    thanks,
    Bret

    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, [Brazier only].ItemNum;

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select Query and date (Access XP)

    It sounds like each transaction has a date time stamp in the Datetime field . Grouping requires values to be the same.
    Try replacinjg the last line with this.

    GROUP BY [Brazier only].ItemName, format(Invoice_Totals.DateTime,"mm/dd/yyyy") as InvDate, [Brazier only].ItemNum
    Regards
    John



  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Select Query and date (Access XP)

    Besides the time stamp, check the item number as well.

    As John pointed out, the time stamp could be stamping data with a time formatted as Date, Hours, Minutes, Seconds. Thus, the grouping would never work as the time stamp will always be different due to the Seconds assigned to each piece of data.

    You might also want to take a look at your item number as well as each item name may contain a different item number. HTH
    Regards,

    Gary
    (It's been a while!)

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

    Re: Select Query and date (Access XP)

    Thanks John, Access doesn't like the word "as" in the change you told me to make.

    Bret

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

    Re: Select Query and date (Access XP)

    You and John are correct, the is a time stamp on each transaction. Having tried John's solution I get an error on the word "as" in the last line he wanted me to try. This has been a big pain for me, I would really like to be able to manipulate the data that I get from this database more. It is a Point of Sale program that we use in our restaurant.

    Thanks for the help.

    Bret

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select Query and date (Access XP)

    Sorry Bret
    My mistake. The calculated field is in the wrong place. The calculated field InvDate needs to be defined in the SELECT part of the query, then used in the Group BY line.

    SELECT DISTINCTROW [Brazier only].ItemName, format(Invoice_Totals.DateTime,"mm/dd/yyyy") as InvDate, 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, InvDate, [Brazier only].ItemNum;
    Regards
    John



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

    Re: Select Query and date (Access XP)

    Now I have an "undefined format" error. Sorry this is such a pain. But this is why I haven't been able to figure out these queries. I really appreciate the help.

    Bret

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Select Query and date (Access XP)

    Bret,

    Two items.

    1. Change "as InvDate" to "as [InvDate]" - Dont actually include the quotations.
    2. I don't see why you are using the Select Distinct Row functionality since you are grouping your data. Perhaps there is something happening that I dont see.

    HTH

    Post immediately edited.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Select Query and date (Access XP)

    Bret

    I have actually tested this one !

    SELECT DISTINCTROW [Brazier only].ItemName, format([Invoice_Totals].[DateTime],"mm/dd/yyyy") as InvDate,
    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, format([Invoice_Totals].[DateTime],"mm/dd/yyyy") as InvDate, [Brazier only].ItemNum;

    I can't reproduce the "endefined format" error , so i am not sure what's going on. Try to make a simple select query on Invoice_totals to get the syntax correct for the calculated field. Here is mine:
    Attached Images Attached Images
    Regards
    John



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

    Re: Select Query and date (Access XP)

    If the date/time field includes time values, recommend use DateValue function to return date value only and group on this date value. Simple example SQL statement:

    SELECT DateValue([DateField]) AS [Order Date], Sum(Table3.Amt) AS SumOfAmt
    FROM Table3
    GROUP BY DateValue([DateField])
    ORDER BY DateValue([DateField]);

    In example DateField is date/time field, Amt is a currency field, and Table3 is name of table.

    HTH

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

    Re: Select Query and date (Access XP)

    Yeehaw, the DateValue seems to have worked it's magic. Thanks to everyone for your help on this. Phew, what a struggle this little item has been for me.

    Bret <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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