Please help with crosstab queries

G

Guest

Hello. I am trying to display 2 rows of data underneath a column heading. I
have created two crosstab queries which work right independently. Below are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS [Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03
AA-04
50 5 10 25 1 2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem headings
across top)
50 5 10 25 (Number of pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
 
G

Guest

I am reposting the results I would like to see for this query, because the
spacing got messed up. Below is what happens when I combine the crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2 2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of occurrences)

Again, thank you for any advice you can provide!
 
D

Duane Hookom

You would need to use a union query to get your results.

I'm not sure why you need to use DCount() in either crosstab.

--
Duane Hookom
MS Access MVP
--

NewSysAdmin said:
I am reposting the results I would like to see for this query, because the
spacing got messed up. Below is what happens when I combine the crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2 2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of
occurrences)

Again, thank you for any advice you can provide!

NewSysAdmin said:
Hello. I am trying to display 2 rows of data underneath a column
heading. I
have created two crosstab queries which work right independently. Below
are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all
displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03
AA-04
50 5 10 25 1
2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem
headings
across top)
50 5 10 25 (Number of pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
Click to expand...
Click to expand...
 
G

Guest

Thank you so much, a union query gave me the correct results. I thought that
I had tried that earlier, but I couldn't get it to work. Now it works, and
that's all that matters. I used DCount only as a placeholder for the row
heading to display the results I wanted. I will now take the results and put
in a form, leaving out the DCount function.

Duane Hookom said:
You would need to use a union query to get your results.

I'm not sure why you need to use DCount() in either crosstab.

--
Duane Hookom
MS Access MVP
--

NewSysAdmin said:
I am reposting the results I would like to see for this query, because the
spacing got messed up. Below is what happens when I combine the crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2 2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of
occurrences)

Again, thank you for any advice you can provide!

