help writing query to sum groups

  • Thread starter Thread starter Daniel M
  • Start date Start date
D

Daniel M

I have a table with enteries including date, producttype and qty.

I need to be able to pull all of a specific product type and sum up all the
qty for a specific date range.

problem is the producttypes might not be known. so i dont want to hardcode
the product type in the query. can someone help with this? Thanks.
 
I have a table with enteries including date, producttype and qty.

I need to be able to pull all of a specific product type and sum up all the
qty for a specific date range.

problem is the producttypes might not be known. so i dont want to hardcode
the product type in the query. can someone help with this? Thanks.
Click to expand...

I don't understand the question, it seems. you want to pull all of "a specific
product type" without knowing what that product type might be!?

If you want totals for all product types, just create a totals query. Create a
new query based on the table; select the three fields. Change the default
Group By on the date field to "Where", and put a criterion such as
= [Enter start date:] AND < DateAdd("d", 1, [Enter end date:])
Click to expand...

on the criteria line (this will handle date and time values on the last day).
Leave the default Group By on Producttype, and change it to Sum under qty.
 
Is there a way to point to a table instead of specifying a specific product?
I have a table of products and when the form enters the data to the entry
table it puts a product entry into that table from the product table. I would
like to be able to expand the product table without having to rewrite the
query everytime i add a product. is this possible?

ie product table:
productID 1
Product X

ProductID2
Product Y

Entry Table:
EntryID 1
ProductID 1
Qty 5
Date 1-2-2009

EntryID2
ProductID 1
Qty 6
Date 1-1-2009

I would like to be able to return
DateRange 1-1-2009 to 1-7-2009
ProductID 1
Qty Sum 11

So sometimes i will have both products X and Y sometimes not. I would also
like to expand the product table and allow entries of the new products in the
entry table. The goal is to not report 0 Qty and not have to change the query
everytime i update the product table.

Hope this makes sense. If you have any other suggestions on how to do this i
can look at that too. Thanks.

John W. Vinson said:
I have a table with enteries including date, producttype and qty.

I need to be able to pull all of a specific product type and sum up all the
qty for a specific date range.

problem is the producttypes might not be known. so i dont want to hardcode
the product type in the query. can someone help with this? Thanks.
Click to expand...

I don't understand the question, it seems. you want to pull all of "a specific
product type" without knowing what that product type might be!?

If you want totals for all product types, just create a totals query. Create a
new query based on the table; select the three fields. Change the default
Group By on the date field to "Where", and put a criterion such as
= [Enter start date:] AND < DateAdd("d", 1, [Enter end date:])
Click to expand...

on the criteria line (this will handle date and time values on the last day).
Leave the default Group By on Producttype, and change it to Sum under qty.
Click to expand...
 
Is there a way to point to a table instead of specifying a specific product?
I have a table of products and when the form enters the data to the entry
table it puts a product entry into that table from the product table. I would
like to be able to expand the product table without having to rewrite the
query everytime i add a product. is this possible?
Click to expand...

My suggestion WILL give you all products in the table, regardless of when they
were added. There's nothing in the query as written that would be affected in
any way by your adding a new product.

If you're not seeing that, please open the query in SQL view and post the SQL
text here, together with sample data showing what you're seeing.
 
Sorry i didnt read close enough. i went back and reread the post and got it
working. Thanks again for the help!
 
Back
Top