Competent Performer
Windows XP 
Access 2003
Hi, I have a problem.
I have a table with 2 columns named: InvtyDate(date type) and InvtyQty (currency type). For example, I have dates ranging from January 1, 2008 to June 30, 2008 and its daily InvtyQty is 10.
Question: How do I make a query that will sum all InvtyQty from January 1 to May 30, 2008 and present all data of invtyqty from June 1 to June 30 2008?
This is the result format I needed. Can someone help me please. Thanks.
Last edited by marianne; 03-29-2009 at 08:29 PM. Reason: SOLVED
Administrator
Windows 10 Access 2013 32bit
If this is for a report then you can use a query to return the June records and a DSum() for the rest.
Competent Performer
Windows XP Access 2003
I'm just learning still for queries ruralguy. yes this is for reporting pusposes. but I would like to make it first on the query. I would appreciate if you can give me the query solution so I can study it and use on the future. actually I need the answer now so I can finish my assignment for tomorrow. i dont know how to use what you said.
Administrator
Windows 10 Access 2013 32bit
I see you've posted this in other forums with a similar response. The query builder can assist building the query the has the data criteria and this link shows how to use the Domain functions. All of the Domain functions use the same syntax and all of the parameters are strings.
Competent Performer
Windows XP Access 2003
yes I have posted this on other forums too because I need to find answers. On the link you have provided, I have also studied them but it does not solve my problem. I just want a query that will summarize all the InvtyQty from January 1 to May 31, 2008 and show all the records of the current month which is June 2008. Maybe you can give me the sql code for the query.
Administrator
Windows 10 Access 2013 32bit
Why does it have to be in one query? It would take maybe 15 minutes to put together a report that you need using a query and a DSum().
Administrator
Windows 10 Access 2013 32bit
BTW Marianne, I will be glad to help you complete the project but I will not just give you the answer. If that is not satisfactory then you better look elsewhere.
Competent Performer
Windows XP Access 2003
really? what would be your suggestion?
my intention why it should be in a query form because I need to export it later to excel file format.
Administrator
Windows 10 Access 2013 32bit
Describe to me what you believe will be the first two records, field by field.
Competent Performer
Windows XP Access 2003
it will look like this if this is a query view:
MyDate InvtyQty
------ 1,500.00 (this is for sum of invtyqty from 1/1/08-5/31/08
6/1/08 10.00
6/2/08 10.00
6/3/08 10.00
up to
6/30/08 10.00
that is the result in the query that I would like to attain ruralguy!
Administrator
Windows 10 Access 2013 32bit
Do you see the problem? Queries return records. Each record has the same number of fields. Your first two records:
are not the same.1,500.00 (this is for sum of invtyqty from 1/1/08-5/31/08
6/1/08 10.00
Competent Performer
Windows XP Access 2003
do you have a suggestion?
Administrator
Windows 10 Access 2013 32bit
We have to get past the "Export it to Excel in a query" first. Do you see that it has to be two separate operations? That can be done in a report but not in a query. If the Sum part had two field that line up with your June records then you could put them together with a Union.
Administrator
Windows 10 Access 2013 32bit
Did you give up?
Administrator
Windows 10 Access 2013 32bit
Probably just went to bed since I believe she is in Australia and it was the middle of the night. +14 hours from me.
Posting Permissions
Reply With Quote