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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

"Jeff Boyce" wrote:

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


"Rod" <Rod@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E274A9EE-859F-43A5-B140-E2A9B0FFFC4E@xxxxxxxxxxxxxxxx
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!



.

Relevant Pages

  • Re: Having Count(*)
    ... So if John Doe ... What you need to do is create another query that looks ... FROM tblMainDataBase ... INNER JOIN Query1 as Q ON T.Patient_ID = Q.Patient_ID ...
    (microsoft.public.access.queries)
  • Re: Get "X" records from one tbl and append to another
    ... You get prompted to create a type of query, ... "Jeff Boyce" wrote: ... Doe John 5556667777 ... Doe Jack 1116667777 ...
    (microsoft.public.access.queries)
  • Re: creating a sort field
    ... Here's the SQL for my query: ... Please provide the SQL ... Jon Doe EH106 12 1 ... in the sort field and so on. ...
    (microsoft.public.access.queries)
  • Re: Get "X" records from one tbl and append to another
    ... Find the next record in tblRRDExport to be appended to tblCandidates. ... Doe John 5556667777 ... Doe Jack 1116667777 ...
    (microsoft.public.access.queries)
  • Re: Get "X" records from one tbl and append to another
    ... then append those with a specific ImportDate. ... Find the next record in tblRRDExport to be appended to tblCandidates. ... Doe John 5556667777 ... Doe Jack 1116667777 ...
    (microsoft.public.access.queries)