CODEWITHSUNDEEP
sqldatabasems-access
user3651100
user3651100

Reputation: 29

Ms Access Gathering data from subdatasheet

Im trying to find a query which shows only fields with IS NULL from a subdatasheet column.

So far I have:

SELECT EmployeeId,FirstName,LastName,Salary 
FROM Employee
WHERE ServiceDate IS NULL

Within the EmployeeId there is an expandable subdatasheet and I'm trying to figure out how to call upon the "ServiceDate" which is within the subdatasheet to display which employee hasn't got a service. I hope this make sense.

EDIT: Here are the two tables:

https://i.sstatic.net/3LWSh.jpg
https://i.sstatic.net/8zSVS.jpg

Result I'm after:

EmployeeId  FirstName   LastName    Salary
E003        Ken         Moore       $59,000.00

Upvotes: 1

Views: 484

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

Try something on these lines:

SELECT Employee.EmployeeId,Employee.FirstName,Employee.LastName,Employee.Salary 
FROM Employee 
LEFT JOIN Services
ON Employee.EmployeeId = Services.EmployeeId
WHERE Services.EmployeeId IS NULL

Do not forget that MS Access has a Find Unmatched query wizard.

You might like to look at:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Upvotes: 1

Related Questions