Complex SQL action query help 2

[Goondu]

Hi,

I want to create a complex INSERT/APPEND action query SQL String.

Table Name - Company

Field Names -
CompanyID
CompanyName
Building

Table Name - TblOldBackup

Field Names -
CompanyID
CompanyName
Building

Conditions:

If records does not exist – INSERT/APPEND into tblOldBackup

If records exist but Fields does not match, INSERT/APPEND into tblOldBackup.

This is where the furthest I can go…

Insert INTO tblOldBackup ( CompanyID, CompanyName, Building)
Select Company.CompanyID, Company.CompanyName, Company.Building
FROM Company
WHERE Company.CompanyID = 1008

Anyone got ideas?

 

[lameid]

Your conditions aren't clear...

If records does not exist – INSERT/APPEND into tblOldBackup - If records does not exits - Append what?

If records exist but Fields does not match, INSERT/APPEND into tblOldBackup. - What fields do not match? Presumably the company table and something else? What is the Soemthing else?

 

[Goondu]

Thanks for replying,

Maybe it wasn't so clear.

If records not exist - insert records into tblOldBackup table - all fields.

If records exist but fields do not match - meaning to say if Field1 in Table Company does not match Field1 in Table tblOldBackup - Insert record into tblOldBackup - (insert all Fields into tblOldBackup from Company.)

It's sort of an audit table likeness.

When one or more Fields in Company is/are modified, I want it to insert the record into tblOldBackup. (all fields/columns). Hope you get what I mean.

 

[Goondu]

Can't edit post.

The first condition is simple.

If no record, insert record - all fields/column.

 

[Andrzejek]

If no record, insert record - all fields/column."

How about:
[tt]
Insert INTO tblOldBackup (CompanyID, CompanyName, Building)
Select Company.CompanyID, Company.CompanyName, Company.Building
FROM Company
WHERE Company.CompanyID NOT IN (Select CompanyID FROM tblOldBackup)
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Goondu]

Andrzejek,

The first condition isn't much of an issue.

I wondered if the first condition can be also part of the second condition. Meaning two condition into one SQL string. Possible?

The alternative is to use the Main Form's AfterUpdate event to trigger an Insert if the form is Dirty. But the problem is that the record might be the same or no changes as the user might be just changing it back to the original data.

Another alternative would be using 2 Recordsets by looping through each Fields/Columns until a Field/Column doesn't match. Not something I would like to do.

Does anyone have a clue on the second SQL string condition?

 

[Goondu]

Originally, my first Access unmatched query failed.

I think I have found a solution for the second condition from Access Unmatched query designer after visiting some web sites regarding unmatched queries.

Insert into tblPOBackup (Fields…..)
Select ..Fields…From Company LEFT JOIN tblOldBackup ON
(Company.CompanyID = tblOldCompany.CompanyID) AND
(Company.CompanyName = tblOldBackup.CompanyName) AND
(Company.Building = tblOldCompany.Building)
WHERE (((Company.CompanyID)="xxxx") AND ((tblOldCompany.CompanyID) Is Null));

It seems like I have to join all fields with the LEFT JOIN. This seems to work. Access designer doesn’t do multiple field joins. So I have to do one by one for all fields except for the criteria (Is Null) to remain without the append field. Will not work correctly without CompanyID criteria filter.

For the first condition, I think I have to live 2 SQL commands instead of having one SQL.

 

[lameid]

Joining all the fields should work for both... it is still null if it isn't there at all.

 

[Goondu]

Yeah, it seems that I don't need to 2 SQL for inserts.

So long it's altered or not in the backup table, it will execute else it does nothing.

Note of SQL string, some typo for table names.

Thanks.