union query from multiple crosstabs

[AndyC121]

I have been try to create a union query from multiple crosstabs. I get either no data or just the first headings duplicated, searched for lots of answers but not quite sure what im doing any pointer please. Below are my crosstab queries:



TRANSFORM Count([COSHH table].ID) AS CountOfID
SELECT [COSHH table].ID, [COSHH table].[Name of substance]
FROM [COSHH table] INNER JOIN [Fire fighting] ON [COSHH table].[Fire fighting measures].Value = [Fire fighting].ID
WHERE ((([COSHH table].ID)=True))
GROUP BY [COSHH table].ID, [COSHH table].[Name of substance]
PIVOT [Fire fighting].[Fire fighting measures];

TRANSFORM Count([COSHH table].ID) AS CountOfID
SELECT [COSHH table].ID, [COSHH table].[Name of substance]
FROM [COSHH table] RIGHT JOIN Hazards ON [COSHH table].Hazards.Value = Hazards.ID
WHERE ((([COSHH table].ID)=True))
GROUP BY [COSHH table].ID, [COSHH table].[Name of substance]
PIVOT Hazards.Hazards;


TRANSFORM Count([COSHH table].ID) AS CountOfID
SELECT [COSHH table].ID, [COSHH table].[Name of substance]
FROM [COSHH table] INNER JOIN Locations ON [COSHH table].[Location used].Value = Locations.ID
WHERE ((([COSHH table].ID)=True))
GROUP BY [COSHH table].ID, [COSHH table].[Name of substance]
PIVOT Locations.Locations;


TRANSFORM Count([COSHH table].ID) AS CountOfID
SELECT [COSHH table].ID, [COSHH table].[Name of substance]
FROM [COSHH table] INNER JOIN PPE ON [COSHH table].[PPE required].Value = PPE.ID
WHERE ((([COSHH table].ID)=True))
GROUP BY [COSHH table].ID, [COSHH table].[Name of substance]
PIVOT PPE.[PPE Required];


TRANSFORM Count([COSHH table].ID) AS CountOfID
SELECT [COSHH table].ID, [COSHH table].[Name of substance]
FROM [COSHH table] INNER JOIN [Route of exposure] ON [COSHH table].[Route of exposure].Value = [Route of exposure].ID
WHERE ((([COSHH table].ID)=True))
GROUP BY [COSHH table].ID, [COSHH table].[Name of substance]
PIVOT [Route of exposure].[Route of exposure];

[CJ_London]

for a union query to work, all queries must have the same number of columns and datatypes. Crosstabs can have varying number of columns (even if you know there are the same number, access doesn't). You can get round this by setting the column headings in the crosstab properties.

If you actually want a very wide query then you don't use a union query, instead you need tow more.

The first is one that will generate a list of all the rows you want to appear (regardless of whether they are in each of the crosstabs)

Not clear from what you have posted but perhaps [COSHH table].ID and [COSHH table].[Name of substance]

then create a new query, bring the one above in and left join to all of your crosstabs

Also not clear about your criteria

WHERE ((([COSHH table].ID)=True))

this will bring through all records where ID<>0 and is not null. ID's are usually autonumbers and never zero or null so the criteria is pointless. But perhaps it means something else...

[AndyC121]

Hi Ajax,
thanks for the reply. maybe im going about this all the wrong way.
Im trying to generate a report from the form "COSHH Form" in my attached example database. the report has to look like "report1" (this is just an example of the report and wont be an embedded word doc) with the boxes ticked or crosses in them corresponding to the selections made from the cosh form.

[CJ_London]

the report looks like it is one report per substance, So I think all you need is the one crosstab - but put the column headers in query so you can see all of them - see the query properties, you would populate it with the text value e.g. "Very Toxic", "Toxic", "Corrosive" etc