Help with a Query. 1

[serino]

I need some assistance with this query and I am not sure how to go about it. I need to create a query that will fill the same "Code" where it is Null just like the lower screenshot.

 

[Andrzejek]

In my opinion, not enough information.
1. How did you get the list of Code/Serial data in the first screenshot?
2. Do you have a Primary Key field for that data?
3. Did you use any 'Order By' in your Select statement in the first screenshot? (Remember - your data is not ordered unless you order it)

 

[serino]

1. Imported data from excel into a table.
2. Yes on Primary key.



3. I did not use an orderby. This is the query:
SELECT Import_PT1_Table.PID, Import_PT1_Table.Code, Import_PT1_Table.Serial
FROM Import_PT1_Table;

 

[Andrzejek]

I don't know how to do that with a query, but you can run this little code which should update your Code field, especially if it is just one time deal: (code not tested)

Dim strSQL As String
Dim strCode As String

strSQL = "SELECT PID, Code, Serial FROM Import_PT1_Table Order By PID"

With rst
    .Open strSQL, MyDB
    Do While Not .EOF
        If Not IsNull(!Code.Value) Then
            strCode = !Code.Value
        Else
            strSQL = "Update Import_PT1_Table Set Code = '" & strCode & "' Where PID = " & !PID.Value
            MyDB.Execute strSQL
        End If
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing

BTW, you should really normalize your DB, which means - establish another look-up table with:

tblCode
ID   Code
1    ABD
2    ABJ
3    ABU
4    ACC
5    XYZ

and use it as a reference to your Import_PT1_Table

 

[serino]

I placed the code on the event procedure for a command button and I received an error "Object Required" for "MyDB". If I create a lookup table as you suggested how do incorporate that lookup table i.e table2 into this code?

 

[Andrzejek]

You may try:

Dim rst As Recordset
...
Set rst = CurrentDb.OpenRecordset(strSQL)
...

"how do incorporate that lookup table i.e table2 into this code?"
Well, you don't.
The data in your Code field (in Import_PT1_Table) would become 1, 2, 3, etc. corresponding to the records in your look-up table (tblCode?) And you would establish the relation (PK/FK) between Import_PT1_Table.Code(FK) to tblCode.ID(PK)

 

[serino]

Thank you for patience and your help! Now i am receiving the following error.

 

[Andrzejek]

Comment out that line. You don't needed it anymore.

and you may want to change:
MyDB.Execute strSQL
to
CurrentDb.Execute strSQL

 

[serino]

It worked! Thank you for you help on this. Have a great weekend.