Re: Compare Records & Move to new table in VBA

Thanks for your reply, it has helped greatly.

Another quesiton, similar topic;

If i write an sql query, can the field be a variable pulled from somewhere
else?

i.e. i have a form which lets the user select the field, can i pull that
value, and put it in the sql query?

Thanks

Tom

"Allen Browne" wrote:

Okay: you can do it by opening 3 recordsets:
- first source table;
- second source table;
- the target table.

Loop through the first source table.
FindFirst for the matching conditions on the second one.
AddNew (with Update) on the target table if it matches.

For an example of looping through a recordset, see:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

(I'm not yet convinced that it would not be a better job to dynamically
generate the SQL statement from the fields the user chose, and Execute it to
populate the target table. But perhaps it is more complex than the overview
you gave.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom" <Tom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:613F6A0B-B42F-4888-8498-3EB96EAB3615@xxxxxxxxxxxxxxxx
Thanks for your response, i know how to do it in queries, but i think i
need
to use VBA because i am firstly using user selected fields, (from a form)
and
secondly, im not checking for duplicates, im basically going through a
list
of transactions to find any returns (i.e. two transactions with same
product,
but the sum of their value = 0) and moving them (copy & delete) amongst
other
things.

So i need to know how to compare in VB, because the criteria in a query is
not complex enough to allow me to do everything i need to.

Thanks again

Tom

"Allen Browne" wrote:

You can do this in queries, without VBA.

Create a query, using 2 copies of the table.
If it's called tblClient, Access will alias the 2nd one as tblClient_1.

Drag the matching fields from tblClient onto tblClient_1.
Add criteria that the primary key is different.
For example, under ClientID of tblClient, enter:
<> tblClient_1.ClientID

That will give you the matching records.
Now turn it into a Append query (Append on Query menu), to add these
records
to the new table.

"Tom" <Tom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C1635B0-1218-4537-9353-91CB64C1707A@xxxxxxxxxxxxxxxx
I want to make a loop whereby i can compare the first record with every
other
record until i find a 'True' comparison, if i find this, move the
records
into a new table, otherwise, move onto the second record and repeat.

I figured i would do this with a for next loop, i have done them in
excel,
but im not certain of how to compare cells in access.


.

Relevant Pages

  • Re: Compare Records & Move to new table in VBA
    ... So i need to know how to compare in VB, because the criteria in a query is ... Create a query, using 2 copies of the table. ... If it's called tblClient, Access will alias the 2nd one as tblClient_1. ... Now turn it into a Append query, ...
    (microsoft.public.access.modulesdaovba)
  • Re: php and MySQL
    ... access to a 'members' page. ... I wish to compare the password entered by ... however i keep getting a mySQL error message 'query was empty'. ... The password match is already accounted for in the where clause of the SQL query. ...
    (comp.lang.php)
  • Re: Clarification of BytesSent vs BytesSentDelta in ISA 2004 Firewall log
    ... Would it be possible to please post the SQL query you used to get the ... > In ISA Server Management, I can get matching results between the Bytes ... > Remember that the data in the logs is per connection, ...
    (microsoft.public.isaserver)
  • Re: Many To Many Relationships
    ... INNER JOIN CATEGORY AS C ... An SQL query typically returns a table. ... Requires RVAs and an aggregate union. ...
    (comp.databases.theory)
  • Re: faster search engine for fulltext search
    ... Here is the SQL query I have used in a table with less than 200.000 records, ... FULLTEXT KEY `full` ... againstorder by date desc, rank desc limit 0,20; ...
    (perl.beginners)