Complex CrossTab Query

[Paul]

Hi,
I like to created a weekly Statistics below:
week of: 04/27/03 to 05/03/03

Prior Current
Unit Backlog Received Processed Backlog
------- -------- --------- -------
A 1 2 2 1
B 0 5 3 2
C 0 0 0 0

Definition:
The Prior backlog are the requests with Created date
before 04/27/03 and the Status is Not Finished.

The Received are the requests with Created date between
04/27/03 and 5/03/03

The Processed are the requests with Completed date between
04/27/03 and 5/03/03

The Current Backlog are the requests with Created date
before 05/03/03 and the Status is Not Finished.

There are two tables involved, how can I created a
crossTab query like that? Or I tried to wrote 4 SQL
scripts, each will generate the rows and one column, but I
don't know how to combine them? When using Union All, it
only come one column.

Thanks for your kindly advise.

 

[Duane Hookom]

Seems like this is my day for answering this question. It would help to know
your current data structure. Do you have fields for each of the date types?
Please come back with your TABLE and FIELD names. I think you would need to
create four sections to a union query. Each section would create one
Statistic Category. You could then make a crosstab based on the union query.

 

[Paul]

Duane,
Look like Wednesday is a good day. There are two
tables involved:
Table A: ID, Unit, Created Date
Table B: ID, Completed Date

Let me tried the 1st Stat. category "Prior Backlog", the
SQL :
SELECT A.Unit, count(*)
FROM A,B
WHERE A.Created date <#04/27/03# and B.Completed date is
null
Group By A.Unit

Question 1:
With this SQL script, I got rows prior backlog > 0, if
backlog=0, then it won't display the unit?
Question 2:
After construct 4 sections, how do you union into one? It
seems after I union, it give me one long category? And
how you created a crosstab based on after union?
Thanks a lot.

 

[Duane Hookom]

Are the two ID fields having a relationship? If so, your query should have a
join in it some place. Your first query might be something like
SELECT A.Unit, "Prior Backlog" as Status, Count(*) as Qty
FROM A LEFT JOIN B on A.ID = B.ID
WHERE A.[Created Date]<#04/27/03# AND B.[Completed Date] Is Null
GROUP BY Unit, "Prior Backlog";
I might have the JOIN wrong but you need to select all records from A.
You could create the other three queries similarly. Then join them in a
union query. This would create a query with the fields:
Unit, Status, Qty
Take the final union query and create a crosstab where Unit is the Row
Heading, Status is the Column Heading, and Qty is the Value.