INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Complex SQL action query help

Complex SQL action query help

(OP)
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?

RE: Complex SQL action query help

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?


RE: Complex SQL action query help

(OP)
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.

RE: Complex SQL action query help

(OP)
Can't edit post.

The first condition is simple.

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

RE: Complex SQL action query help

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

How about:

Insert INTO tblOldBackup (CompanyID, CompanyName, Building)
Select Company.CompanyID, Company.CompanyName, Company.Building
FROM Company
WHERE Company.CompanyID NOT IN (Select CompanyID FROM tblOldBackup)

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.

RE: Complex SQL action query help

(OP)
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?

RE: Complex SQL action query help

(OP)
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.

RE: Complex SQL action query help

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

RE: Complex SQL action query help

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close