Get "X" records from one tbl and append to another

[Guest]

Hello,

This may sound familiar to some, but I have tried several approaches to this
problem. The VB approach is beyond my VB skillset given the timeframe I have
to get this done, so here goes an attempt using and append query...

I have tblCandidates and tblRRDExport.
1) Find the next record in tblRRDExport to be appended to tblCandidates.
This is signified by tblRRDExport.ImportDate being null.
2) Find the next "X" amount of record to be imported. "X" is inputed by the
user (if this is not possible use 250).
3) As the records are appended, make sure they do not already exist in
tblCandidates.Number. If the export number exists in Candidates, then mark
it as imported and get next record - careful to make sure a total of "X"
unique records are actually selected. All successfully imported records
should have tblRRDExport.ImportDate set to current date.
4) When "X" number of records are appended then end.

Start tblRRDExport:
Name PhoneNumber ImportDate
Doe John 5556667777
Doe Jack 1116667777 08042006
Doe Joe 3336667777
Doe Jane 2226667777

Start tblCandidates:
Candidate Number
Smith Mary 4445556666
Doe Jack 1116667777
Naft Connie 1112223333

Resultant tblRRDExport:
Name PhoneNumber ImportDate
Doe John 5556667777 08072006
Doe Jack 1116667777 08042006
Doe Joe 3336667777 08072006
Doe Jane 2226667777 08072006

Resultant tblCandidates:
Candidate Number
Smith Mary 4445556666
Doe Jack 1116667777
Naft Connie 1112223333
Doe John 5556667777
Doe Joe 3336667777
Doe Jane 2226667777

Thanks much!

 

[Jeff Boyce]

Rod

It sounds like you want to find "x" records in one recordset/table that are
NOT in another, then append those with a specific ImportDate.

Have you looked into the query wizard for "Missing..."?

Take a look at the TOP property of queries. This gives you a way of
qualifying the "missing ... query" to get some number of them. NOTE: you
HAVE to specify a sort order to get the TOP "x" rows you expect, otherwise,
Access chooses!

I'm not sure, right off the top of my head, how you'd gather an input value
for the TOP property as part of a query (in VB, sure, but in a query...?),
so you might have to use your "250" for this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

That sounds promising. I'll look into this...

Rod

It sounds like you want to find "x" records in one recordset/table that are
NOT in another, then append those with a specific ImportDate.

Have you looked into the query wizard for "Missing..."?

Take a look at the TOP property of queries. This gives you a way of
qualifying the "missing ... query" to get some number of them. NOTE: you
HAVE to specify a sort order to get the TOP "x" rows you expect, otherwise,
Access chooses!

I'm not sure, right off the top of my head, how you'd gather an input value
for the TOP property as part of a query (in VB, sure, but in a query...?),
so you might have to use your "250" for this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Start this by chosing "Create a query by using a wizard"?

 

[Jeff Boyce]

Rod

From the database window, click on the toolbar button that helps you create
a new ... object (you'll need to use the dropdown arrow to select a new
query). You get prompted to create a type of query, with wizard help.

Regards

Jeff Boyce
Microsoft Office/Access MVP