Getting percentages out of Union Query

[jalge2]

I've built a Union Query that pulls counts from 3 different queries. I placed those 3 counts on a report but my boss wants the percentages beside it too. The output is this

Building Picks 130000
Mod 13000
Perms 5000

I need the percentages of the picks that are coming from the Mods and the Perentages coming from the Perms. I'm not sure if I do this in the query, or in the report. Can someone help me here?

Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'

 

[Golom]

Without seeing the current query its a bit difficult but something like

Select GroupField, Count(*) As [Num], 
       (Count(*) / (Select Count(*) From tbl)) As Percent
From tbl
Where GroupField = 'Building Picks'
Group By GroupField

UNION

etc.

 

[dhookom]

Percentages of what? You might want to provide the calculated percentages as desired in your sample output so we have an idea of your final result.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[jalge2]

The percentages of picks in the building as compared to the total.

Mods 13000 Percentage 10%
Perm 5000 Percentage 3%


Total Picks 130000




Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'

 

[jalge2]

Select Carton, Area from qryfinalDedicatedItemsPickingInBld Union Select Carton, Area from qryCartonsinMods UNION Select Carton, Area from qryTotalCartonsinPrintedStatus;


Above you will see my SQL Query. Hope that helps.

Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'

 

[Golom]

Presumably the grand total that you need is

Select TOP 1
(

(Select SUM(Area) From qryfinalDedicatedItemsPickingInBld) +
(Select SUM(Area) From qryCartonsinMods) +
(Select SUM(Area) From qryTotalCartonsinPrintedStatus)

) As Total

From AnyTable

Save that as a query (qryTotal, for example), then

Select Carton, Area, (Area / Total) As Percentage
From qryfinalDedicatedItemsPickingInBld, qryTotal 

UNION

 etc.

 

[dhookom]

I would use something like:

Select qryCartonsinMods.Carton, qryCartonsinMods.Area, qryFinalDedicatedItemsPickingInBld.Area as PickArea,
 qryCartonsinMods.Area/qryFinalDedicatedItemsPickingInBld.Area As Pct
from qryCartonsinMods , qryFinalDedicatedItemsPickingInBld
UNION 
Select qryTotalCartonsinPrintedStatus.Carton, qryTotalCartonsinPrintedStatus.Area, qryFinalDedicatedItemsPickingInBld.Area,
 qryTotalCartonsinPrintedStatus.Area/qryFinalDedicatedItemsPickingInBld.Area
from qryTotalCartonsinPrintedStatus , qryFinalDedicatedItemsPickingInBld;

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]