Prevent Double Entry

[James Beckham]

Hi guys,

When we do importing, the data will always be add onto the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added into
the "Revenue" Table)

Thanks~!

 

[John Nurick]

Hi James,

First, you should create one or more "no duplicates" indexes on your
existing table so that duplicate records cannot be imported. See my post
in the nearby thread "Automate data entry from sources" for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but link it or
import it to a temporary table
2) use an Append query to move only the new data to your main table.
3) if you've imported to a temporary table, use a Delete query to empty
the table ready for next time, and don't forget to compact the database
regularly.

 

[James]

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang

-----Original Message-----
Hi James,

First, you should create one or more "no duplicates" indexes on your
existing table so that duplicate records cannot be imported. See my post
in the nearby thread "Automate data entry from sources" for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but link it or
import it to a temporary table
2) use an Append query to move only the new data to your main table.
3) if you've imported to a temporary table, use a Delete query to empty
the table ready for next time, and don't forget to compact the database
regularly.

Hi guys,

When we do importing, the data will always be add onto the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi James,

Indexes should be the principal safeguard against duplicated data.
Remember that one index can incorporate several fields (in table design
view, select View|Index). For instance, a "no duplicates" index on
TransactionDate, AccountNumber and Amount would only disallow a new
record if there was already one with the same date, account number and
amount - which seems to be the sort of thing you're trying to achieve.

For append queries, search help for "Create an Append Query".

Good luck!

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang

-----Original Message-----
Hi James,

First, you should create one or more "no duplicates" indexes on your
existing table so that duplicate records cannot be imported. See my post
in the nearby thread "Automate data entry from sources" for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but link it or
import it to a temporary table
2) use an Append query to move only the new data to your main table.
3) if you've imported to a temporary table, use a Delete query to empty
the table ready for next time, and don't forget to compact the database
regularly.

Hi guys,

When we do importing, the data will always be add onto the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[James]

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec-03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James

-----Original Message-----
Hi James,

Indexes should be the principal safeguard against duplicated data.
Remember that one index can incorporate several fields (in table design
view, select View|Index). For instance, a "no duplicates" index on
TransactionDate, AccountNumber and Amount would only disallow a new
record if there was already one with the same date, account number and
amount - which seems to be the sort of thing you're trying to achieve.

For append queries, search help for "Create an Append Query".

Good luck!

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang

-----Original Message-----
Hi James,

First, you should create one or more "no duplicates" indexes on your
existing table so that duplicate records cannot be imported. See my post
in the nearby thread "Automate data entry from sources" for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table

but
link it or

import it to a temporary table
2) use an Append query to move only the new data to

your
main table.

3) if you've imported to a temporary table, use a

Delete
query to empty

the table ready for next time, and don't forget to compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

I can see that you will have multiple transactions all with the same
date, and multiple transactions with the same account number, and
multiple transactions for the same amount.

But can one account ever have more than one transaction for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index incorporating all
three fields.

If the answer is yes, how can you ever tell whether the two records
represent two separate transactions, or whether they represent just one
transaction that has been entered twice?

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec-03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James

-----Original Message-----
Hi James,

Indexes should be the principal safeguard against duplicated data.
Remember that one index can incorporate several fields (in table design
view, select View|Index). For instance, a "no duplicates" index on
TransactionDate, AccountNumber and Amount would only disallow a new
record if there was already one with the same date, account number and
amount - which seems to be the sort of thing you're trying to achieve.

For append queries, search help for "Create an Append Query".

Good luck!

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang


-----Original Message-----
Hi James,

First, you should create one or more "no duplicates"
indexes on your
existing table so that duplicate records cannot be
imported. See my post
in the nearby thread "Automate data entry from sources"
for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but
link it or
import it to a temporary table
2) use an Append query to move only the new data to your
main table.
3) if you've imported to a temporary table, use a Delete
query to empty
the table ready for next time, and don't forget to
compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto
the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added
into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Guest]

Hi John,

*******You exactly see my picture!!********
But can one account ever have more than one transaction
for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index
incorporating all
three fields.
*******************************************

I did as what you say--> I placed Yes(No Duplicates) on
[Txn Date],[Txn Fee],[Merchant Code]

Result:
Suppose to have 90 Records, it only gives me 2 records
when APPEND.
FYI: [Any field] seems to work (No Duplicates)individually


Thanks Pal, making you to think again. Sorry abt that !

-----Original Message-----
I can see that you will have multiple transactions all with the same
date, and multiple transactions with the same account number, and
multiple transactions for the same amount.

But can one account ever have more than one transaction for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index incorporating all
three fields.

If the answer is yes, how can you ever tell whether the two records
represent two separate transactions, or whether they represent just one
transaction that has been entered twice?

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec-03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James

