Union, Crosstab or other Query to find missing data

[shuddle]

I have three tables: VOR (that has the month and year a report was submitted), Vehicle_Data (That gives the information on the vehicle, tag number), and User_Data (that shows who is driving the vehicle).

Each month the driver must submit a report that his vehicle is in working condition.

What I would like my query to do is find those who have NOT turned in their report for the month. Need a little guidance on what query to use and the logic behind it.

VOR Table:
VOR_ID
VEHICLE_ID
RPT_MONTH
RPT_YEAR

VEHICLE Table:
VEHICLE_ID
TAG

USER Table:
USER_ID
VEHICLE_ID


CURRENT_USER (yes/no)

[Bob Fitz]

I have three tables: VOR (that has the month and year a report was submitted), Vehicle_Data (That gives the information on the vehicle, tag number), and User_Data (that shows who is driving the vehicle).

Each month the driver must submit a report that his vehicle is in working condition.

What I would like my query to do is find those who have NOT turned in their report for the month. Need a little guidance on what query to use and the logic behind it.

VOR Table:
VOR_ID
VEHICLE_ID
RPT_MONTH
RPT_YEAR

VEHICLE Table:
VEHICLE_ID
TAG

USER Table:
USER_ID
VEHICLE_ID
CURRENT_USER (yes/no)

Can you post a copy of your db or at least show us a pic of your relationships window.

[shuddle]

I've attached a drawing of the relationships. I can't upload the database for security purposes.
.

[Bob Fitz]

Try:

SELECT Tbl_VOR.VehicleIDFROM Tbl_VOR
WHERE (((Tbl_VOR.Rep_Year)=2024))
GROUP BY Tbl_VOR.VehicleID
HAVING (((Max(Tbl_VOR.Rep_Month))<[Month]));

[Micron]

A table is an entity; it's fields are attributes of that entity. Things that do not define the entity should not be in that table ( as a general rule). VehicleID and current user don't describe/define the user and should not be there. If you can agree with that, then you can see that userID does not belong in the vehicle table either as a way to define who currently drives a vehicle. For that you'd have a junction table, and the better way to define who currently drives a particular vehicle in that table would be to have start and end dates for each combination of driver and vehicle. Not only is your data not properly normalized (IMHO) you have no way to retain history. Currently when you uncheck a user you cannot assign them a different vehicle unless you can have multiple userID's - yet you can't because it's a PK field.

All of that is likely the reason why you're having difficulty at present.
EDIT - I wouldn't have separate fields for year and month either. Maybe one is enough, or two if you want to know when it was due and when it was filed. It seems you're looking for that based on your post question. Easy enough if the due field is empty.

[madpiet]

Shuddle,

This would be way easier to explain if we had data (fake data is ideal... or remove any PII kinds of stuff... Just export the 3 or 4 tables (the short version) to a new database and upload that...

MonthList--(1,M)--MonthlyReport--(M,1)--Vehicle ---(1?,M?)--Driver

MonthList is basically a table of dates (like the first of the month for a period of time).

then it's super simple MonthList CROSS JOIN Vehicle returns something like this: (VehicleID, DueDate) and then you just subtract the Monthly Reports you have. (using maybe a LEFT JOIN between the (VehicleID, DueDate) and your MonthlyReport(VehicleID, SubmitDate) ).

if you can post a sample database with just the necessary tables, I can bang out a query for you to solve this.

The "MonthList" makes things infinitely easier, and you could just crank that out in Excel (start with 1/1/2024, end on the date you want, drag down, copy & paste into Access.) (Little wonder Excel is MSFT's moneymaker!)