Current month & next month for multiple date fields (1 Viewer)

Amanda003

New member
Local time
Today, 08:40
Joined
Feb 14, 2014
Messages
16
I have an employee database that I am making and need a little help. Right now I have separate queries for Vacation Eligible, Benefits Eligible, and Birthdays pulling this month and next month for a total of 6 queries. Each query has Employee Status, EmployeeID, Employee Name and the corresponding date. I have tried to do a union query and was unsuccessful because when it does return a date I have no idea if it was a birthday, vacation or benefits date. I have also tried making one query with all 3 dates and get stumped on how to continue. My end goal is to run a report that will have the 3 fields listed and the employees with the current month criteria underneath. Is there a way to do this all in one query or two (current & next month) or am I stuck with a bunch of queries? Thank you in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:40
Joined
May 7, 2009
Messages
15,037
Join them together into 1 query.

SELECT A.EmployeeID, A.[Employee Name], A.[Employee Status],
B.[Vacation Eligible], C.[Benefits Eligible]
FROM (tblEmployee AS A
LEFT JOIN qryVacEligible AS B
ON A.[EmployeeID] = B.[EmployeeID])
LEFT JOIN qryBenefitsEligible AS C
ON A.[EmployeeID] = C.[EmployeeID];
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:40
Joined
May 21, 2018
Messages
5,908
When you do your union query you make an extra field in each select query by typing in the literal.
So each of you queries should have something like
Select "Birthday" as DateType, DateField, ...

Select "Vacation Day" as DateType, DateField,...

Select "Benefit Date" as DateType, DateField

Here is one where there was fields for the mother and the father. Then this was combined

Code:
Select
"Father" as Parent, [fldPLastName] & ", " & [fldFatherTitle] & " " & [fldFatherFirstName] as FullName
FROM
tblParents
UNION Select
"Mother" as Parent, [fldPLastName] & ", " & [fldMotherTitle] & " " & [fldMotherFirstName] as FullName
FROM
tblParents;

The results then had an identifier for which parent
Code:
Parent     FullName
Father      Smith,  Mr. John
Mother    Smith, Dr. Denise
 

Amanda003

New member
Local time
Today, 08:40
Joined
Feb 14, 2014
Messages
16
When you do your union query you make an extra field in each select query by typing in the literal.
So each of you queries should have something like
Select "Birthday" as DateType, DateField, ...

Select "Vacation Day" as DateType, DateField,...

Select "Benefit Date" as DateType, DateField

Here is one where there was fields for the mother and the father. Then this was combined

Code:
Select
"Father" as Parent, [fldPLastName] & ", " & [fldFatherTitle] & " " & [fldFatherFirstName] as FullName
FROM
tblParents
UNION Select
"Mother" as Parent, [fldPLastName] & ", " & [fldMotherTitle] & " " & [fldMotherFirstName] as FullName
FROM
tblParents;

The results then had an identifier for which parent
Code:
Parent     FullName
Father      Smith,  Mr. John
Mother    Smith, Dr. Denise
Thank you so much! That helped a bunch!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:40
Joined
May 7, 2009
Messages
15,037
maybe you need to see the "other" side.
see qryFinal on this demo.
note: the calculation are just made up.
 

Attachments

  • dates.accdb
    420 KB · Views: 63

Amanda003

New member
Local time
Today, 08:40
Joined
Feb 14, 2014
Messages
16
maybe you need to see the "other" side.
see qryFinal on this demo.
note: the calculation are just made up.
Thank you! I have a query with all the date already. MajP's solution was what I was after and was able to get rid of several queries and subs.
 

Users who are viewing this thread

Top Bottom