Crosstab query?

[aageorge]

I have a query that gives me the following output(Thanks Norris68):

Date A B C D
8/10 100 100 100 100
8/11 50 50 50 50

I am trying to use a crosstab query to get the following output:

Part 8/10 8/11
A 100 50
B 100 50
C 100 50
D 100 50

The reason I want to this I have a lot of parts and a maximum of 7 dates. How do I go about doing this?

 

[LarryDeLaruelle]

aageorge:

Make the date the column header and part the row header.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[dhookom]

You would first need to normalize the initial query into records like:
Date Part Qty
8/10 A 100
8/10 B 100
...
You could do this with a UNION query. Then take these results and build your crosstab with Part as Row Heading, Date as the Column Heading, and Sum of Qty as the Value.

Duane
MS Access MVP

 

[LarryDeLaruelle]

Duane:

Why would aageorge have to do an inital query for normalization? Wouldn't the crosstab provide the summarization he needs? Or, am I missing something?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[dhookom]

aageorge hasn't provided his REAL table structure, just the result of a query (which might be a crosstab). If the real table structure has A,B,C,.. as field names then you can't transpose the results using only a single crosstab.

If however the real table structure is similar to the UNION query I suggested then it would be a waste of effort to base a UNION query off his original CROSSTAB query to get a new CROSSTAB query.


Duane
MS Access MVP

 

[aageorge]

dhookom, these are my tables on which the above union query is based.

T1 T2 T3
Date A B C D Date B C Date A C
8/1 10 10 10 10 8/2 20 20 8/1 30 30
8/2 10 10 10 10 8/2 30 30
8/3 10 10 10 10

So is there any way to directly get the crosstab result I wanted?

 

[dhookom]

Since you have fields that are A, B, C, and D you will need to create a UNION query as suggested in my first reply.
Your union query would be similar to:
SELECT [Date], [A] as Qty, "A" as Part
FROM qYourQuery
UNION ALL
SELECT [Date], , "B"
FROM qYourQuery
UNION ALL
SELECT [Date], [C], "C"
FROM qYourQuery
UNION ALL
SELECT [Date], [D], "D"
FROM qYourQuery;

Save this union query and then create a crosstab based on the union query. This is the price that is paid for tables that might not be properly normalized.


Duane
MS Access MVP

 

[LarryDeLaruelle]

Duane:

Good catch. Thanks for the response.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[aageorge]

Duane,

Just make things clear, I start with these tables:

T1 T2 T3
Date A B C D Date B C Date A C
8/1 10 10 10 10 8/2 20 20 8/1 30 30
8/2 10 10 10 10 8/2 30 30
8/3 10 10 10 10

And then I have a union query that sums everything into one table:

T1
Date A B C D
8/1 40 10 40 10
8/2 40 30 60 10
8/3 10 10 10 10

Now the question is how do I get the normalized table you talked about in your earlier post(like the one below)

T1
Date Part qty
8/1 A 40
8/1 B 10
8/1 C 40

and so on, so I can create a crosstab query?

 

[dhookom]

The answer is in a previous post from me. It is the one with partially bold letters because I use a field name [B.] without the "."

Duane
MS Access MVP