Need subquery to calculate on field containing date

[Yogibear]

I have an alumni database that contains tblBio and tblEmployment (1 to many relationship). I need a sub query that would only extract records whose start date tblEmployment) is within 2 years of grad date (tblBio). The tables are joined.



I have an Access consultant come in a few times a year to solve these problems for me, and she had created this subquery shown below:

It is not pulling just records that have a tblEmp start date within two years of tblBio grad date. It is pulling all records. We need to exlcude any job start date that was past two years of grad date.

I appreciate anyone willling to provide help. Thank you.

Susan

[orange]

Interesting -- you mention I have an alumni database that contains tblBio and tblEmployment but you show us a diagram of

tblEmp and subqryStartDate. It would be helpful to show readers all of the tables; and perhaps the SQL of the query that isn't working.

[Yogibear]

This is the SQL for the subquery:

SELECT tblEmp.ID, Min(tblEmp.EmployerStartDate) AS MinOfEmployerStartDate, Max(tblEmp.EmployerStartDate) AS MaxOfEmployerStartDate, IIf([MinOfEmployerStartDate]=[MaxOfEmployerStartDate],"one job","multiple jobs") AS Expr1, Count(tblEmp.ID) AS CountOfID
FROM tblEmp
GROUP BY tblEmp.ID
HAVING (((Min(tblEmp.EmployerStartDate)) Is Not Null));

Screen capture of all tables in the main query



Hope that is more helpful. Thank you.

[orange]

Here is something that may be helpful.

I mocked up2 tables"
tblBio
ID 0 Long 4
Fname 1 Text 255
LName 2 Text 255
GradDate 3 Date 8

and tblEmployment
ID 0 Long 4
EmpStartDate 1 Date 8
EmployerName 2 Text 255
BioIDFK 3 Long 4

I am attaching a jpg of the relationship between tblBio and tblEmployment


ere is the data in the tables.

ID	Fname	LName	GradDate
1	Porky	Pig	21/05/1978
2	Jesse	James	06/04/1984
3	Heesa	Payne	07/05/1994
4	Arya	Listnin	15/06/1996

ID	EmpStartDate	EmployerName	BioIDFK
1	23/09/1978	Great National	1
2	01/01/2000	MiniTech	1
3	05/08/2010	LargeTeck	1
4	16/10/1985	Walmart	2
5	20/04/1998	Target	2
6	10/08/1997	Napa Parts	3
7	01/12/1998	Cragar Auto	3
8	04/02/2001	Smith Bros.	4
9	15/09/2004	Vicks Inc	4
10	02/10/2009	Smuckers	4
11	16/07/1980	Peoples Trust	1

Here is the query SQL

SELECT tblBio.ID
    ,tblBio.Fname
    ,tblBio.LName
    ,tblBio.GradDate
    ,tblEmployment.EmpStartDate
    ,tblEmployment.EmployerName
    ,DateDiff("yyyy", [graddate], [EmpStartDate]) AS Diff
FROM tblBio
INNER JOIN tblEmployment ON tblBio.ID = tblEmployment.BioIDFK
WHERE (((DateDiff("yyyy", [graddate], [EmpStartDate])) <= 2))
ORDER BY tblBio.ID
    ,DateDiff("yyyy", [graddate], [EmpStartDate]);

Here is the output:

ID	Fname	LName	GradDate	EmpStartDate	EmployerName	Diff
1	Porky	Pig	21/05/1978	23/09/1978	Great National	0
1	Porky	Pig	21/05/1978	16/07/1980	Peoples Trust	2
2	Jesse	James	06/04/1984	16/10/1985	Walmart	1

Porky Pig has 2 employments within 2 years of Grad
Jesse James had 1
Heesa Payne and Arya Listnin had none.