Union Query

[FuzzyTom]

Hi,

I have this code for my SQL in creating this Union of two Queries:

Select EmployeeName, Day, TotalTime As Total, "TimeSheet" As Source
From qryTimeSheet
Where EmployeeName = "David Davia"
UNION
Select EmployeeName, Day, Total, "DailyActivity" As Source
From qryDailyActivity
Where EmployeeName = "David Davia";

The result is:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>qryTestUnion</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>EmployeeName</TH><TH bgColor=#c0c0c0 borderColor=#000000>Day</TH><TH bgColor=#c0c0c0 borderColor=#000000>Total</TH><TH bgColor=#c0c0c0 borderColor=#000000>Source</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/1/2011</TD><TD borderColor=#d0d7e5>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/1/2011</TD><TD borderColor=#d0d7e5>8.5</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/4/2011</TD><TD borderColor=#d0d7e5>7.5</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/4/2011</TD><TD borderColor=#d0d7e5>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/5/2011</TD><TD borderColor=#d0d7e5>8</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/5/2011</TD><TD borderColor=#d0d7e5>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/6/2011</TD><TD borderColor=#d0d7e5>8</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/6/2011</TD><TD borderColor=#d0d7e5>8.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/7/2011</TD><TD borderColor=#d0d7e5>2.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/7/2011</TD><TD borderColor=#d0d7e5>6.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/7/2011</TD><TD borderColor=#d0d7e5>8</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/8/2011</TD><TD borderColor=#d0d7e5>8</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>David Davia</TD><TD borderColor=#d0d7e5 align=right>4/11/2011</TD><TD borderColor=#d0d7e5>1</TD><TD borderColor=#d0d7e5>TimeSheet</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>DAVID DAVIA</TD><TD borderColor=#d0d7e5 align=right>4/15/2011</TD><TD borderColor=#d0d7e5>6.00</TD><TD borderColor=#d0d7e5>DailyActivity</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

If you look at record 9 and 10 they are both on the same Day and for the same Employee. I was wondering if I could get this Union Query to give me a total of 8 for 4/7/2011 for the employee David Davia.

The underlying queries have many more fields and therefore they are unique since an employee might have many clients and billing codes that he/she services on a particular day. I just pulled out the three fields in the Union so that I could run a Totals check against a particular day. I am just using the DailyActivity form to collect all of those activities and then checking that the total for the day is the same as the total entered in the TimeSheet form. If there is difference I would like to alert the Employee when they are entering the data on the TimeSheet form that the difference is either on the DailyActivities Form or the TimeSheet Form.

Thanks for any or suggestions.

Tom.

 

[Joe4]

Welcome to the Board!

Create a new Aggregate Query off of this Union Query, grouping on Employee Name, Day, and Source and taking the Sum of the Total field.

If you are not familair with Aggregate (Total) Queries, take a look at Access's built-in Help files on the subject.

 

[FuzzyTom]

Thanks Joe4 .... that did it.

Tom.