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

    Query Design - dates (2000)

    Hello all,

    I have what I think is a pretty simple question. I want to create a query that will give me a quantity count for each date. I have these fields, datetime, Item Name, Quantity.

    I would like the query to tell me how many of each item name I have sold on each date. I cannot figure out how to get the query to give me the "sum" of each item name on date X.

    TIA

    Bret

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    Make a query with the 3 fields.
    In the design view, on the toolbar click on Totals button
    In the total row enter Group By for the DateTime field and Item Name field, Enter Sum for the quantity field
    Francois

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

    Re: Query Design - dates (2000)

    Thanks for the help but it did not help me. I get the same results whether I have the Sum in the totals field or Group By.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    Maybe you have time in your datetime field.
    Try fore the datetime field:
    SalesDate :Format([datetime],"mm/dd/yyyy")
    Francois

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

    Re: Query Design - dates (2000)

    You are correct it does have time in that field also. When I try to format for "short date" I either get errors or I get "mm/dd/yyyy" appearing in my query. What am I doing wrong.

    Bret

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    If you enter SalesDate :Format([datetime],"mm/dd/yyyy") in the field, what kind of error do you get?
    Is it "The expression you entered contains invalid syntax." and the cursor stand on the , when you click OK ?
    Then you have your internaltional settings set to , for decimal separator. No problem, replace the , with a ; in the field
    SalesDate :Format([datetime] ; "mm/dd/yyyy")
    Francois

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

    Re: Query Design - dates (2000)

    First I tried typing it as you describe and get this error, so I tried copy and paste and I get the same error.
    "You omitted an operand or operator, you entered an invalid character or comma, you entered text without surrounding it with quotation marks.

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

    Re: Query Design - dates (2000)

    Okay, I have it working now. Thanks for all the help. The ; (semi colon) was causing the problem. I replaced it with a , (comma) and all is well now.

    Thanks for all the help.

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

    Re: Query Design - dates (2000)

    Thanks again for all your help. This is great to be able to have this information readily available.
    I did get it to work, by removing the ; (semi colon) and replacing it with a , (comma).

    I have another question about a query. I have this database with all these items I sell (over 300) but I know that there are just certain items that are my main sellers. Is there a way to create a query that will tell me only the items that I am concerned with? By this I mean, "Can I specify what 30 items I am interested in and have it tell me how many of only those 30 items I have sold?" These 30 items are not necessarily the top 30 sellers in numbers.

    I guess another way to say this would be to ask, "Can I create a query that returns the quantity sold on the same 30 items? Even when those same 30 items aren't top sellers?"

    This is based on food sales at a restaurant. I want to no how many of special #1, #2, #3, large drinks, coffee, pie ala mode, etc. that I sell everyday. How do I specify the 30 items?

    TIA

    Bret

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    Bret,

    In your item table, you can add a yes/no field. Check the field for the 30 items you want.
    In the query, add this yes/no field, in the Total line enter Where and on the criteria line enter Yes. This column will not be visible in the data view of the query.
    This is a yes/no solution. The 30 items and nothing else.
    You could also add a number field or text field and enter different numbers or categories. In the criteria of the query, you could the enter the item with the wanted number or ategory.

    Hope this help
    Francois

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

    Re: Query Design - dates (2000)

    This table is linked to a table in a commercial piece of software. When I tried to add the Yes/No field I was given the message that changes cannot be made to a linked table. Is there some other way for me to do this?

    Bret

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    Did your table have an ID or Unique key or are the Item Name Unique ?
    Make a table with 1 field : the same as the unique field in the item table.
    Enter the 30 items you want, the same data as in the item table.
    In the query, add the new table, make relation whit the the key field is equal to the field of the new table. In the Join properties check the button show all records from the new table and only those records from Item table where the joined fields are equal.
    Francois

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Design - dates (2000)

    Little mistake.
    Forget about unique or key.
    Just make a table with the same field as the item name and enter you 30 items like they are in the item table( 30 records). Go on with the query as in the previous post.
    Francois

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

    Re: Query Design - dates (2000)

    Wow, is this nice! Thank you so much. This has been a frustration for me for a couple of years now. I have all of this information and no way to get to it. The reports built into the software are okay but they only allowed me to look at on item at a time and in the programmers idea of how they should be viewed. This is what I had originally bougt Access for, so that I could manipulate data as I needed.

    Thank you so much of the help.

    Bret

Posting Permissions

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