Question

Locked

How can I create one query from multiple queries (12 of them) in access?

By wmoyonsana ·
I have 12 queries with 4 same fields 1 different field in each query. Like this:
(1) -districtID, FacilityID, AssessmentAreaID, AssessmentTypeID, TotalANCStds
(2) -districtID, FacilityID, AssessmentAreaID, AssessmentTypeID, TotalNLDStds
How do i create my union query to come up with one query with all total fields included?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Two ways

by Tony Hopkinson In reply to How can I create one quer ...

Given all your columns are the same datatype
you could do something like

Select districtid,AssessmentAreadId,assessmenttypeid,Convert(VarChar(16),'ANC') as TypeOfTotal, TotalANCStds as Total
Union all
Select districtid,AssessmentAreadId,assessmenttypeid,'NLD', TotalNLDStds
Union all
...

Or (for just two totals)

Select districtid,AssessmentAreadId,assessmenttypeid, TotalANCStds, Convert(int,0) as TotalNLDStds
Union all
Select districtid,AssessmentAreadId,assessmenttypeid, 0,TotalNLDStds

then you can sum it with

Select districtid,AssessmentAreadId,assessmenttypeid, Sum(TotalANCStds) and TotalANC,Sum(TotalNLDStds) as TotalNLD
Group By districtid,AssessmentAreadId,assessmenttypeid

More total colums is just starting off with more columns in teh first query and inserting the requite number of 0s.

HtHs

Collapse -

How can I create one query from multiple queries (12 of them) in access?

by wmoyonsana In reply to How can I create one quer ...

Thanks for the quick response. Can you give me a clue on how you would finish the remaining portion of union query starting from "FROM" to the very end? Include that in both examples. Thanks, Watson Moyonsana.

Collapse -

Reponse To Answer

by Tony Hopkinson In reply to How can I create one quer ...

YourFirstQuery
Union All
YourSecondQuery
Union All
...
Union All
YourTwelfthQuery

The only rule for union is
each query must return the same number of columns and that each column has the same datatype all the way through.

What I gave you was a a couple of templates for unioning queries, can't give you the answer, haven't got your tables/queries, or their content...

Good yes, psychic no.

Collapse -

How can I create one query from multiple queries (12 of them) in access?

by wmoyonsana In reply to How can I create one quer ...

Your assistance is greatly appreciated. What I am looking for is a single query that will produce something like this:
DistrictId FacilityID AssessmentAreaId AssessmentTypeID ANCTotal NLDTotal MCLDTotal PNCTotal FPSTotal FPFUTotal PACTotal CCTotal STITotal SSTotal IECTotal MSTotal
Currently, I have separate queries that look like this:
(1) qryBaselineANCTotal (Columns: DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID, ANCTotal
(2) qryBaselineNLDTotal (Columns: DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID, NLDTotal
(3) qryBaselineMCLDTotal (Columns:DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID, MCLDTotal
.
.
And the twelfth query is:
qryBaselineMSTotal (Columns: DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID, MSTotal
I am not sure if union query is the answer to my problem or a different query can help. The challenge with union query is the omission of dissimilar columns in the output. I don't know how to resolve this. I guess writing a code will be more complicated than the query. Thanks in advance for your input or comments.

Collapse -

Reponse To Answer

by Tony Hopkinson In reply to How can I create one quer ...

Can't see another way of doing aside from a union query, you could use an intermediate table but you'd still effectively get one of two basic outputs.

you can have

DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID with tweve total columns.
They'd start with nulls except for one column for each subquery in the union, then you could sum and group them by DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID

or

DistrictID, FacilityID, AssessmentAreaID, AssessmentTypeID, TotalType
and a total column.

There are options other than union but they wonlt have a significant impact on the presentation / layout of the result. Can't see many other ways to present it in one go either, aside from perhaops crosstab / pivot for a matrixc or graph....

Is there some dark secret you aren't sharing with us, as far as I can the we've answered your question, so waht are we missing that you think is 'obvious'

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums