Update Table

[small brother]

Dear All
I need your help to make an update querrie.

I have the following tables:

1-Date Table : only 1 field dates
2- Customers Code: 2 fields (Code - Name)
3- Customers balances : Code - Name - Date and Balance.

I need to make an update querrie that seach date by date (From table 1) for
each customer (from table 2) if the customer exist for that date in the table
3 and if don't exist add a row for that customer : Code - Name - Date and
balance = 0

Thank you for your help.

 

[Michel Walsh]

That is not an update but an insert query. Untested, but should be something
like:



SELECT x.code, x.name, x.date, 0
FROM (SELECT code, name, date FROM table2, table1) AS x
RIGHT JOIN table3 AS y ON y.code = x.code
AND y.name = x.name
AND y.date = x.date
WHERE y.code IS NULL



which should list the records to be happened to table3. Once this is
checked, turn the select query into an insert into query (or add a first
line to read:

INSERT INTO table3(code, name, date, balance)
SELECT ...


)


Sure, it is preferable to use a backup before running a query that does
massive modifications, like it does here.



Hoping it may help,
Vanderghast, Access MVP

 

[small brother]

Thanks a lot Michel
I will try and hope that it will work for me.

 

[small brother]

Thanks a lot Michel
I will try and hope that it will work for me.