Duplicate Question

[Jay]

I have a table that I want to append records to from another table.

StudentNumber EntryDate ExitDate


The problem is that I don't want to paste any records that have the
same StudentNumber, EntryDate, and ExitDate data in the 3 fields.



example:


StudentNumber EntryDate ExitDate
222 4/5/06 6/1/06
222 4/5/06 6/1/06
223 1/8/06 3/7/06
223 3/7/06 5/10/06


Only one record of the first two and the other two.
What I am trying to say is paste every record except those
records that are the same in their 3 fields.


I tried making the StudentNumber a PK but that would not
append one of the bottom two records. I need the StudentNumber
to allow duplicates in this table.

Can it be done in an append query and using select and how?

 

[Jay]

Try adding the DISTINCT predicate to the SELECT clause.

I tried and failed.
The problem is that the last2 records has the same StudentNumber,
but there are different entry and exit dates. I lose the record.

I guess what I am asking is there away to stop the distinct duplicate
record that is a duplicate across all of the fields in a record.
Looking above at StudentNumber 222, those 2 records are identical. The
statement below does work for 222.
However, 223 it does not because it will remove the second StudentNumber.
I am still researching and I am not sure if this can be done.


In (SELECT DISTINCT StudentNumber FROM tblStudents)
This one worked but I lost those records that have different entry
and exit dates.

 

[Marshall Barton]

I have a table that I want to append records to from another table.

StudentNumber EntryDate ExitDate


The problem is that I don't want to paste any records that have the
same StudentNumber, EntryDate, and ExitDate data in the 3 fields.

example:

StudentNumber EntryDate ExitDate
222 4/5/06 6/1/06
222 4/5/06 6/1/06
223 1/8/06 3/7/06
223 3/7/06 5/10/06


Only one record of the first two and the other two.
What I am trying to say is paste every record except those
records that are the same in their 3 fields.


I tried making the StudentNumber a PK but that would not
append one of the bottom two records. I need the StudentNumber
to allow duplicates in this table.

Can it be done in an append query and using select and how?

Try adding the DISTINCT predicate to the SELECT clause.

 

[John Spencer]

A COUPLE of ways.

Method one.
Your original method would work except you have to make a compound primary key
instead of a single field primary key.

Open the table in design mode and select all three fields and then click the
primary key button. Now do the append.

Method two - does not require you to do anything with the primary key.
Use an aggregate query (totals) query as the source

Group by your three fields (student number, entryDate, and Exit date) and use
First for the remaining fields.

 

[Jay]

A COUPLE of ways.

Method one.
Your original method would work except you have to make a compound primary
key instead of a single field primary key.

Open the table in design mode and select all three fields and then click
the
primary key button. Now do the append.

Method two - does not require you to do anything with the primary key.
Use an aggregate query (totals) query as the source

Group by your three fields (student number, entryDate, and Exit date) and
use First for the remaining fields.

It has worked.
I did Method2 and made it a Totals Query and a Make Table at the same time.
I have all of my correct records.

Thankyou