Subquery to sum values between two dates

[scott216]

I have two tables, tblAcct first has 3 fields: AccountID, StartDate, EndDate
The second, tblData, has the 2 fields: DDate, iValue

I need a query that will sum iValue over the Start and End dates in each record in tblAccount, For example:

tblAcct:
AccountID  StartDate  EndDate
CDD001     01/15/10   02/14/10
CDD002     01/17/10   02/15/10
CDD003     02/05/10   03/03/10
CDD001     02/15/10   03/14/10
CDD002     02/16/10   03/15/10
CDD003     03/04/10   04/03/10

tblData:
DDate        iValue
01/15/10       10 
01/16/10       9 
01/17/10       11
01/18/10       5
01/19/10       7
etc

In the query I need, the first record, Sum_iValue = 259 would be all the iValue numbers from 1/15/10 to 2/14/10 added together. And so on for each record in tblAccount

Output:
AccountID  StartDate  EndDate   Sum_iValue
CDD001     01/15/10   02/14/10     259
CDD002     01/17/10   02/15/10     275
CDD003     02/05/10   03/03/10     289
CDD001     02/15/10   03/14/10     260
CDD002     02/16/10   03/15/10     241
CDD003     03/04/10   04/03/10     301

 

[John Big Booty]

Welcome to the forum.

Have a look at a Totals Query.

 

[scott216]

I took a look at your link to Total Queries, I don't see how that will do what I want.

 

[John Big Booty]

You may need to use a series of cascading queries to achieve the required result.

 

[scott216]

I was hoping I could use a subquery or dsum, but I don't know how.

 

[John Big Booty]

Can you post a copy of your DB ('03 version for preference)?

 

[scott216]

Sure. I don't see a link or an icon on the forum that will let me upload my file. How do I do it?

 

[John Big Booty]

Do a compact and repair on your DB first up, then put it in a zip file. Post a new message in this thread, whilst doing that you will notice a Paper-clip icon at the top of the posting window, click this and follow the bouncing ball from there

 

[scott216]

I attached my file. I didn't see the upload link before because I was in the quick reply screen.

 

[scott216]

I posted the file before I saw your response. Attached is the zip version.

 

[John Big Booty]

Have a look at Query5

 

[scott216]

I just tried it on my real db, it works great. Thanks!

 

[John Big Booty]

Not a problem

 

[dighesp]

Hello, I have a similar problem with my dB. I want to sum upp all the values of quantity between two dates in the query %RFT. I couldnt use dsum and i dont know how to use cascade queries. any advice?

 

[walie]

Hi... Im new here...

I also have the same problem as the above but the solution above doesn't seem to work with my dbase

I have this query

Query1
employee dateworked hours
emp1 1/1/16 10
emp2 1/1/16 20
emp2 1/20/16 10
emp2 2/16/16 10
emp3 1/1/16 20
emp3 2/16/16 20

What I need is this

employee start date end date total hours
emp1 1/1/16 1/30/16 10
emp2 1/1/16 1/30/16 30
emp3 1/ 1/16 1/30/16 20
emp2 2/1/16 2/30/16 10
emp3 2/1/16 2/30/16 20

i hope someone can help me with this