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

R

Rod

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!
 
J

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
 
R

Rod

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

Jeff Boyce said:
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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top