Query with multiple date fields

[esi]

Have a table with the following fields:

Log#(primary key)
Reason
ReceiveDate
1stAttemptDate
2ndAttemptDate
LogStatus(2 options, successful/unsuccessful)
CloseDate

I need to develop a query that provides me with the following info:

Reason Date Total Recvd Total 1st Att Total 2nd Att
Total Log Status(break out by successful/unsuccessful)
Ex. Overdue 12/1/03 3 3 2
1

My [date] field in the query refers to a table [Date] which is populated
with dates from 1/1/03 thru 12/31/04.

Not sure how to setup using multiple date fields where I just can't base my
activity from 1 date field.

 

[Duane Hookom]

Start by normalizing your data into a table with a single date field. This
can be done by changing your table structure or creating a union query like:
SELECT Log#, Reason, "Recvd" as DateType, ReceivedDate as TheDate
FROM tblNoName
UNION
SELECT Log#, Reason, "1stAtt", 1stAttemptDate
FROM tblNoName
UNION
etc.

Then you can use the results of the union query to create a crosstab query.