Append query problem

vladimir · July 22nd, 2003, 11:55 PM

Hello everyone,

I know that you can use a variable in the WHERE part of SQL statement (as criteria), but can you insert variable data to a field as part of the same SQL statement ?

Vladimir
Australia

Steven · July 23rd, 2003, 01:42 AM

Short answer - YES.

There's heaps of different ways you can do it - whether it's a saved query, using recordsets, or plain old code to do it.

What is it that you're wanting to do?

There are 10 kinds of people in the world - those who understand binary - and those with friends

vladimir · July 23rd, 2003, 06:26 PM

Hello everyone,

I'm posting the same problem on Access and Access VBA list, so here's the summary of the problem regarding the append query. I've "hit a wall" with this and not sure how to proceed, any opinion will be greatly appreciated.

My main table contains field TeamLeaderID as AutoNum, PK. Other fields are TeamLeaderName, Crew. The "child" table contains ID field as AutoNum, PK, TeamLeaderID link as LongInt and other fields. The 1 to many rel. is on TeamLeaderID and i have enforced all ref.integrity rules on the link.

When the user (new TeamLeader) carries over the jobs, he should go to entry form enter his details, then append non-complete jobs from previous day to subform. I think what is creating the problem is that previous days' jobs carry previous teamleader's ID link. If i append as it is, the jobs will remain linked to previous team leader. I need to find a way to append job details and insert new teamLeaderID for every appended job, so that data will show properly in both main and subform.

Vladimir
Australia

Steven · July 23rd, 2003, 07:04 PM

If I understand this correctly, this is what you're wanting to do:
Append records that are the same as existing records, but with a different TeamLeaderID. If this is right - read on - if not let me know.

It can be done with a simple insert statement - such as:

Code:

INSERT INTO MainTable (TeamLeaderID,Crew,Date,WhateverElse)
SELECT [Forms]![YourForm]![txtNewTeamLeader] as NewTeamLeader, Crew, Date, WhateverElse
FROM MainTable;

HTH

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends

vladimir · July 23rd, 2003, 07:58 PM

Steven,

Case of beer coming down your way to Melbourne....

Thank you very, very much ! The solution was a lot simpler and it works ! This thing had me thinking for 2 days and the code i came up with so far was like War and Peace compared to yours.

Vladimir
Australia