S
Sam
Hi,
I have three queries that all share a common primary key. One is the
result of a standard select query. The other two are the result of
crosstab queries, thus the number and names of the colums they contain
cannot be predicted. All three queries are derived from the same
underlying query and have the same rows (e.g. for every row in query A
there is one and only one row in queries B/C with the same key).
My objective is to create a new query that combines all of the columns
in these other queries keyed by the same PK. I've been able to get
most of the way there in SQL, but the resulting query has one column
for each of the keys from each of the queries. I want the key to
appear only once!
So, for example the queries might have the following column structure:
qryHHInfo: (HHID [PK], LastName, FirstName, ...)
qryGrants_Crosstab: (HHID [PK], ProgramX, ProgramY, ProgramZ)
qryBatches_Crosstab: (HHID [PK], ProgramX_Batch, ProgramY_Batch,
ProgramZ_Batch)
The new query result looks like this. Note the three instances of the
key:
qryCombinedResults: (qryHHInfo.HHID, LastName, FirstName, ...,
qryGrants_Crosstab.HHID, ProgramX, ProgramY, ProgramZ,
qryBatches_Crosstab.HHID, ProgramX_Batch, ProgramY_Batch,
ProgramZ_Batch)
This is the SQL I used to create the combined results:
SELECT qryHHInfo.*, qryGrants_Crosstab.*, qryBatches_Crosstab.*
FROM (qryHHInfo INNER JOIN qryGrants_Crosstab ON
qryHHInfo.HouseholdID=qryGrants_Crosstab.HouseholdID)
INNER JOIN qryBatches_Crosstab ON
qryGrants_Crosstab.HouseholdID=qryBatches_Crosstab.HouseholdID;
Can this query be rewritten to get rid of the extra key columns???
Thanks,
Sam
I have three queries that all share a common primary key. One is the
result of a standard select query. The other two are the result of
crosstab queries, thus the number and names of the colums they contain
cannot be predicted. All three queries are derived from the same
underlying query and have the same rows (e.g. for every row in query A
there is one and only one row in queries B/C with the same key).
My objective is to create a new query that combines all of the columns
in these other queries keyed by the same PK. I've been able to get
most of the way there in SQL, but the resulting query has one column
for each of the keys from each of the queries. I want the key to
appear only once!
So, for example the queries might have the following column structure:
qryHHInfo: (HHID [PK], LastName, FirstName, ...)
qryGrants_Crosstab: (HHID [PK], ProgramX, ProgramY, ProgramZ)
qryBatches_Crosstab: (HHID [PK], ProgramX_Batch, ProgramY_Batch,
ProgramZ_Batch)
The new query result looks like this. Note the three instances of the
key:
qryCombinedResults: (qryHHInfo.HHID, LastName, FirstName, ...,
qryGrants_Crosstab.HHID, ProgramX, ProgramY, ProgramZ,
qryBatches_Crosstab.HHID, ProgramX_Batch, ProgramY_Batch,
ProgramZ_Batch)
This is the SQL I used to create the combined results:
SELECT qryHHInfo.*, qryGrants_Crosstab.*, qryBatches_Crosstab.*
FROM (qryHHInfo INNER JOIN qryGrants_Crosstab ON
qryHHInfo.HouseholdID=qryGrants_Crosstab.HouseholdID)
INNER JOIN qryBatches_Crosstab ON
qryGrants_Crosstab.HouseholdID=qryBatches_Crosstab.HouseholdID;
Can this query be rewritten to get rid of the extra key columns???
Thanks,
Sam