Add zeros where there is unmatched record 1

[gemoon]

I am trying to replace unmatched/missing records in a table with zero values, but I not sure how to do it. Im sure I need to make an append query, but Im not sure where to start.

I have three tables A(date), B(count), C(item list).

The tables have the following fields
A: DateID, Date
B: DateID, Count, ItemID
C: ItemID, Item

Relationships:
Table A:B 1 to many
Table C:B 1 to many

For every item in table C there should be a corresponding count in table B for every record in table A. Where the records in B are missing I would like to add zero values with their associated DateIDs and ItemIDs.

For example:
Table A has 50 dates, table B has 250 counts , and table C has 60 items.

Table B should have 300 records, so 50 records with the correct DateIDs and ItemIDs need to be added.

Thanks for the help,
Ed.

 

[PHV]

Something like this ?
INSERT INTO B (DateID, [Count], ItemID)
SELECT A.DateID, 0 As myCount, C.ItemID
FROM A, C
WHERE NOT EXISTS
(SELECT * FROM B WHERE B.DateID=A.DateID AND B.ItemID=C.ItemID)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gemoon]

many thanks!