Appending Unique Data

[matethreat]

Hello,

Currently I have a table re-create every morning by importing information from an excel spreadsheet. I first run a delete query on the table then I run the macro to import all the up to date information. All of this works a charm.

What I want to be able to do now is using the job number from the above mentioned table, create a new record in a second table. This second table will hold information about the job entered by the user. So what I want it to be able to do is only append job numbers that were not previously in the table.

I guess my real problem is what criteria I would use in an append query to check if there are any new job numbers in table1 and if there are append them to table2. This may not be able to be done with a query, I might have to do it with code. Not sure where to start.

 

[wemeier]

Before deleting the main table, you could run a MakeTable query to create a temporary table of the job numbers currently in the main table. Then, delete the main table and import the new data. Next, left-join the jobs numbers in the reloaded main table with the job numbers in the temporary table and put to the second table any job number where there is no match from the temporary table. Finally, delete the temporary table.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[willir]

matethreat

... using the job number from the above mentioned table, create a new record in a second table ... only append job numbers that were not previously in the table...

If you create an index on the second table for JobNumber - make it unique, with no nulls, when you run your append query, it will fail to append for existing records.

If you have 100 jobs to append and 10 exist, only the 90 will be appended.

Do you need help with the append query?

Richard

 

[matethreat]

Hi Richard,

I think I do need soe help with creating that append query, as that's what I have been trying to do.

When you say an Index do you mean an auto-number or something along those lines. These job numbers are unique and I currently have them set as the primary key so I would have assumed they would act as my index.

???

 

[willir]

matethreat

No not autonumber - Job number is what needs to be set as unique, and since you have set it to the primary key, no further action is required - it should work. You will see a warning about all the number of records not appened to the second table.

To address your query issue.
- Start up the Query Builder.
- Add your first table.
- Select frmo the menu, "Query" -> "Append"
- Now double click on each field to copy from the first table to the second table. If the names match on the first and second table, then Access should correctly populate the "Append to" field.
- You probably want to save the query.
- Run the query ("!" menu item)

Richard

 

[matethreat]

Willir,

Doing one thing wrong can sure cause a lot of headaches. I thought it would be as simple as you suggested, I just couldn't figure out what I was missing.

Thanks,

Aaron

 

[wemeier]

willir,

I think matethreat wants the second table to be ONLY those that were added new that day. If that's true, your primary key scenario won't work.


Cruising the Information Superhighway
_{(your mileage may vary)}

 

[willir]

wemeier

I want it to be able to do is only append job numbers that were not previously in the table.

ONLY those that were added new that day

By creating a unique index on Job Number will prevent duplication of Job Numbers.

Did I miss something?

Richard

 

[wemeier]

Richard,

My mistake. I interpreted the objective of the second table to be holding ONLY the jobs that were newly entered (such as a "new today" table, possibly so additional information can be supplied).

If the objective is to add records from table 1 to table 2 so that table 2 holds EVERY job, including those newly added, without duplicating job numbers then your solution is correct.

Wally

Cruising the Information Superhighway
_{(your mileage may vary)}