Re: Append Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


On Oct 22, 4:13 pm, "Jeff Boyce" <nonse...@xxxxxxxxxxxx> wrote:
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

<ryan.fitzpatri...@xxxxxxxxxxx> wrote in message

news:9f3239b1-a670-40c8-bd9f-c3397d2822fb@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: recordset displaying double entry issue.
    ... I am trying to display my clients ... always tell us what kind and version of database you are using. ... So the question is: are the duplicates ... If your answer is that the tables do not contain duplicates, but your query ...
    (microsoft.public.inetserver.asp.db)
  • Re: Adding auto-numbered field screws up Table order
    ... Append Queries are very simple, and part of the standard methodology ... Access's query designer is one of the great features. ... I know of no desktop database application tool with the equivalent power ... > things, like finding/isolating/deleting duplicates when ...
    (microsoft.public.access.tablesdbdesign)
  • Access Table Sorting/Query Last Function
    ... I've been designing a database that basically works off two tables. ... I have a query that pulls information and populates the ... in the query that pulls the information from the table. ...
    (microsoft.public.access.tablesdbdesign)
  • Access Table Sorting/Query Last Function
    ... I've been designing a database that basically works off two tables. ... I have a query that pulls information and populates the ... in the query that pulls the information from the table. ...
    (microsoft.public.access.gettingstarted)
  • Access Table Sorting/Query Last Function
    ... I've been designing a database that basically works off two tables. ... I have a query that pulls information and populates the ... in the query that pulls the information from the table. ...
    (microsoft.public.access.queries)