clean up a linked table

[krberube]

Hi all,
I have a linked table ( see attached image ). In it are several lines with the same invoice number. 1 line with this invoice number has some info I need to retrieve by serial number ( but this line doesn't contain the S/N). My question I guess is how can I retrieve a SerialNumber along with the 2 right most columns from the image?
IE: 0067320, A20900, 502060, 2120202019 23

I hope this makes sense. I was thinking of using a make table query to somehow clean this up, but not sure what to place in the query.
any other thoughts?
Thanks
Kevin

 

[neileg]

Obviously it's the field containing 0067320 that's the link. I think the following will work.

Create a query that extracts column 1 (0067320), column 3 (163700), column 6 & 7 (502060 & 2120202029_23). Add a criterion to column 3 'Is Not Null'.

Create another query that extracts column 1 (0067320), and column 2 (A209200). Add a criterion to column 2 'Is Not Null'.

Create a third query and add both queries to this. Add a relationship between the two queries linking column 1 in each case. Then add the 4 fields you want to the query grid.

Bob's your uncle, as they say.