Inserting unique records in MS access 2007
So, I searched around and didn't find many. I am sentencing ahead of time because this is probably the wrong way to do it, but this is what it is.
So I need to keep track of the class that employees completed. This is done via an excel sheet that passes the MS access database. There are 3 fields for me.
Full name, course title and completion date.
I know I don't have a primary key, so I am trying to create a query that will only add unique records retrieved from an excel sheet. I can do this based on one field, but I need help so that my query only adds it when both the full name and the course name do not match, for example
Joe Somebody, Course#1, 14feb13
Joe Somebody, Course#2, 15feb13
Joe Somebody, Course#1, 15feb13
I need a query that will add the first 2 rows to the table, but will ignore the third because the person has already completed course # 1. this is what I have so far, which basically turns my name field into a primary key.
INSERT INTO table [Full name], [Course], [Date]
SELECT excel_table.[Full name], excel_table.[Course], excel_table.[Date]
FROM excel_table
WHERE excel_table.[Full name] Not in (SELECT table.[Full Name] FROM table)
I also have some Is Not Null stuff at the end, but I didn't think it would be relevant to the question.
source to share
The easiest way to do this so you don't get duplicates is to add an index. In this case, the composite primary key would be the answer. Just select all the fields you want to include in the composite key and click the Primary Key button:
You will not be allowed zeros in any of the fields containing the primary key, but until the combination of fields is matched, the data in each of the fields can be repeated. So:
Joe Somebody, Course#1, 14feb13 <-- good
Joe Somebody, Course#2, 15feb13 <-- good
Joe Somebody, Course#1, 15feb13 <-- fails
Joe SomebodyElse, Course#1, 14feb13 <-- good
Now, if you run a regular append query assembly with the query design window, you will get an error if the entry exists twice in the Excel import table or already exists in Access:
source to share
You don't really need a composite primary key. In fact, there are several places in Access where you are encouraged not to use a composite primary key. You can create your Access table with a simple integer primary key:
create table CourseCompletions (
ID autoincrement primary key
, FullName varchar(100)
, CourseName varchar(100)
, CompletionDate date
);
Then you can gulp on all data from the Excel file:
insert into CourseCompletions (
, FullName
, CourseName
, CompletionDate
) select
[Full name]
, [Course]
, [Date]
from excel_table;
This will give each row of the Excel Excel spreadsheet a unique number and write it down in the Access table. Now you need to decide how you want to reject conflicting rows from the CourseCompletions table. (The following requests only show entries that you choose not to reject.) If you want to reject contributions by the same person on the same course at a later date:
select
ID
, FullName
, CourseName
, min(CompletionDate)
from CourseCompletions
group by
ID
, FullName
, CourseName;
If you want to reject completion at an earlier date, just change MIN to MAX.
If you want to reject any course completion that appeared earlier in the Excel spreadsheet:
select
cc1.ID
, cc1.FullName
, cc1.CourseName
, cc1.CompletionDate
from CourseCompletions as cc1
inner join (
select
max(ID) as WantedID
, FullName
, CourseName
from CourseCompletions
group by FullName, CourseName
) as cc2
on cc1.ID = cc2.WantedID;
And to reject course completion that appears later in the Excel spreadsheet, just replace MAX with MIN.
So using an integer primary key gives you some options.
source to share