Find missing data

[Homegrownandy]

I want to find out quickly who has not submitted their timesheet. I have a list of dates representing week endings (sundays). This is in a table on its own and has no relationships.

Then I have a table of submitted timesheets. literally just the week ending date and how many hours that person has done. This links to the staff table which is just their names.



For EVERY staff member in this database they need to submit a timesheet EVERY week. I have an idea how I could do this for one person but not for all of them.

because; a relationship will exist if only one person submits for that week. I could check through name by name but I'm sure there's a better way. screenshot just for information. This doesn't do anything.

If anyone has any ideas I would love to hear them. Thanks.

Note: all dates for the week ending in the timesheet table will be an exact match. Any that don't match I will edit manually via the unmatched query to what it should be.

[ranman256]

you need an OUTER join. dbl-click the join between PERSON and TIMESHEET,
set it to ALL PERSONS, some timesheets.

then you will get null timesheet along with a person who didnt submit one.

[Homegrownandy]

the relationship needs to be between the sunday date and the timesheet weekending date I would assume?

Doing this DOES show where data is missing. but because its missing data it doesn't show the PO_ID or who's is missing.

what do you think?

[Homegrownandy]

I was wrong it only shows null where there is no data at all.

[Homegrownandy]

the closest I can come is to define the name in the first query then do the following:


this will show where data is missing for one person.

[aytee111]

Post # 3 - show PO id where WeekEnding is null and sundays = the date you are looking at.

[Homegrownandy]

If week ending is null then there is no PO ID as these are on the same record. also I want to see every Sunday a timesheet is missing. This isn't a big deal now, I can check one sunday as you suggested OR I can check all sundays for ONE person. So with a combination of the two ill keep on top of it.

But still if its possible I'm interested in ideas/.

[CJ_London]

this question has been asked before but cannot find the link.

You need two queries, a Cartesian query to show all possible combinations of sundays and employees. I don't understand your field naming so have used my own

Query1 (called cartqry here)

SELECT *
FROM Sundays, Employees

you can change the * to only include the fields you need but your Sundays table has a field called Sundays, always a bad idea as it will confuse Access somewhere down the line. You can also include a criteria to limit the query to one or more sundays

then you need another query to left join it to your timesheet data with criteria to show where the timesheet data is null

SELECT *
FROM cartqry LEFT JOIN timesheetdata ON cartqry.Sunday=timesheetdata.weekending AND cartqry.employeeID=timesheetdata.employeeID
WHERE timesheetdata.employeeID is null

Or you can do it in one query as follows

SELECT *
FROM (SELECT * FROM Sundays, Employees) cartqry LEFT JOIN timesheetdata ON cartqry.Sunday=timesheetdata.weekending AND cartqry.employeeID=timesheetdata.employeeID
WHERE timesheetdata.employeeID is null

[Homegrownandy]

Thank you very much. Its taken half a day of trial and error but I understand how it works. Really appreciate the help.

It was more difficult due to the fact I had several orders for one person but making a query to show the person only once resolved that.

Thanks again.