missing data

  • Thread starter Thread starter Ra
  • Start date Start date
R

Ra

I have the following table:

PART_ID LINE_ID PLANT_ID
A0322 AA1N PFM
A0322 AA1N PFM
A0322
A0322 AA1N PFM
B0316 BB1C PFM
B0316 BB1C
B0316 PFM
B0316 BB1C

Any way I can run a query with a function that fills up the two fields with
missing data in the following format:

PART_ID LINE_ID PLANT_ID
A0322 AA1N PFM
A0322 AA1N PFM
A0322 AA1N PFM
A0322 AA1N PFM
B0316 BB1C PFM
B0316 BB1C PFM
B0316 BB1C PFM
B0316 BB1C PFM

Thank you,
 
I have the following table:

PART_ID LINE_ID PLANT_ID
A0322 AA1N PFM
A0322 AA1N PFM
A0322
A0322 AA1N PFM
B0316 BB1C PFM
B0316 BB1C
B0316 PFM
B0316 BB1C

Any way I can run a query with a function that fills up the two fields with
missing data in the following format:

PART_ID LINE_ID PLANT_ID
A0322 AA1N PFM
A0322 AA1N PFM
A0322 AA1N PFM
A0322 AA1N PFM
B0316 BB1C PFM
B0316 BB1C PFM
B0316 BB1C PFM
B0316 BB1C PFM

Thank you,
Click to expand...

Can you be ABSOLUTELY CERTAIN that you will not have a situation such as

A0322 AA1N PFM
A0322
A0322 AC3Y BGX

which would leave the update ambiguous?

If so, run an Update query updating Line_ID to

=DLookUp("[Line_ID]", "yourtable", "[PART_ID] = '" & [PART_ID] & "' AND
[LINE_ID] IS NOT NULL")

and similarly for PLANT_ID.

This query will (sort of) work even if your data is in fact ambiguous, but it
will fill in the blanks with an arbitrarily selected LINE_ID and PLANT_ID from
among the possibilities.
 
SELECT PART.PART_ID,
(Select Max(LINE_ID)
FROM PART as P2
WHERE P2.PART_ID = PART.PART_ID) AS LINE_IDs,
(Select Max(PLANT_ID)
FROM PART as P2
WHERE P2.PART_ID = PART.PART_ID) AS PLANT_IDs
FROM PART
ORDER BY PART.PART_ID;

Change the table name of 'PART' to the correct name.
 
Back
Top