Questions with date append on using start and end date

[MC]

Here's my dilemna, I have two tables:

1st table:

[Rec_date], [Num_recieved]

2nd table

[Comp_date],[Num_completed]

I need to know if complete date doesn't equal rec date the # of pending
items per day. So it would create a table like this. if I recieved 100
items on 2/1/06 but didn't do it until 2/3/06 I need it to look like
this

date carryover rec completed items pending
2/1/06 0 100 0 = 100
2/2/06 100 10 10 = 100
2/3/06 100 50 150 = 0

any help would be appreciated

 

[Marshall Barton]

Here's my dilemna, I have two tables:

1st table:

[Rec_date], [Num_recieved]

2nd table

[Comp_date],[Num_completed]

I need to know if complete date doesn't equal rec date the # of pending
items per day. So it would create a table like this. if I recieved 100
items on 2/1/06 but didn't do it until 2/3/06 I need it to look like
this

date carryover rec completed items pending
2/1/06 0 100 0 = 100
2/2/06 100 10 10 = 100
2/3/06 100 50 150 = 0

I'm not sure I completely understand what you want, but try
this kind of query:

SELECT R.RecDate,
Nz((SELECT Sum(XR.NumRecieved - XC.NumCompleted)
FROM tblRecieved As XR INNER JOIN tblCompleted AS XC
ON XR.RecDate = XC.CompDate
WHERE XR.RecDate < R.RecDate), 0) As CarryOver,
R.NumRecieved,
C.NumCompleted,
CarryOver + R.NumRecieved - C.NumCompleted As Pending
FROM tblRecieve As R INNER JOIN tblCompleted AS C
ON R.RecDate = C.CompDate