Adding personel numbers to vehicles

[JeroenMioch]

I have a table with lisenceplate numbers. Some of the licenplate numbers have multiple records.


A second field in the table is the personel number (if known).

What i want to do using a SQL insert statement is adding the personel number for any record that has the corresponding lincenseplate number.

An example of the table how it looks now :

Lisenceplate PersonelNumber

1-AAA-34
1-AAA-34 123456
1-AAA-34

After the SQL it needs to be like this :

1-AAA-34 123456
1-AAA-34 123456
1-AAA-34 123456

Any suggestions ?

[ItsMe]

I would copy some of the data to a new blank DB and practice creating an Action query in the new DB. When you create a new SELECT query using the Query Designer you can convert it to an action query using the tools within the Ribbon. Look for the tool that says 'Update'.

[JeroenMioch]

Im doing my best to solve this using your comment. However to no avail :P

I think i know how to go about it but im having difficulty on using the correct sytaxis for the SQL.
The fields i want the data to appear are empty fields, so im thinking an UPDATE sql wont do it as my experience is that UPDATE only works on fields that allready have a value.

So would a INSERT sql do it for me ?

[JeroenMioch]

Here is what i got so far (but im getting syntaxis error)

Dim strSQL As String

strSQL = "UPDATE tblWaarschuwingen as t1" _
       & "INNER JOIN tblWaarschuwingen as t2 ON" _
       & "t1.Kenteken2 = t2.Kenteken2 AND" _
       & "t2.Pnummer IS NOT NULL" _
       & "SET t1.Pnummer = t2.Pnummer"
       
DoCmd.RunSQL strSQL

[orange]

After the SQL it needs to be like this :

1-AAA-34 123456
1-AAA-34 123456
1-AAA-34 123456

Please explain this as it appears you now have 3 replicates/duplicates. This is not a condition you want in a relational database.

What relationship exists between Vehicles and Personnel?

A Person may be associated with 0,1 or Many Vehicles???
A Vehicle may be driven by Many Persons????

[ItsMe]

I tried a couple approaches to get it done on a query but was not successful in creating an updateable query. It seemed easier to me to use DAO and nest an updatable query within a loop. I would still create some queries and make sure that a plate number is not assigned two different personnel ID's and other possible referential/constraint issues.

So I ended up with the following. Not sure I got the names of the fields perfect but ...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPlate As String
Dim lngID As Long
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT Kenteken2, Pnummer FROM tblWaarschuwingen  WHERE Pnummer IS NOT NULL", dbOpenSnapshot)
 
rs.MoveFirst
    While rs.EOF = False
        strSQL = ""
        strPlate = rs![Kenteken2]    'Get the License Plate Number
        lngID = rs![Pnummer] 'Get the Personnel ID
        
        strSQL = "UPDATE tblWaarschuwingen SET Pnummer = " & lngID & " WHERE (((Kenteken2)='" & strPlate & "') AND Pnummer Is Null);"
        db.Execute strSQL
        
        rs.MoveNext
    Wend

rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Complete"

[JeroenMioch]

they are lisence plate numbers and personel numbers,
People park somewhere withouth us knowing who the owner is. Once we know we put the personel number behind the lisence plate. But there may be more then one warning given out.

I want the db to fill in the blanc spots without us having to do it manually.

Im gonna have a go at your code tomorrow mate, thanks in advance !

[JeroenMioch]

Hey ItsMe,

I have tested it just now and it does what it needs to, great piece of coding mate. I wish i could do it like that :P

I made a little adjustment because you defined Pnummer (Personnel number) as long while indeed its a string. Pnummer can hold a letter, like T3162 for example. You could offcourse not have known that.

Many thanks for solving my problem, i wish i was bright enough to have done it myself. Maybe one day .. hahaha

[ItsMe]

Glad you were able to get it to work for you.