-----Original Message-----
Hi James,

Indexes should be the principal safeguard against duplicated data.
Remember that one index can incorporate several fields (in table design
view, select View|Index). For instance, a "no

duplicates"
index on

TransactionDate, AccountNumber and Amount would only disallow a new
record if there was already one with the same date, account number and
amount - which seems to be the sort of thing you're trying to achieve.

For append queries, search help for "Create an Append Query".

Good luck!

On Mon, 29 Dec 2003 00:49:44 -0800, "James"

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang


-----Original Message-----
Hi James,

First, you should create one or more "no duplicates"
indexes on your
existing table so that duplicate records cannot be
imported. See my post
in the nearby thread "Automate data entry from sources"
for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but
link it or
import it to a temporary table
2) use an Append query to move only the new data to your
main table.
3) if you've imported to a temporary table, use a Delete
query to empty
the table ready for next time, and don't forget to
compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto
the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added
into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[James \(Thanks you John !\)]

Hey John,

Ignore my previous reply. I've solve the problem using
<<multiple-field index>> method.

Anyway..Thanks A Million Brother!
you've solve my problem

I think MS Access should start to have a special "Phd"
degree for that in Unisersity. It is really lots to learn
inside there.

Hmm...Care to introduce more about urself?

My name is James Pang
--> Singapore
--> currently still a Student under Industrial Attachment
--> Hobbies: All Sports

Nice to meet you...pls leave me your contact details...
hope i've a chance to repay you!

Cheers ~!

-----Original Message-----
I can see that you will have multiple transactions all with the same
date, and multiple transactions with the same account number, and
multiple transactions for the same amount.

But can one account ever have more than one transaction for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index incorporating all
three fields.

If the answer is yes, how can you ever tell whether the two records
represent two separate transactions, or whether they represent just one
transaction that has been entered twice?

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec-03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James

-----Original Message-----
Hi James,

Indexes should be the principal safeguard against duplicated data.
Remember that one index can incorporate several fields (in table design
view, select View|Index). For instance, a "no

duplicates"
index on

TransactionDate, AccountNumber and Amount would only disallow a new
record if there was already one with the same date, account number and
amount - which seems to be the sort of thing you're trying to achieve.

For append queries, search help for "Create an Append Query".

Good luck!

On Mon, 29 Dec 2003 00:49:44 -0800, "James"

Hi John,

It's me again (James and James Beckham are the same person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang


-----Original Message-----
Hi James,

