CODEWITHSUNDEEP
mysqlms-access
divdeamor
divdeamor

Reputation: 99

Inserting unique records into MS access 2007

So i have been looking around and not finding much. I appologize ahead of time because this is probably the wrong way to do this but it is what it is.

So i have to track class's that co-workers have completed. This is done through a excel sheet that feeds the MS access database. There is 3 fields that are supplied to me.

Full name, Course Name, and Completion Date.

I know that i dont have a primary key here so i am trying to create a query that will only append the unique records pulled from the excel sheet. I can do it based on a single field but need help making my query append it only when both the Full name and Course Name are not the same for example

Joe Somebody, Course#1, 14feb13

Joe Somebody, Course#2, 15feb13

Joe Somebody, Course#1, 15feb13

I need a query that will append the first 2 rows to a table but ignore the third one due to the person already completing course#1. this is what i have so far that basicly 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 didnt think it would be relevent to the question.

Upvotes: 3

Views: 1879

Answers (2)

Yawar
Yawar

Reputation: 11637

You don't actually need a composite primary key. In fact there are a few places in Access where you are encouraged to not 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 in all the 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 your input Excel table a unique number and stash it in the Access table. Now you need to decide how you want to reject conflicting rows from your CourseCompletions table. (The following queries show only the records that you decide to not reject.) If you want to reject completions by the same person of 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 completions at an earlier date simply change the MIN to MAX.

If you want to reject any course completion that appeared earlier in the Excel table:

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 completions that appeared later in the Excel table, simply replace MAX with MIN.

So using an integer primary key gives you some options.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

The easiest way to do this so you do not get duplicates is to add an index. In this case, a composite primary key would seem to be the answer. Just select all of the fields you want included in the composite key and click the Primary Key button:

add composite primary key

You will not be allowed nulls in any of the fields comprising the primary key, but as long as the combination of the fields is not matched, 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 an ordinary append query build with the query design window, you will get an error if the record exists twice in the Excel import table or already exists in Access:

error on append

Upvotes: 2

Related Questions