inner join combined with outer join. (1 Viewer)

[Fuga]

Hi,

I´m trying to create an append query that I need to append only new records. This is done on an hourly basis, which means that the date should be an inner join and the hour should be an outer join, right? However, access can´t handle this so I need help.

If I do an append at 12 today, the query should select todays records from hours 0 to 11 for append. Next, when I do an append at 3 pm, the query should select todays records (inner join) but only those hours not already in the table (ie 12, 13, 14 etc (outer join)).

The date and the hour are in separate fields. I need to have it like that.

I can´t use PK:s because then access messes up my running sequence, which I also need.

Please don´t tell me I have to create a querydef each time.

Thanks
Fuga

 

[vbaInet]

You can either do a Unmatched query to find those records that do not exist in your first table

OR

Use an Outter Join and use the Is Null keyword.

 

[Fuga]

Thanks for your reply.

If I try that, access tells me a have ambiguous outer joins.

Fuga.

 

[vbaInet]

All the others must be Outter Joins too. So if you have an outter join pointing this way -> all the other joins must point this way too ->. Change all the other joins to outter joins pointing towards the same direction.

Also, you cannot have this -> and this <-

 

[Fuga]

Yes! Thanks a lot!

I had actually tried that, but I put "is null" in both fields, which gave me nothing of course. Great! Now it works.

Fuga

 

[vbaInet]

Great! Glad you got that working.