First, you should create one or more "no duplicates"
indexes on your
existing table so that duplicate records cannot be
imported. See my post
in the nearby thread "Automate data entry from sources"
for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table but
link it or
import it to a temporary table
2) use an Append query to move only the new data to your
main table.
3) if you've imported to a temporary table, use a Delete
query to empty
the table ready for next time, and don't forget to
compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto
the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data? (where
only the new data(in my case New Month) will be added
into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Hi James,

I'm glad you've got it working.

You're not the only one who finds Access difficult to start with. The
concepts are more complicated and less obvious than those involved in
word processing or spreadsheets. But it's a tremendously powerful tool
when you understand it.

As for me, I'm just one of many Microsoft MVPs, volunteers who help
people with questions in these newsgroups.

Hey John,

Ignore my previous reply. I've solve the problem using
<<multiple-field index>> method.

Anyway..Thanks A Million Brother!
you've solve my problem

I think MS Access should start to have a special "Phd"
degree for that in Unisersity. It is really lots to learn
inside there.

Hmm...Care to introduce more about urself?

My name is James Pang
--> Singapore
--> currently still a Student under Industrial Attachment
--> Hobbies: All Sports

Nice to meet you...pls leave me your contact details...
hope i've a chance to repay you!

Cheers ~!

-----Original Message-----
I can see that you will have multiple transactions all with the same
date, and multiple transactions with the same account number, and
multiple transactions for the same amount.

But can one account ever have more than one transaction for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index incorporating all
three fields.

If the answer is yes, how can you ever tell whether the two records
represent two separate transactions, or whether they represent just one
transaction that has been entered twice?

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec-03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James




-----Original Message-----
Hi James,

Indexes should be the principal safeguard against
duplicated data.
Remember that one index can incorporate several fields
(in table design
view, select View|Index). For instance, a "no duplicates"
index on
TransactionDate, AccountNumber and Amount would only
disallow a new
record if there was already one with the same date,
account number and
amount - which seems to be the sort of thing you're
trying to achieve.

For append queries, search help for "Create an Append
Query".

Good luck!

On Mon, 29 Dec 2003 00:49:44 -0800, "James"

Hi John,

It's me again (James and James Beckham are the same
person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to make a
reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang


-----Original Message-----
Hi James,

First, you should create one or more "no duplicates"
indexes on your
existing table so that duplicate records cannot be
imported. See my post
in the nearby thread "Automate data entry from sources"
for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table
but
link it or
import it to a temporary table
2) use an Append query to move only the new data to
your
main table.
3) if you've imported to a temporary table, use a
Delete
query to empty
the table ready for next time, and don't forget to
compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto
the
existing records. The same data can appear more than 2
times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data?
(where
only the new data(in my case New Month) will be added
into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[James]

Hmm...That's cool to be a volunteer!

Yah, i truly agree that Access is very Powerful if you can
master it. I've also been a user of Macromedia Flash,
realising there are so much thing inside it. Can even do
programming in it...woah~

Anyway very thank you.

That's you personal email right? i have noted that down.

Cheers John,
May your wish come true~

Warmest Regards,
Brother James Pang

-----Original Message-----
Hi James,

I'm glad you've got it working.

You're not the only one who finds Access difficult to start with. The
concepts are more complicated and less obvious than those involved in
word processing or spreadsheets. But it's a tremendously powerful tool
when you understand it.

As for me, I'm just one of many Microsoft MVPs, volunteers who help
people with questions in these newsgroups.

Hey John,

Ignore my previous reply. I've solve the problem using
<<multiple-field index>> method.

Anyway..Thanks A Million Brother!
you've solve my problem

I think MS Access should start to have a special "Phd"
degree for that in Unisersity. It is really lots to learn
inside there.

Hmm...Care to introduce more about urself?

My name is James Pang
--> Singapore
--> currently still a Student under Industrial Attachment
--> Hobbies: All Sports

Nice to meet you...pls leave me your contact details...
hope i've a chance to repay you!

Cheers ~!

-----Original Message-----
I can see that you will have multiple transactions all with the same
date, and multiple transactions with the same account number, and
multiple transactions for the same amount.

But can one account ever have more than one transaction for the same
amount on the same day, e.g.
Txn Date, Acct Number, Amount
31-Dec-03, 1234, $999.99
31-Dec-03, 1234, $999.99
?

If the answer is no, create one "no duplicates" index incorporating all
three fields.

If the answer is yes, how can you ever tell whether the two records
represent two separate transactions, or whether they represent just one
transaction that has been entered twice?


On Mon, 29 Dec 2003 17:08:00 -0800, "James"

Rehi John,

Please take a look at my scenario. I can't do a "No
Duplicate" index as i can have repeated data, the only
unique key is Acct Number (which is an AutoNumber)

My Scenario:

I am doing obtaining all the revenue transaction done
during a month says "December". I specially have a field
which stores the Date of Transactions[Txn Date]. All these
txn revenue date will show as "31-Dec-03". I will then
have all the thousands of transaction showing 31-Dec- 03.
It's prefect !

Now...i would not want a "Double" import to happen (in the
case of new data is present, only those new data will be
added in).

Thanks Brother,
you have really facilitate me alot~!

Cheers,
James




-----Original Message-----
Hi James,

Indexes should be the principal safeguard against
duplicated data.
Remember that one index can incorporate several fields
(in table design
view, select View|Index). For instance, a "no duplicates"
index on
TransactionDate, AccountNumber and Amount would only
disallow a new
record if there was already one with the same date,
account number and
amount - which seems to be the sort of thing you're
trying to achieve.

For append queries, search help for "Create an Append
Query".

Good luck!

On Mon, 29 Dec 2003 00:49:44 -0800, "James"

Hi John,

It's me again (James and James Beckham are the same
person)

This definitely seem to answer both my question "Prevent
DOuble Entry" & "Automate data from sources".

Now...can you teach me how to go about your 2nd point -
"APPEND" method. Pls Advice

Any code that you have (in my context) for me to

make
a

reference (I've seen MS Access help file)

Thanks JohN~
Wish you all the Best~

Regards,
James Pang


-----Original Message-----
Hi James,

First, you should create one or more "no duplicates"
indexes on your
existing table so that duplicate records cannot be
imported. See my post
in the nearby thread "Automate data entry from sources"
for more.

For neatness, consider this in addition:
1) don't import the data directly to your main table
but
link it or
import it to a temporary table
2) use an Append query to move only the new data to
your
main table.
3) if you've imported to a temporary table, use a
Delete
query to empty
the table ready for next time, and don't forget to
compact the database
regularly.



On Sun, 28 Dec 2003 20:12:22 -0800, "James Beckham"

Hi guys,

When we do importing, the data will always be add onto
the
existing records. The same data can appear more

than
2

times, this causes my revenue figure to be inaccurate.

Qns:
How can i prevent the double importation of data?
(where
only the new data(in my case New Month) will be added
into
the "Revenue" Table)

Thanks~!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.