The table has the following columns/fields:
quantity, itemname, itemidentifier, createdon;

This is a table of shopping cart dates.
Each row is filled with the quantity purchased (usually 1), the name of the product, the itemidentifier(part no.) and the date the order was placed. What I'm trying to figure out is how many of a certain type of product was purchased during a particular date. I want to tell the boss that between this date and that date x amount of this product was bought on line. But I don't want to have to name the product outright in my select. Just want a list or something....don't know really...sorry the vagueness.

So the item name would be "Shopping Cart" and the item identifier would be a number of some type?

First thing we need to do is break out the itemname fromt this table and make a new one.

tblItemsWeBuy
Item# (number)
ItemName (shopping carts, etc.)

Put all your stuff you buy in this one.

With me so far>>

So I'll create a new table, name it ItemsWeBuy and populate it with only the name of the product and it's identifier (part no.?)

Yes. Your second table will contain only the fields.

ItemItentifier (under type you will choose Lookup Wizard and use the first table)
Quantity (number, set its default value to 1 since you usually only enter 1)
CreatedOn (under type choose date/time)

Got that?

Bingo! Thanks. I put the fields in a new table and just ask it to count by the quantity and group by the item number..Big help. Thank you again.

I forgot to mention that when you create that lookup field you will choose tblItemsWeBuy and choose the fields...Item# and ItemName in that order. I should have told you also to set the Item# in tblItemsWeBuy to be the primary key.

Now...at that last step you will get a kinda "look" at your choices for that lookup field. You should only see the Item name and the little box that says Hide Key Column (Recommended) should be checked.

Hope your not having too much trouble...I'm a lousy teacher sometimes.

Well, glad to see you got what you needed. I wasn't aware of how far you were with your query and report.

Anyway, with the foreign key in your second table you can do a whole lot more. Now you can add new items to tblItemsWeBuy and have them available automatically to be selected in your other table. You can create queries to just work on certain item names and even offer a choice of what item you want a report on instead of reporting all the items on one report.

I'm still not sure what you said..but whatever you did it helped me get the answer!!
Thanks again.

Most of the time I have no problem with words. Fatique setting in. Anyway, where I was going with that was a form with a drop down combo box that allows you to select the name of the item you want the report on. The choice becomes part of your query and a report is based off of that query.

You don't need to create a new table. Just use a totals query:

Select itemname, itemidentifier, Sum(quantity) AS QuantitySum
From YourTable
Where createdon Between [Enter Start Date] and [Enter End Date]
Group by ItemName, ItemIdentifier;

Pat: Your advice on table structure has been extremely valuable to me. I was trying to to "follow your example" and give the option of using a combo box later on that looked up values based on the new table.

I hope I'm starting to get a better grip on structure.

Pat...you hit it..Wow. Great Job. Thank you. It was very smooth. I didn't know AS QuantitySum...that's sweet.

Appreciate it.
Thanks.

jwindon, you can use a combobox for criteria entry without creating a table to hold the data you are trying to sum. For date entry, either text boxes with masks or the activeX calendar control work better than comboboxes.

Yes Pat, but what about a combo asking for the item name? That is the one I was going for. The date thing I know to be best with ASK options.

jwindon, I think we're having a semantics problem. In an Order Entry System there would already be a table that contains products with their names. You would not need to create this table by processing OrderDetails. It would have been the table that populated the combobox that was used when the order was created. If it is necessary to calculate the count for specific products, you would use the Product table to populate the combobox. You were correct in suggesting a combobox for this but a table that can be used as its rowsource should already exist.