Simple SQL Syntax Problem: Help?

[DasaniBoy]

Hi there,

 

I am having issues with my code, was hoping you could help.  I'm writing a query for access, and I can't get the code below to work.  I know it's a stupid syntax issue, so could someone please tell me what I'm doing wrong?  I'm trying to use an inner join because B constantly changes, but I still want the dates to transfer to A, even when the entry is removed from B. 

 

update A.Covered=1, A.Date=B.Date 

WHERE SELECT B.ID from B INNER JOIN A as A_1 ON A_1.ID=B.ID WHERE A_1.Covered==0;

 

This is what I was trying to do:

ID's in A is searched in B.

If ID is in B, then copy date field from B into A’s Date field.  Also, Covered field would go to 1.

If ID is NOT in B, then don’t copy anything and remain blank; covered would also go to 0. 

Issue: B constantly adds and deletes entries, so in A if there is an ID that had a date entry, and B deletes that ID entry, then in A the covered field would go to 0, signifying it was dropped from B, while the date remains, signifying that it was there at one point, but not anymore.

 

[fenway]

Well, if you're looking for "NOT in B", you'll need a left join -- and then use a IF(), check for NULL, and update your two fields accordingly.l

[DasaniBoy]

How would I do that?

[fenway]

How would I do that?

hold on a sec... did you say access?