SQL history

[ben.oates]

Hi all,

I am trying to get an output from Access and I have never seen SQL that relates to what I am trying to do.

Basically, I have a table of information where each object is associated with two dates: Received and Completed.

I want an output that displays a count of received and a count of completed on a given day, but I have no idea how to go about it.

For example:
Item Received Completed
Item 1 12/01/2009 16/01/2009
Item 2 12/01/2009 15/01/2009
Item 3 13/01/2009 16/01/2009
Item 4 14/01/2009 17/01/2009

Would show

Date Received Completed
12/01/2009 2 0
13/01/2009 1 0
14/01/2009 1 0
15/01/2009 0 1
16/01/2009 0 2
17/01/2009 0 1

I tried a pivot table but that came out counting the number of items that had completed dates on the received date as received date was the only field setting the increment - i.e. in this case:

Date Received Completed
12/01/2009 2 2
13/01/2009 1 1
14/01/2009 1 1

Any suggestions, greatly appreciated.

[OBP]

Interesting question.
How about if you use Total and Group By both Dates but have 2 further Columns with Headings like
exp1:iif(isnull([Received],0,1)
exp2:iif(isnull([Completed],0,1)

which should give a 1 for a date and 0 for not date and when set "Sum" should provide your example

[ben.oates]

But how would I get it to return a list of dates in the left column? I don't really understand what you mean by "use total"

[OBP]

Query in design View, Main Menu>View>Totals
Group By the Dates and Sum the 2 new columns