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.

 

[mikrom]

when the table is sorted by PID, this SQL-query works for me

select
   t1.PID,
   case
     when t1.CODE is not null then t1.CODE
     else (
        select t2.CODE from MYTABLE t2
        where t2.PID = (
          select max(t3.PID) 
             from MYTABLE t3
             where t3.PID < t1.PID and t3.CODE is not null        
        )
     )
   end,
   t1.SERIAL
from MYTABLE t1
;

(replace MYTABLE with your table name)

 

[strongm]

>this SQL-query works for me

It may do if your backend is SQL Server - but looks to me like the OP is using Access. In which case they don't have access to Case When - but can use Switch instead (assuming Access 2016 or later)

 

[mikrom]

Instead of the SQL CASE statement you could use IIF() in MS Access

select
   t1.PID,
   iif (not(IsNull(t1.CODE)),
        t1.CODE,
        (select t2.CODE from MYTABLE t2 where t2.PID = (
           select max(t3.PID)
             from MYTABLE t3
             where t3.PID < t1.PID and (not(IsNull(t3.CODE)))       
           )
        )
     ) as CODE,
   t1.SERIAL
from MYTABLE t1
;

 

[combo]

If your data is a copy from pivot table report and both Code and Serial are in row area, you can format set pivot table field (in this case code) to repeat labels - no need to fill field with empty entries.

 

[strongm]

>If your data is a copy from pivot table report

It is an import from an Excel table ...