Union two query results in horizontal way??

[kelvin2088]

I have two queries yielding following results:

Cerritos Service Center	$70,500.00
New York Academy	$82,000.00

<caption> qrySelectedRegionCC_CAPEX </caption><thead>
[TH="bgcolor: #c0c0c0"] CC Name [/TH]
[TH="bgcolor: #c0c0c0"] CAPEX [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Cerritos Service Center	$42,000.00

<caption> qrySelectedRegionCC_Personeel </caption><thead>
[TH="bgcolor: #c0c0c0"] CC Name [/TH]
[TH="bgcolor: #c0c0c0"] Personeel [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Is there any query that can help me combine two results into single set like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CC Name[/TD]
[TD]CAPEX[/TD]
[TD]Personeel[/TD]
[/TR]
[TR]
[TD]Cerritos Service Center[/TD]
[TD]70500[/TD]
[TD]42000[/TD]
[/TR]
[TR]
[TD]New York Academy[/TD]
[TD]82000[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!!

 

[ryansclev]

This should work, just replace the table names with your query names. It joins the tables (queries) on the CC Name field and takes all of the records from Table1 and the matching records in Table2. The fields are the CC Name from Table1, CAPEX from Table1 and Personeel from Table2.:

SELECT Table1.[CC Name], Table1.CAPEX, CCur(Nz([Personeel],0)) AS Personnel
FROM Table1 LEFT JOIN Table2 ON Table1.[CC Name] = Table2.[CC Name];

 

[kelvin2088]

Thank you very much!!
I just discovered crosstab query and I'm thinking about redesigning my query.

 

[Joe4]

Just a note on Ryan's reply. That will work, assuming that there will never be a "CC Name" value in your second table that doesn't exist in the first table.
Basically, what the query written above will do is return all the records from the first table, and all the matching values from the second (or uses a zero if there is no match).

If you have a situation where you may have records in either table that do not exist in the other, then it gets a bit more tricky...

 

[Joe4]

I just discovered crosstab query and I'm thinking about redesigning my query.

If Ryan's assumption is correct, then there is no need for a Crosstab Query. A simple Left Join like he did will handle it.

 

[ryansclev]

Thanks Joe for adding that. I thought I had prefaced my response with that, but I believe the cold medicine has the better of my brain today.

 

[Joe4]

No problem!
I hate summer colds! Hope you feel better soon.

 

[kelvin2088]

I just redesigned the original query to a crosstab one, and it worked great!
TRANSFORM Sum([Qtr1Amount]+[Qtr2Amount]+[Qtr3Amount]+[Qtr4Amount]) AS Annual
SELECT tblCostCenter.CostCenterName
FROM tblProjectType INNER JOIN (tblProject_Master INNER JOIN (((tblRegion INNER JOIN tblCostCenter ON tblRegion.RegionName = tblCostCenter.RegionName) INNER JOIN tblProject_Budget ON tblCostCenter.CostCenterID = tblProject_Budget.CostCenterID) INNER JOIN tblProject_BudgetDetail ON tblProject_Budget.BudgetProjectID = tblProject_BudgetDetail.BudgetProjectID) ON tblProject_Master.ProjectID = tblProject_Budget.ProjectID) ON tblProjectType.ProjectType = tblProject_Master.ProjectType
GROUP BY tblCostCenter.CostCenterName
PIVOT tblProject_Master.ProjectType;

Cerritos Service Center	$70,500.00	$2,966.00		$42,000.00
Facilities Management			$1,719.00
Houston Academy		$825.00
Long Island Academy		$7,885.00
New York Academy	$82,000.00
Total	$152,500.00	$11,676.00	$1,719.00	$42,000.00

<tbody>
[TH="bgcolor: #c0c0c0"] CC Name [/TH]
[TH="bgcolor: #c0c0c0"] CAPEX [/TH]
[TH="bgcolor: #c0c0c0"] Charity [/TH]
[TH="bgcolor: #c0c0c0"] Medical [/TH]
[TH="bgcolor: #c0c0c0"] Personeel [/TH]

</tbody>

To go deeper, is there anyway to add another column (calculated field) to calculate total of, let's say, CAPEX+Charity fields?

Thanks!

 

[Joe4]

So there are really four tables/queries to include, not two (as stated in your original question). It is usually best to not over simplify your situation, or else you may get an oversimplified answer that might not work for you.

To go deeper, is there anyway to add another column (calculated field) to calculate total of, let's say, CAPEX+Charity fields?

So, you now have this CrossTab Query that shows the results you posted above. You should be able to create a new query based on this query where you can add your fields in a new calculated field, i.e.

Total: NZ([Capex],0) + NZ([Charity],0)

 

[kelvin2088]

I did so. thank you very much!!

So there are really four tables/queries to include, not two (as stated in your original question). It is usually best to not over simplify your situation, or else you may get an oversimplified answer that might not work for you.


So, you now have this CrossTab Query that shows the results you posted above. You should be able to create a new query based on this query where you can add your fields in a new calculated field, i.e.

Total: NZ([Capex],0) + NZ([Charity],0)