Primary Key

A

Antonio

I have a table that I contains imported data from a text file that is linked
to the database. I need to automate the upload process so that 38 people in
the office can do the upload on their scheduled weekend. The problem I am
running into is no check that prevents the upload of the same days data more
than once. Example
Txt File @ 10:00 AM contains:
07/17/05 M0009999 Joe Smith 2nd Floor
07/17/05 M0008888 Sally Doe 3rd Floor
07/17/05 M0007777 John Doe Basement

At 03:30 PM the text file contains:
07/17/05 M0009999 Joe Smith 2nd Floor
07/17/05 M0008888 Sally Doe 3rd Floor
07/17/05 M0007777 John Doe Basement
07/17/05 M0006666 Betty Smith Rooftop

Is there a way to set the primary key as the Date and "M000####" Id so that
at 4 pm when the upload is re re run, it only uploads the record for "Betty
Smith"? At the same time, when Joe Smith turns up on the 7/18/05 list, his
data is allowed to be appended into the table because the date is not the
same as the 7/17/05 record?
 
B

Brian

You can set two fields together as a primary key. Just go to the table design
view, highlight both fields, and click the Primary Key button. What this does
is allow only one entry per COMBINATION of the two elements. In your example,
this would allow only one entry per person per day.

This, however, this does not solve the problem of how to prevent the system
from trying to import duplicate records and simly simply generating a
duplicate key error.

Instead, you need to filter out records that already exist using an unequal
join in your query. This is much easier to explain by example than by words,
so do the following as an example:

Create a Temp table having a FileDate field formatted as Short Date and a
Person field formatted as Text. Now make the FileDate & Person together the
primary key.

Copy the Temp table to a new table called Perm.

Enter some data into the Temp table (i.e. person/date combinations, using
date format like "08/01/05"); be sure to enter only one entry per person per
day, or you will get the duplicate key error.

Create a new query. Go to SQL view & paste this in, then return to design
view. Right-click on the join lines, then Edit to see how the unequal join is
done.

INSERT INTO Perm ( FileDate, Person )
SELECT Temp.FileDate, Temp.Person
FROM Perm RIGHT JOIN Temp ON (Perm.Person = Temp.Person) AND (Perm.FileDate
= Temp.FileDate)
WHERE (((Perm.FileDate) Is Null) AND ((Perm.Person) Is Null));

Run this once, then go back to Temp & add another entry for a different day
or person. Run the query again, and it will import only the ones where the
name/date combination does not already exist in Perm.

Apply this logic to your field names.
 
Top