By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

Checking for doubles

P: n/a
I've made an import macro to import bank transactions. Those transactions
don't have an ID_number when downloaded from internet. They get it when they
are added to the main transaction table in my database. When adding them I
would like to check if they haven't accidentally been added already, based
on account nr, date, amount of money, and payers info. What would be the
best way of doing this without actually having to program in VB?
Thanks in advance,
john
Aug 27 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
standard way is to either link to the existing datasource or import to
a temporary table. Then run the Find Unmatched query wizard.
(basically it's an outer join on the fields in common). Then you'd
turn that query into an append query. and probably create a macro/some
code to run the import of the raw data, shut off warnings, run the
append (the outer join query), and then turn the warnings back on and
then maybe show a message.
No VB _required_, but VB is a lot more flexible than macros. If you
want, you can create the whole thing using macros and then convert it
to VB.

Aug 27 '06 #2

P: n/a
"john" <jo**@test.comwrote in
news:Aa********************@casema.nl:
I've made an import macro to import bank transactions. Those
transactions don't have an ID_number when downloaded from
internet. They get it when they are added to the main
transaction table in my database. When adding them I would
like to check if they haven't accidentally been added already,
based on account nr, date, amount of money, and payers info.
What would be the best way of doing this without actually
having to program in VB? Thanks in advance,
john
The simplest way would be to create a unique index in the table on
the fields account nr, date, amount of money, and payers info.
Then the database will object to those records which match those
fields. Just select the option to continue an the warning...

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 27 '06 #3

P: n/a
Thanks guys. I'll look into that...
john

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...
"john" <jo**@test.comwrote in
news:Aa********************@casema.nl:
>I've made an import macro to import bank transactions. Those
transactions don't have an ID_number when downloaded from
internet. They get it when they are added to the main
transaction table in my database. When adding them I would
like to check if they haven't accidentally been added already,
based on account nr, date, amount of money, and payers info.
What would be the best way of doing this without actually
having to program in VB? Thanks in advance,
john
The simplest way would be to create a unique index in the table on
the fields account nr, date, amount of money, and payers info.
Then the database will object to those records which match those
fields. Just select the option to continue an the warning...

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 28 '06 #4

P: n/a
"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...
"john" <jo**@test.comwrote in
news:Aa********************@casema.nl:
>I've made an import macro to import bank transactions. Those
transactions don't have an ID_number when downloaded from
internet. They get it when they are added to the main
transaction table in my database. When adding them I would
like to check if they haven't accidentally been added already,
based on account nr, date, amount of money, and payers info.
What would be the best way of doing this without actually
having to program in VB? Thanks in advance,
john
The simplest way would be to create a unique index in the table on
the fields account nr, date, amount of money, and payers info.
Then the database will object to those records which match those
fields. Just select the option to continue an the warning...
In the secundary index on multiple fields I've set the 'ignore null values'
setting to No, but still I'm able to enter the fields as shown below in 2
records (leaving field2 empty), which is not what I want.
Field1 Field2 Field3
1 1
1 1
'Ignore null values' set to Yes gives the same result.
What am I doing wrong?
john
Aug 28 '06 #5

P: n/a
In the secundary index on multiple fields I've set the 'ignore null
values' setting to No, but still I'm able to enter the fields as shown
below in 2 records (leaving field2 empty), which is not what I want.
Field1 Field2 Field3
1 1
1 1
'Ignore null values' set to Yes gives the same result.
What am I doing wrong?
john
Btw: The setting Unique is set to Yes.
john
Aug 28 '06 #6

P: n/a
"john" <jo**@test.comwrote in
news:SL******************************@casema.nl:
>In the secundary index on multiple fields I've set the
'ignore null values' setting to No, but still I'm able to
enter the fields as shown below in 2 records (leaving field2
empty), which is not what I want. Field1 Field2 Field3
1 1
1 1
'Ignore null values' set to Yes gives the same result.
What am I doing wrong?
john

Btw: The setting Unique is set to Yes.
john
With the index: nothing. Since by definition Null does not equal
another null, the two records can coexist. Set the field level
properties to allow nulls =no.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 28 '06 #7

P: n/a
Thanks!
john

"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht
news:Xn**********************@66.150.105.47...
"john" <jo**@test.comwrote in
news:SL******************************@casema.nl:
>>In the secundary index on multiple fields I've set the
'ignore null values' setting to No, but still I'm able to
enter the fields as shown below in 2 records (leaving field2
empty), which is not what I want. Field1 Field2 Field3
1 1
1 1
'Ignore null values' set to Yes gives the same result.
What am I doing wrong?
john

Btw: The setting Unique is set to Yes.
john
With the index: nothing. Since by definition Null does not equal
another null, the two records can coexist. Set the field level
properties to allow nulls =no.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 30 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.