fill in a table with missing data

[BMag]

I have a table [QUANTITY] with the following fields:
CatNum, LocID, MinQty, LocStored
CatNum is the foreign key to another table.

Each CatNum needs to be listed 4 times with the following different LocID fields:3S, 3R, 2C, 2S
Example #1:
[CatNum] [LocID] [MinQty] [LocStored]
888 3S 2 Locker2
888 3R 1 Shelf3B
888 2C 0 Shelf3F
888 2S 0 Shelf1D
999 3S 0 Locker3
999 3R 4 Shelf3A
999 2C 0 Shelf2C
999 2S 1 Shelf1B

Currently some CatNum's are listed as only 1 or 2 records with [LocID] null in some casses.
Example #2:
[CatNum] [LocID] [MinQty] [LocStored]
888 3S 2 Locker2
888 2C 0 Shelf2C
900 2S 0 Shelf1D
910
999 3S 0 Locker3
999 3R 4 Shelf3A
999 2C 0 Shelf2C
999 2S 1 Shelf1B

Is it possible to write a query or code to add records to the table to ensure that each CatNum is exactly4 records with the different required LocID's?
It would need to check for how many CatNum records exist and what if any data is in the LocID fields. The Existing data must remain intact with the exception of null [LocID] fields need to be populated.

Thanks for any suggestions or help.
Brent

 

[Jon K]

I think it is easier to do it in a select query than updating the existing table.

I have attached a database, in which I have added the four LocIDs in a table "tblLocID" and created a series of three queries.

You can run the third query. If you need a new table, you can change the third query into a make-table query.
.

 

[sportsguy]

is this a one time fix, or a permanent entry solution?

a one time fix is to create a temp table with all the existing
CatNum with the 4 locIDs.

Then run a Query where the temp table looks for all the missing
entries,, then paste them into the table. . .

but this is a one time fix. . . doesn't sound like you are looking for a one time fix.

sportsguy

 

[BMag]

This is a one time fix.