querying unrelated tables
in [Queries]
|
Prev: Default Dates
Next: Storing time as integers
From: Keith on 3 Jun 2010 12:03 I have two tables - Employee_PunchTime table and Holidays Table. The PunchTime table contains three fields representing EmployeeID, Time-in and Time-out; and the Holiday table contains two fields representing name of holiday and the date. If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 is a paid holiday, I need to create a query that shows punched time and paid holiday on a separate column. On the paid holiday, there would obviously be no Time-In and Time-Out record. It will look something like this: EmpID Date Time-in Time-out Paid Holiday ------- ---- -------- --------- ------------ 1001 5/31/10 Memorial Day 1001 6/01/10 9:00 am 6:00 pm 1001 6/02/10 9:00 am 6:00 pm 1001 6/03/10 9:00 am 6:00 pm 1001 6/02/10 9:00 am 6:00 pm Any suggestions? Thank you. Keith
From: Jeff Boyce on 3 Jun 2010 12:37 Keith It sounds like you are saying that you want to see each employeeID and each date (on the "PunchTime" table), plus the time in/out if the date is not a holiday, and the holiday's name if it is. Note that I'm assuming that your punchtime table contains DATES as well as times... If that's an accurate paraphrase, then one approach would be to: 1. create a new query 2. add both tables 3. put the punchtime table fields in the output of the query 4. add one more output field to the query, containing something like (untest): HolidayName: IIF(PunchDate = HolidayDate, [HolidayName], "") You'd need to adjust this to match your fieldnames... Good Luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Keith" <Keith(a)discussions.microsoft.com> wrote in message news:C61EB2F9-6DD6-4522-B12D-EDD40E3B26DD(a)microsoft.com... >I have two tables - Employee_PunchTime table and Holidays Table. The > PunchTime table contains three fields representing EmployeeID, Time-in and > Time-out; and the Holiday table contains two fields representing name of > holiday and the date. > > If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 > is a paid holiday, I need to create a query that shows punched time and > paid > holiday on a separate column. > > On the paid holiday, there would obviously be no Time-In and Time-Out > record. > > It will look something like this: > > EmpID Date Time-in Time-out Paid Holiday > ------- ---- -------- --------- ------------ > 1001 5/31/10 Memorial Day > 1001 6/01/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > 1001 6/03/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > > Any suggestions? > > Thank you. > > Keith
From: KARL DEWEY on 3 Jun 2010 13:39 You should not use Date as a field name as it can give you problems. Try these two queries -- UNTESTED --- UNTESTED -- qryEmpAllDate -- SELECT Employee_PunchTime.EmpID, Employee_PunchTime.[Date] AS AllDate FROM Employee_PunchTime, Holidays GROUP BY Employee_PunchTime.EmpID, Employee_PunchTime.[Date] UNION SELECT Employee_PunchTime.EmpID, Holidays.[Date] FROM Employee_PunchTime, Holidays GROUP BY Employee_PunchTime.EmpID, Holidays.[Date]; SELECT qryEmpAllDate.EmpID, Employee_PunchTime.[Time-in], Employee_PunchTime.[Time-out], qryEmpAllDate.AllDate, [Paid Holiday] FROM (qryEmpAllDate LEFT JOIN Employee_PunchTime ON qryEmpAllDate.EmpID = Employee_PunchTime.EmpID AND qryEmpAllDate.AllDate = Employee_PunchTime.[Date]) LEFT JOIN Holidays.Holiday ON qryEmpAllDate.AllDate = Holidays.[Date]; -- Build a little, test a little. "Keith" wrote: > I have two tables - Employee_PunchTime table and Holidays Table. The > PunchTime table contains three fields representing EmployeeID, Time-in and > Time-out; and the Holiday table contains two fields representing name of > holiday and the date. > > If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 > is a paid holiday, I need to create a query that shows punched time and paid > holiday on a separate column. > > On the paid holiday, there would obviously be no Time-In and Time-Out record. > > It will look something like this: > > EmpID Date Time-in Time-out Paid Holiday > ------- ---- -------- --------- ------------ > 1001 5/31/10 Memorial Day > 1001 6/01/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > 1001 6/03/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > > Any suggestions? > > Thank you. > > Keith
From: John Spencer on 3 Jun 2010 14:01 Best I can think of right now is a Union query Parameters [Period Start] as DateTime, [Period End] as DateTime; SELECT EmpID, [Date], [Time-In], [Time-out], Null as [Paid Holiday] FROM WHERE [Date] Between [Period Start] and [Period End] UNION SELECT EmpID, HolidayDate, Null, Null, HolidayName FROM Employees, Holidays WHERE HolidayDate Between [Period Start] and [Period End] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Keith wrote: > I have two tables - Employee_PunchTime table and Holidays Table. The > PunchTime table contains three fields representing EmployeeID, Time-in and > Time-out; and the Holiday table contains two fields representing name of > holiday and the date. > > If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 > is a paid holiday, I need to create a query that shows punched time and paid > holiday on a separate column. > > On the paid holiday, there would obviously be no Time-In and Time-Out record. > > It will look something like this: > > EmpID Date Time-in Time-out Paid Holiday > ------- ---- -------- --------- ------------ > 1001 5/31/10 Memorial Day > 1001 6/01/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > 1001 6/03/10 9:00 am 6:00 pm > 1001 6/02/10 9:00 am 6:00 pm > > Any suggestions? > > Thank you. > > Keith
From: Keith on 3 Jun 2010 14:09
Thanks Jeff. I understand your logic, however, in your "HolidayName: IIF(PunchDate = HolidayDate, [HolidayName], "")" statement, PunchDate will never equal HolidayDate because no one would have punched-in or out on the holiday. Does this make sense? "Jeff Boyce" wrote: > Keith > > It sounds like you are saying that you want to see each employeeID and each > date (on the "PunchTime" table), plus the time in/out if the date is not a > holiday, and the holiday's name if it is. > > Note that I'm assuming that your punchtime table contains DATES as well as > times... > > If that's an accurate paraphrase, then one approach would be to: > > 1. create a new query > 2. add both tables > 3. put the punchtime table fields in the output of the query > 4. add one more output field to the query, containing something like > (untest): > > HolidayName: IIF(PunchDate = HolidayDate, [HolidayName], "") > > You'd need to adjust this to match your fieldnames... > > Good Luck! > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "Keith" <Keith(a)discussions.microsoft.com> wrote in message > news:C61EB2F9-6DD6-4522-B12D-EDD40E3B26DD(a)microsoft.com... > >I have two tables - Employee_PunchTime table and Holidays Table. The > > PunchTime table contains three fields representing EmployeeID, Time-in and > > Time-out; and the Holiday table contains two fields representing name of > > holiday and the date. > > > > If the payroll period is 5/31/2010 (Mon) thru 6/4/2010 (Fri) and 5/31/2010 > > is a paid holiday, I need to create a query that shows punched time and > > paid > > holiday on a separate column. > > > > On the paid holiday, there would obviously be no Time-In and Time-Out > > record. > > > > It will look something like this: > > > > EmpID Date Time-in Time-out Paid Holiday > > ------- ---- -------- --------- ------------ > > 1001 5/31/10 Memorial Day > > 1001 6/01/10 9:00 am 6:00 pm > > 1001 6/02/10 9:00 am 6:00 pm > > 1001 6/03/10 9:00 am 6:00 pm > > 1001 6/02/10 9:00 am 6:00 pm > > > > Any suggestions? > > > > Thank you. > > > > Keith > > > . > |