check table, if exist then update another table

[JeroenMioch]

Im breaking my head over something i cant figure out.

I have a table with 600 personel numbers. This table is called tblKRPdeelnemersTotaal

I have another table with 2600 personel numbers and their licence plate numbers. this table is called Parkeerbeheer.
Personel can have more then one vehicle, so they might appear twice or even more times in the table parkeerbeheer.

If i would put the licenceplatenumer behind the 600 people that would be an easy job, but i cant do it that way because of people having more then one vehicle.

So, i have to check the table tblKRPdeelnemersTotaal, look if a value is present (personell number)and then mark the field KRP in my table parkeerbeheer with preferably a Yes or No, but any value will do.

I tried it with a query but im not so good with query's.
I also tried it with a SQL statement on form_open() trying to update the 2600 records if the value is present in the other table.

ANY suggestion would be great, its driving me mad that i cant figure this out while i have much more sufiticated codes in my database(s).



Greetings, Jeroen

[recyan]

personellNumber is assumed as PK in tblKRPdeelnemersTotaal

Assuming this is a one time activity,
Have you tried building a Make Table query.

SELECT 
    Parkeerbeheer.TestID, 
    Parkeerbeheer.personellNumber, 
    "YES" AS KRP, 
    tblKRPdeelnemersTotaal.personellNumber 
    INTO 
    Parkeerbeheer_New_Table
FROM 
    Parkeerbeheer 
    INNER JOIN 
    tblKRPdeelnemersTotaal 
    ON 
    Parkeerbeheer.personellNumber = tblKRPdeelnemersTotaal.personellNumber;

Thanks

[JeroenMioch]

Hi Reycan, thanks for the reply.

Personellnumber is not the Pk in that table, but i can make that so.
In the other table parkeerbeheer i cant make it the PK because its not unique, the ID field is.
This is due to the fact that some people have more then one vehicle, thus their personell number would appear more then once in this table.

Ill give you maketable code a shot, thanks alot for thinking with me

I just tried it again with my form open code but i still cant quit make it yet :

    Dim str1SQL As String
    If DLookup("Pnummer", "tblKRPdeelnemersTotaal", [Pnummer] > "") Then
    str1SQL = "update Parkeerbeheer set [KRP] = 'ja'"
    DoCmd.RunSQL str1SQL
    Else
    str1SQL = "update Parkeerbeheer set [KRP] = 'nee'"
    DoCmd.RunSQL str1SQL
    End If

[recyan]

Personellnumber is not the Pk in that table, but i can make that so.

If it is logically a PK & is also stored in table parkeerbeheer & logically there is a relationship of one-to-many between tblKRPdeelnemersTotaal and parkeerbeheer on PersonnellNumber, then go ahead.

In the other table parkeerbeheer i cant make it the PK because its not unique, the ID field is.
This is due to the fact that some people have more then one vehicle, thus their personell number would appear more then once in this table.

In parkeerbeheer, I have assumed PersonnelNumber as FK, not as PK.

Edit :

Alternatively, also check below Make-Table query for guidelines

SELECT 
	Parkeerbeheer.TestID, 
	Parkeerbeheer.personellNumber, 
	tblKRPdeelnemersTotaal.personellNumber, 
	IIf([Parkeerbeheer.personellNumber]=[tblKRPdeelnemersTotaal.personellNumber],"Yes","No") AS KRP 
	INTO 
	Parkeerbeheer_New_Table_1
FROM 
	Parkeerbeheer 
	LEFT JOIN 
	tblKRPdeelnemersTotaal 
	ON 
	Parkeerbeheer.personellNumber = tblKRPdeelnemersTotaal.personellNumber;

Thanks

[JeroenMioch]

That worked out fine for except one thing and thats that i want the Yes /no to be placed in the Parkeerbeheer.KRP field.

At first i struggled because after the query ran i had only 300+ records. Stupid me because i didnt realise that not every member participating in the KRP (wich stands for Milage Reduction Plan, by the way) has a car, and thus has no match in the parkeerbeheer database.
As it turns out your query gives me the right data, just only not in the fields i want.

Is an append query maybe the way to go ?

Its not a one-time thing, as i have to deliver this data maybe once a month.

Anyways, thanks a million for your help so far. I really appreciate it mate !

[ssanfu]

Rather than change the name of the table each month, you could run two Update queries.

SETUP
Hopefully, the field "personellNumber" in both tables is an Integer or Long Integer.
- In table "Parkeerbeheer", set the Indexed property to "YES, No Duplicates".
- In table "Parkeerbeheer", set the Default Value property for the field KRP to "YES". Every time a new record is created, the KRP field value will be "No".

- In table "tblKRPdeelnemersTotaal", set the property to "YES, Duplicates OK"

Whenever you need to update the KRP field -
- Execute a query to set KRP field to "No" for all records.

UPDATE Parkeerbeheer SET Parkeerbeheer.KRP = "No";

- Then execute a query to match the personellNumber field in the two tables:

UPDATE Parkeerbeheer INNER JOIN tblKRPdeelnemersTotaal ON Parkeerbeheer.personellNumber = tblKRPdeelnemersTotaal.personellNumber SET Parkeerbeheer.KRP = "Yes";

[JeroenMioch]

Brillant Steve !!
That worked like a charm

Thank you all for helping me, you have no idea how i appreciate it.
I can program a lot of things through trial and error but im lost in query's

Thanks again !