A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

querying unrelated tables



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 05:03 PM posted to microsoft.public.access.queries
Keith
external usenet poster
 
Posts: 531
Default querying unrelated tables

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
  #2  
Old June 3rd, 2010, 05:37 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default querying unrelated tables

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" wrote in message
...
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



  #3  
Old June 3rd, 2010, 06:39 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default querying unrelated tables

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

  #4  
Old June 3rd, 2010, 07:01 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default querying unrelated tables

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

  #5  
Old June 3rd, 2010, 07:09 PM posted to microsoft.public.access.queries
Keith
external usenet poster
 
Posts: 531
Default querying unrelated tables

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" wrote in message
...
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



.

  #6  
Old June 3rd, 2010, 07:39 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default querying unrelated tables

Keith

So you're saying that the daterange does NOT have "empties" for the dates
that are holidays, counter to my assumption.

I didn't see anything describing where you are storing the "pay period".
Unless you have that, how will you (or Access) know where to look for
"holidays"?

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" wrote in message
...
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" wrote in message
...
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



.



  #7  
Old June 3rd, 2010, 08:36 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default querying unrelated tables

Might help if I included the table name
Parameters [Period Start] as DateTime, [Period End] as DateTime;
SELECT EmpID, [Date], [Time-In], [Time-out], Null as [Paid Holiday]
FROM Employee_PunchTime
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

John Spencer wrote:
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

  #8  
Old June 4th, 2010, 07:13 PM posted to microsoft.public.access.queries
Keith
external usenet poster
 
Posts: 531
Default querying unrelated tables

Thank you John.

Your suggestion seems to to work. I just need to tweak the query a little to
suit to my tables.

Thanks again.

"John Spencer" wrote:

Might help if I included the table name
Parameters [Period Start] as DateTime, [Period End] as DateTime;
SELECT EmpID, [Date], [Time-In], [Time-out], Null as [Paid Holiday]
FROM Employee_PunchTime
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

John Spencer wrote:
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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.