SELECT [Date] as theDate
, [Name] as theName
, Format(Pay, "Currency")
, "Salary Only" as Source
FROM SALARY as S LEFT JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.[Name]
WHERE H.[Date] is Null
UNION ALL
SELECT [Date] as theDate
, [Name] as theName
, Format(Pay, "Currency")
, "Hourly Only" as Source
FROM Hourly as H LEFT JOIN Salary as S
ON H.[Date] = S.[Date] and
H.[Name] = S.[Name]
WHERE S.[Date] is Null
UNION ALL
SELECT S.[Date]
, S.[Name]
, Format(Nz(S.Pay,0) + Nz(P.Pay,0), "Currency") as TotalPay
, "BOTH" as Source
FROM Salary as S INNER JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.Name
That will convert the pay into a formatted string. If you need to be able
to do arithmetic on the value, try
SELECT [Date] as theDate
, [Name] as theName
, CCur(Pay)
, "Salary Only" as Source
FROM SALARY as S LEFT JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.[Name]
WHERE H.[Date] is Null
UNION ALL
SELECT [Date] as theDate
, [Name] as theName
, CCur(Pay)
, "Hourly Only" as Source
FROM Hourly as H LEFT JOIN Salary as S
ON H.[Date] = S.[Date] and
H.[Name] = S.[Name]
WHERE S.[Date] is Null
UNION ALL
SELECT S.[Date]
, S.[Name]
, CCur(Nz(S.Pay,0) + Nz(P.Pay,0)) as TotalPay
, "BOTH" as Source
FROM Salary as S INNER JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.Name
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Rick Stahl said:
Great ! Thank You, John ! Got it working nicely.
One other minor thing: I was wondering if I could somehow format PAY to
display in currency.
Have a Wonderful Christmas !
John Spencer said:
SELECT [Date] as theDate
, [Name] as theName
, Pay
, "Salary Only" as Source
FROM SALARY as S LEFT JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.[Name]
WHERE H.[Date] is Null
UNION ALL
SELECT [Date] as theDate
, [Name] as theName
, Pay
, "Hourly Only" as Source
FROM Hourly as H LEFT JOIN Salary as S
ON H.[Date] = S.[Date] and
H.[Name] = S.[Name]
WHERE S.[Date] is Null
UNION ALL
SELECT S.[Date]
, S.[Name]
, Nz(S.Pay,0) + Nz(P.Pay,0) as TotalPay
, "BOTH" as Source
FROM Salary as S INNER JOIN Hourly as H
ON S.[Date] = H.[Date] and
S.[Name] = H.Name
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Rick Stahl wrote:
I have 2 queries one HOURLY and one SALARY. Each contains three
identical named fields DATE, NAME, and PAY. Some Names are unique to
either Hourly or Salary but some are contained in both. I wish to join
these two queries such that for each Date every Name and Pay is listed
and if the Name is contained in both queries then determine the Sum of
Pay.
Thank you in advance for any help.
