Look if value is present

[JeroenMioch]

I hope someone can help me with the following:

I have a table with vehicle information (name, licenseplate number, personell number, make car, model car etc)


I have two other tables with information about a socalled milage reduction plan.

Any member if the milage reduction plan have received compensation for NOT using their car to go to work.
My employer has set up camera's that scans the vehicle licence plate numbers upon entering the carpark.

So, they want me to figure out wich employees still use their car more then once a week while they are have received compensation for not using the car to go to work.

I have this information in my database but i need to figure out a way to combine this data.
So i want the table with vehicle information to display Yes / no in a field IF their personell number exists in one of the other two tables.

I can see this information allready but at the form level by using Dlookup. So when i query a personell number in the database a checkmark on the form indicates if they exist in the two Milage reduction tables.
But this checkmark is unbound and thus i cannot see see the information in the vehicle information table.

How do i query the two tables, and then checkmark the vehicle information table if they exist in one of them ?
The point of all this is me having a list of licence plate numbers belonging to people that have received the compensation.

Currently, im adding licence plate numbers to the two tables manually ! But these are more then 600 people that i have to check. It is a lot of work and so i want to automate it.
If i have checkmarks in my vehicle information table i just have to select ascending / descending to get this list.

Hope its clear what i mean, any thoughts about it would be appreciated

Greetings, Jeroen

[June7]

Should not be necessary to enter a checkmark in the vehicle information table. A report should be able to produce the output based on a query.

We need to know more about your data structure and processes. How do you get the daily camera data into the database? I assume someone has to view the videos and type in every license number along with a date.

Do query that joins the vehicle info table with the daily log table and counts the personnel number for each week. Calculate the week number with DatePart("ww",datefield) and use that calculated field and personnel number to group the data

If you want to provide db for analysis, follow instructions at bottom of my post.

[JeroenMioch]

Well i made things a little easier today, but not alot haha.
I now have a list with personel numbers that participate in the milage reduction plan.
So basicly i made one table out of two, wich simplifies things a little.

I could make a query and put the license plate numbers behind the 600 personel numbers, that would be not all that difficult. But there is a catch why i cannot do this; A person (personel) can have more then one vehicle.
So i need to query the 600 personell numbers and mark those vehicles that they have all together.

How on earth do i do this ?

[June7]

My suggestion is basically the same.

However, you need a table that associates personnel number with each license number that employee has. Then join that table with the daily log in query and do weekly count.