NewSysAdmin said:
Hello. I am trying to display 2 rows of data underneath a column
heading. I
have created two crosstab queries which work right independently. Below
are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all
displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03
AA-04
50 5 10 25 1
2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem
headings
across top)
50 5 10 25 (Number of pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
G

George Nicholson

SELECT * FROM CrossTab1
UNION SELECT * FROM CrossTab2
Should give you 2 rows, one for each cross tab. Consider adding an
identifying field to your CrossTabs, if you want the lines automatically
"labeled":
...SELECT "Number of pieces" As [Label], DCount("[Internal # no good sort],
....
...SELECT "Number of occurrences" As [Label], DCount("[Internal # no good
sort], ...

Union queries have to be written by hand in the SQL window of the query
designer.
They also require the same number of fields (but not the same Field names),
so if there is a chance that your crosstabs will return a different # of
Fields OR Field names headers (which does not seem to be the case here), you
would either need to specify the field names in the CrossTabs themselves
(the Column Headings property) or in the UNION query:
SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab1
UNION SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab2

All of this is aircode...
HTH,
--
George Nicholson

Remove 'Junk' from return address.


NewSysAdmin said:
I am reposting the results I would like to see for this query, because the
spacing got messed up. Below is what happens when I combine the crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2 2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of
occurrences)

Again, thank you for any advice you can provide!

NewSysAdmin said:
Hello. I am trying to display 2 rows of data underneath a column
heading. I
have created two crosstab queries which work right independently. Below
are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all
displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03
AA-04
50 5 10 25 1
2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem
headings
across top)
50 5 10 25 (Number of pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
Click to expand...
Click to expand...
 
G

Guest

Thanks again for both of your suggestions. The union query did give me the
results I wanted. However, now I have another obstacle. I put the results
from the union query into a form which I will then use in a report. I need
to apply conditional formatting (for colors) to the number of pcs and number
of occurrences fields individually, with different conditions. It seems that
the union query lumps these fields together, so I'm not sure how to go about
doing the conditional formatting. On my form it groups the 2 lines (being
number of occurrences, and number of pcs) into 2 records. Any suggestions?

George Nicholson said:
SELECT * FROM CrossTab1
UNION SELECT * FROM CrossTab2
Should give you 2 rows, one for each cross tab. Consider adding an
identifying field to your CrossTabs, if you want the lines automatically
"labeled":
...SELECT "Number of pieces" As [Label], DCount("[Internal # no good sort],
....
...SELECT "Number of occurrences" As [Label], DCount("[Internal # no good
sort], ...

Union queries have to be written by hand in the SQL window of the query
designer.
They also require the same number of fields (but not the same Field names),
so if there is a chance that your crosstabs will return a different # of
Fields OR Field names headers (which does not seem to be the case here), you
would either need to specify the field names in the CrossTabs themselves
(the Column Headings property) or in the UNION query:
SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab1
UNION SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab2

All of this is aircode...
HTH,
--
George Nicholson

Remove 'Junk' from return address.


NewSysAdmin said:
I am reposting the results I would like to see for this query, because the
spacing got messed up. Below is what happens when I combine the crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2 2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of
occurrences)

Again, thank you for any advice you can provide!

NewSysAdmin said:
Hello. I am trying to display 2 rows of data underneath a column
heading. I
have created two crosstab queries which work right independently. Below
are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all
displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03
AA-04
50 5 10 25 1
2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem
headings
across top)
50 5 10 25 (Number of pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
G

George Nicholson

Original Post:
Most recent Post:
On my form it groups the 2 lines (being
number of occurrences, and number of pcs) into 2 records. Any
suggestions?
Click to expand...

I'm a bit confused. Isn't that exactly what you wanted??

Or is it a case of "I got what I thought I wanted, now what do I do?", in
which case you need to be a bit more specific as to the problem.
You have 2 records with the following Fields: Label (Pieces or Occurances),
ProbCodeA, ProbCodeB, etc.
What else do you need to know for whatever formating you want to do?

--
George Nicholson

Remove 'Junk' from return address.


NewSysAdmin said:
Thanks again for both of your suggestions. The union query did give me
the
results I wanted. However, now I have another obstacle. I put the
results
from the union query into a form which I will then use in a report. I
need
to apply conditional formatting (for colors) to the number of pcs and
number
of occurrences fields individually, with different conditions. It seems
that
the union query lumps these fields together, so I'm not sure how to go
about
doing the conditional formatting. On my form it groups the 2 lines (being
number of occurrences, and number of pcs) into 2 records. Any
suggestions?

George Nicholson said:
SELECT * FROM CrossTab1
UNION SELECT * FROM CrossTab2
Should give you 2 rows, one for each cross tab. Consider adding an
identifying field to your CrossTabs, if you want the lines automatically
"labeled":
...SELECT "Number of pieces" As [Label], DCount("[Internal # no good
sort],
....
...SELECT "Number of occurrences" As [Label], DCount("[Internal # no
good
sort], ...

Union queries have to be written by hand in the SQL window of the query
designer.
They also require the same number of fields (but not the same Field
names),
so if there is a chance that your crosstabs will return a different # of
Fields OR Field names headers (which does not seem to be the case here),
you
would either need to specify the field names in the CrossTabs themselves
(the Column Headings property) or in the UNION query:
SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab1
UNION SELECT [Label], [AA-01], [AA-02], ... [AA-04]* FROM CrossTab2

All of this is aircode...
HTH,
--
George Nicholson

Remove 'Junk' from return address.


NewSysAdmin said:
I am reposting the results I would like to see for this query, because
the
spacing got messed up. Below is what happens when I combine the
crosstab
queries:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02 AA-03 AA-04
50 10 25 15 1 2
2
1

Here are the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (Problem code headings)
50 10 25 15 (Number of pcs)
1 2 2 1 (Number of
occurrences)

Again, thank you for any advice you can provide!

:

Hello. I am trying to display 2 rows of data underneath a column
heading. I
have created two crosstab queries which work right independently.
Below
are
my 2 SQL statements for the 2 queries:


TRANSFORM Count([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number of Occurrences]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


TRANSFORM Sum([RMA/TR Tracking table].[Internal # no good sort]) AS
[Number
of Pcs]
SELECT DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]") AS CountOfRecord
FROM [RMA/TR Tracking table] INNER JOIN [Problem Codes] ON [RMA/TR
Tracking
table].[Problem Code] = [Problem Codes].[Problem Code]
WHERE ((([RMA/TR Tracking table].[Internal # no good sort])>0))
GROUP BY DCount("[Internal # no good sort]","[RMA/TR Tracking
table]","[Problem Code]")
PIVOT [RMA/TR Tracking table].[Problem Code];


When I try to combine the 2 crosstab queries I get the results all
displayed
in one row:

AA-01 AA-02 AA-03 AA-04 AA-01 AA-02
AA-03
AA-04
50 5 10 25 1
2
2 1


Here is a sample of the results I would like to see:

AA-01 AA-02 AA-03 AA-04 (This lists problem
headings
across top)
50 5 10 25 (Number of
pcs)
1 2 2 1 (Number of
occurrences)



Any help would be very much appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With Parameter Query 2
Crosstab Query by Week with Dates as Headers 5
Lookup based on reference 5
CROSSTAB QUERY- Display Rows even if there is no data 2
Sort on no less than sixteen keys 4
PLEASE help with access query 4
Blank page trying to set display on Tables 1
Slow Crosstab Query 2

Top