Combining Multiple CrossTab Queries into one query in MS Access

[wire_jp]

Hello,

I would like to combine multiple crosstab queries and queries (i.e. eight crosstab queries) into one query. In order to test this approach, I tried to combine two crosstab queries into one query using the following SQL code

Code: Select all

SELECT *
From [qryBCTMemorabiliaResponsesCrossTabQuery] UNION SELECT * FROM [qryBingoResponsesCrossTabQuery];

but I received the error message: "The number of columns in the two selected tables or queries of a Union query do not match."

So instead, I retested this approach using two queries with the same number of columns and the SQL code is attached below: -

Code: Select all

SELECT *
FROM [qryGeneralSponsorshipResponsesCrossTabQuery] UNION SELECT * FROM [qryAnyOtherVolunteerAreasResponses];

However, the resulting query did not produce the results which I was looking for as specific field data for "Helping to identify individuals, philanthropic organisations and corporate entities willing to make donations to the BCT and/or partner with the BCT in special projects of mutual interest." field of the qryGeneralSponsorshipResponsesCrossTabQuery query is combined in the same column with the data of the AnyOtherVolAreasOption field from the qryAnyOtherVolunteerAreasResponses query into one field column of the new union query called qryFormResponsesCrossQueryTabs query (I hoping for the data be separated out into two columns). This is not correct approach which I am looking for as I hoping for the data be separated out into two columns.

The crosstab queries and queries which I want to combine are the following: -
qryBCTMemorabiliaResponsesCrossTabQuery
qryBingoResponsesCrossTabQuery
qryCarRaffleResponsesCrossTabQuery
qryEntertainmentResponsesCrossTabQuery
qryGeneralSponsorshipResponsesCrossTabQuery
qryLifelongLearningCrossQueryTab
qryAnyOtherVolunteerAreasResponses
qryAnyOtherIdeasResponses

The OneDrive weblink is attached below:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=8rzhXD

[HansV]

How about

SELECT tblFormResponses.[BARP member number], tblFormResponses.Timestamp, qryBCTMemorabiliaResponsesCrossTabQuery.[Assisting in the promotion and sale of these items_], qryBCTMemorabiliaResponsesCrossTabQuery.[Helping to secure sponsorship or discounted rates for the manufa], qryBCTMemorabiliaResponsesCrossTabQuery.[Offering your creative and artistic talents in the design of mem], qryBingoResponsesCrossTabQuery.[Assisting with the sale of admission tickets and cards_], qryBingoResponsesCrossTabQuery.[Assisting with the sale of admission tickets and cards_], qryBingoResponsesCrossTabQuery.[Helping to run the sessions_], qryBingoResponsesCrossTabQuery.[Promoting the event among family and colleagues and encouraging], qryBingoResponsesCrossTabQuery.[Sourcing donor sponsorship for prizes_]
FROM (tblFormResponses LEFT JOIN qryBCTMemorabiliaResponsesCrossTabQuery ON (tblFormResponses.Timestamp = qryBCTMemorabiliaResponsesCrossTabQuery.Timestamp) AND (tblFormResponses.[BARP member number] = qryBCTMemorabiliaResponsesCrossTabQuery.[BARP member number])) LEFT JOIN qryBingoResponsesCrossTabQuery ON (tblFormResponses.Timestamp = qryBingoResponsesCrossTabQuery.Timestamp) AND (tblFormResponses.[BARP member number] = qryBingoResponsesCrossTabQuery.[BARP member number])
ORDER BY tblFormResponses.[BARP member number], tblFormResponses.Timestamp;

[wire_jp]

Hi Hans,

Yes, thanks. this is the view which I am looking for.

[wire_jp]

The final SQL for the 6 crosstab queries and the 2 queries:-

Code: Select all

