Help with a Union qry

[Groundrush]

I know I have posted this before but I am still unable to sort this one out

I have a qry that that has an equal join between 2 other qrys, which works fine if there is data in both.

As soon as one of the qrys has no data to pull through it will not read the qry that has

I need something that will except both regardless of which qry has data to pull through...........if that makes any sense!

this is my qry:
SELECT qryHolidayHalfDayCount.ResourceName, Nz([HolidayCount],0)+Nz([HolidayHalfDayCount],0)/2 AS HolidaysTaken
FROM qryHolidayFullDayCount INNER JOIN qryHolidayHalfDayCount ON qryHolidayFullDayCount.ResourceName = qryHolidayHalfDayCount.ResourceName;

this is what "FOFA" has suggested:

"Your query is not what you want. It is because you have a equal join between the two querys that means ONLY if both are present. What you really need is a UNION ALL query that takes all the rows from the halfday count (divides by two) and a one that takes all the rows from the holiday count, groups by resourcename and sums the counts up. Basically"

I think FOFA'S suggestion is the way to go.
can anyone help me to get there?

this is my attempt at a Union All qry:
SELECT [ResourceName],[HolidayHalfDayCount]
FROM [qryHolidayHalfDayCount]
UNION ALL SELECT [ResourceName],[HolidayCount]
FROM [qryHolidayFullDayCount];


Thanks

 

[yippie_ky_yay]

Hey Groundrush,

in a union query, the columns must match so in your example here:
SELECT [ResourceName],[HolidayHalfDayCount]
FROM [qryHolidayHalfDayCount]
UNION ALL SELECT [ResourceName],[HolidayCount]
FROM [qryHolidayFullDayCount];

you would need to make your second column have the same heading. To accomplish this, simply add the keyword "AS" followed by the new column name (this is called aliasing).

SELECT [ResourceName],[HolidayHalfDayCount] AS HolidayCount
FROM [qryHolidayHalfDayCount]
UNION
SELECT [ResourceName],[HolidayCount]
FROM [qryHolidayFullDayCount];

Try that out and see how close you get (note that I assumed that the two querries in refered to after the "FROM" each got you the results you needed).

-Sean

 

[Groundrush]

Sorted

Works now

thanks