Append Query

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I have an append query that updates daily which is ran through a
macro. Is there away to prohibit duplicate information or prohibit a
accidental duplication if someone ran the query manually?

Ryan
 
Ryan

By "append" I assume you mean "add new records to". If so, add an index to
your recipient table (the one being added to). Use (no duplicates) on the
combination of fields (or maybe just one) that you figure would make it a
"duplicate".

When the macro runs more than once (or someone tries to launch the query
more than once), the unique index will reject those records already in the
table.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have an append query that updates daily which is ran through a
macro. Is there away to prohibit duplicate information or prohibit a
accidental duplication if someone ran the query manually?

Ryan
Click to expand...

Put a unique Index on the field or combination of fields which constitute a
duplicate in the table design window.
 
Thanks. It is alittle more complicated that this. I have a query that
pulls from another database. It pulls all the items for one of our
sales divisions, which is 42,000 items. This database table that I
pull refreshes itself daily, so tomorrows data will erase today's and
replace it with new 42,000 items and volumes.

it'll look like

item name, volume, price, date
12345, apples, 1,200, $3.45, 10/22/2008
12346, banana, 2,304, $0.70, 10/22/2008
12347, grape, 0, 0, 10/22/2008

The table will have all items, even if it has 0 volume. the "today's"
date will always show as today. So when I pull tomorrow it'll say

12345, apples, 0, $0.0, 10/23/2008
12346, banana, 1,100, $0.70, 10/23/2008
12347, grape, 5000, $1.26, 10/23/2008

then the next day will delete the 10/23/2008 day and override with the
10/24/2008 data. I don't know why it does this but it does. So i'm
making an append query in access to capture every daily activity. So
it'll look like this.

item name, volume, price, date
12345, apples, 1,200, $3.45, 10/22/2008
12346, banana, 2,304, $0.70, 10/22/2008
12347, grape, 0, 0, 10/22/2008
12345, apples, 0, $0.0, 10/23/2008
12346, banana, 1,100, $0.70, 10/23/2008
12347, grape, 5000, $1.26, 10/23/2008


Whereas the same item numbers are in there except the volumes and date
change. Now if I ran 10/23/2008 already, and someone else decided to
run the query manually and it duplicates all of the 10/23/2008 data,
how and where do I put the no duplicates in the table? This make
sense?

Ryan
 
item name, volume, price, date
12345, apples, 1,200, $3.45, 10/22/2008
12346, banana, 2,304, $0.70, 10/22/2008
12347, grape, 0, 0, 10/22/2008

The table will have all items, even if it has 0 volume. the "today's"
date will always show as today. So when I pull tomorrow it'll say
Click to expand...

Open the table in design view. Select the Indexes tool on the toolbar - looks
like lightning hitting a datasheet.

Put some name - UniqueRecord let's say - in the left column, and select Item
in the right column; on the second row of the grid leave the first column
blank and select Date in the second. Check the Unique checkbox and save the
index. Note that you'll get an error if you already have duplicates - they'll
have to be cleaned up first.

Now if you try to enter two records with the same item and date, the second
try will fail with an error message.
 
I did what you said, now I get an error message.

Microsoft Access set 0 fields to null.......and didn't add 2400
records to the table for key violations.....do I want to run anyways?
 
I did what you said, now I get an error message.

Microsoft Access set 0 fields to null.......and didn't add 2400
records to the table for key violations.....do I want to run anyways?
Click to expand...

Those are the duplicates that you said you didn't want to add.

You can trap or suppress the error message or replace it with one of your own.
 
Ok, thanks.


Those are the duplicates that you said you didn't want to add.

You can trap or suppress the error message or replace it with one of yourown.
Click to expand...
 
Back
Top