SELECT tblFormResponses.[BARP member number], tblFormResponses.Timestamp, [First Name] & " " & [Last Name] AS FullName, tblFormResponses.[First Name], tblFormResponses.[Middle Initial], tblFormResponses.[Last Name], tblFormResponses.[Email Address], tblFormResponses.Address, tblFormResponses.Parish, tblFormResponses.[Home phone number], tblFormResponses.[Mobile phone number_], tblFormResponses.[Date of Birth (mm/dd/yyyy)], qryBingoResponsesCrossTabQuery.[Promoting the event among family and colleagues and encouraging], qryBingoResponsesCrossTabQuery.[Assisting with the sale of admission tickets and cards_], qryBingoResponsesCrossTabQuery.[Sourcing donor sponsorship for prizes_], qryBingoResponsesCrossTabQuery.[Helping to run the sessions_], qryCarRaffleResponsesCrossTabQuery.[Selling books of tickets_], qryCarRaffleResponsesCrossTabQuery.[Sourcing donor sponsorship for additional prizes_], qryEntertainmentResponsesCrossTabQuery.[Performing in concerts or entertainment activities], qryEntertainmentResponsesCrossTabQuery.[Identifying potential performers_], qryEntertainmentResponsesCrossTabQuery.[Sourcing potential sponsors_], qryEntertainmentResponsesCrossTabQuery.[Helping to organise BCT concerts or entertainment activities_], qryEntertainmentResponsesCrossTabQuery.[Promoting entertainment activities_], qryEntertainmentResponsesCrossTabQuery.[Selling tickets for these activities_], qryEntertainmentResponsesCrossTabQuery.OtherEntertainmentOptions, qryLifelongLearningCrossQueryTab.[Being a presenter on a topic of interest to seniors_], qryLifelongLearningCrossQueryTab.[Identifying suitable presenters and possible activities_], qryLifelongLearningCrossQueryTab.[Helping to organize activities_], qryLifelongLearningCrossQueryTab.[Promoting attendance at the activities_], qryLifelongLearningCrossQueryTab.[Securing donor sponsorship for the activities_], qryBCTMemorabiliaResponsesCrossTabQuery.[Offering your creative and artistic talents in the design of mem], qryBCTMemorabiliaResponsesCrossTabQuery.[Assisting in the promotion and sale of these items_], qryBCTMemorabiliaResponsesCrossTabQuery.[Helping to secure sponsorship or discounted rates for the manufa], qryGeneralSponsorshipResponsesCrossTabQuery.[Helping to identify individuals, philanthropic organisations and], qryAnyOtherVolunteerAreasResponses.AnyOtherVolAreasOption, qryAnyOtherIdeasResponses.AnyOtherIdeasOptionID
FROM (((((((tblFormResponses LEFT JOIN qryBCTMemorabiliaResponsesCrossTabQuery ON (tblFormResponses.[BARP member number] = qryBCTMemorabiliaResponsesCrossTabQuery.[BARP member number]) AND (tblFormResponses.Timestamp = qryBCTMemorabiliaResponsesCrossTabQuery.Timestamp)) LEFT JOIN qryBingoResponsesCrossTabQuery ON (tblFormResponses.[BARP member number] = qryBingoResponsesCrossTabQuery.[BARP member number]) AND (tblFormResponses.Timestamp = qryBingoResponsesCrossTabQuery.Timestamp)) LEFT JOIN qryCarRaffleResponsesCrossTabQuery ON (tblFormResponses.Timestamp = qryCarRaffleResponsesCrossTabQuery.Timestamp) AND (tblFormResponses.[BARP member number] = qryCarRaffleResponsesCrossTabQuery.[BARP member number])) LEFT JOIN qryEntertainmentResponsesCrossTabQuery ON (tblFormResponses.[BARP member number] = qryEntertainmentResponsesCrossTabQuery.[BARP member number]) AND (tblFormResponses.Timestamp = qryEntertainmentResponsesCrossTabQuery.Timestamp)) LEFT JOIN qryGeneralSponsorshipResponsesCrossTabQuery ON (tblFormResponses.Timestamp = qryGeneralSponsorshipResponsesCrossTabQuery.Timestamp) AND (tblFormResponses.[BARP member number] = qryGeneralSponsorshipResponsesCrossTabQuery.[BARP member number])) LEFT JOIN qryLifelongLearningCrossQueryTab ON (tblFormResponses.Timestamp = qryLifelongLearningCrossQueryTab.Timestamp) AND (tblFormResponses.[BARP member number] = qryLifelongLearningCrossQueryTab.[BARP member number])) LEFT JOIN qryAnyOtherVolunteerAreasResponses ON (tblFormResponses.Timestamp = qryAnyOtherVolunteerAreasResponses.Timestamp) AND (tblFormResponses.[BARP member number] = qryAnyOtherVolunteerAreasResponses.[BARP member number])) LEFT JOIN qryAnyOtherIdeasResponses ON (tblFormResponses.Timestamp = qryAnyOtherIdeasResponses.Timestamp) AND (tblFormResponses.[BARP member number] = qryAnyOtherIdeasResponses.[BARP member number])
ORDER BY tblFormResponses.[BARP member number], tblFormResponses.Timestamp;

[HansV]