SQL - SELECT DISTINCT

[Kelvin]

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

[Bas Cost Budde]

Maybe you're out with a Totals query; you can group by Ref (in your
sample) and choose e.g. First for other fields. Sum(date) doesn't really
make sense does it?

Kelvin wrote:

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

--
Bas Cost Budde

What a melon scratcher!

The way I would tackle that problem would be to do a query for each of the fields, using the "distinct" parameter.
qryREF_field = "select distinct REF from tblWhatever"
qryNAME_Field = "select distinct NAME from tblWhatever"
etc...

Then would create another query using the table, and each of the separate queries for the fields.
Join the appropriate fields from the queries to the fields on the main table. The default join is what you want.
- So long as those joins are in place.

Then add in all of the fields from tblwhatever, and hopefully should work!? (no time to try it at the moment I'm sorry, but let me know how it goes and if it doesn't work will have a closer look tomorrow for you!)

Regards,

Fraser.

ke**************@dsl.pipex.com (Kelvin) wrote:

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

[Kelvin Middleton]

You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEmps -----------> tblLeavers
xxOrigRemovedxx xxDate&CommentsAddedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark Reed]

Try something like this:

select tblwhatever.*
from tblwhatever
where (((tblwhatever) In (select [Ref] FROM [tblwhatever] as tmp group by
[Ref] having Count(*)<2 )))
order by tblwhatever.Ref;

That would work for eliminating results where 1 field contains duplicate
info. If you wanted to include this criteria for every field then include
more sub-queries.
e.g.:

select tblwhatever.*
from tblwhatever
where (((tblwhatever.Ref) in (select [Ref] from [tblwhatever] as tmp group
by [Ref] having Count(*)<2 )) and ((tblwhatever.[Name]) in (select [name]
from [tblwhatever] as tmp group by [name] having count(*)<2 )))
order by tblwhatever.ref;
Mark
"Kelvin" <ke**************@dsl.pipex.com> wrote in message
news:17************************@posting.google.com ...

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

[Bas Cost Budde]

Maybe you should not use the employee ID as primary key (I guess you
don't already)

Take a two-step approach. Create a query that retrieves the oldest
employee IDs with their true PK, presumably some autonumber (I must
review SQL books on how exactly, maybe someone else here can show how.
Grouping on empID alone will not do the trick; first(date) will show the
first date, but how to retrieve the PK?)
On this query, create another that retrieves all other information about
the history.

Kelvin Middleton wrote:

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--
Bas Cost Budde

[Kelvin]

<fr***@clear.net.nz> wrote in message news:<40********@clear.net.nz>...

What a melon scratcher!

The way I would tackle that problem would be to do a query for each of the fields, using the "distinct" parameter.
qryREF_field = "select distinct REF from tblWhatever"
qryNAME_Field = "select distinct NAME from tblWhatever"
etc...

Then would create another query using the table, and each of the separate queries for the fields.
Join the appropriate fields from the queries to the fields on the main table. The default join is what you want.
- So long as those joins are in place.

Then add in all of the fields from tblwhatever, and hopefully should work!? (no time to try it at the moment I'm sorry, but let me know how it goes and if it doesn't work will have a closer look tomorrow for you!)

Regards,

Fraser.

ke**************@dsl.pipex.com (Kelvin) wrote:

Okay so this is baking my noodle. I want to select all the
attritbutes/fields from a table but then to excluded any row in which
a single attributes data has been duplicated.

I.E. Here's my table:-

ID Ref Name DATE
1 AAA Joe 1/2
2 BBB Ken 1/2
3 AAA Len 6/3

"SELECT DISTINCT * FROM tblWhatever ORDER BY Date;"

So I've read up and this statement doesn't work because all the
attributes in the record have to be the same in order for that record
to be omited from the results but they must be a way to do what I'm
after?

I'd appreciate the help,

Kelvin

Its a good idea but how would I maintain the relations ships between
ref and name if I'm querying them individually.

In my example above a "SELECT DISTINCT Ref FROM tblWhatever" would
return:-

Ref
AAA
BBB

And a "SELECT DISTINCT Name FROM tblWhatever" would return:-

Name
Joe
Ken
Len

So they don't match up, and don't forget I need to query them by order
of 'date'...just to make it even harder :-)

I've had an idea of creating the SQL to join back onto the same table
in a subquery and compare the REF field like that but although I've
heard of this before I've never actually joined a table back onto
itself before. Could this be a possibility?

I appreciate all this help folks, cheers.

Kelvin

[Jim Allard]

Hi,

Take all of this with a grain of salt; but I wouldn't remove employees
from the original table at all. I would merely have a field indicating
that they were or were not a current employee (in my apps, it is
usually just a "deleted?" field) and have a related table of leavers
that has the termination information. That way you never have to deal
with duplicate or incorrect keys, etc.

JimA
Kelvin Middleton <ke**************@dsl.pipex.com> wrote in message news:<40***********************@news.frii.net>...

You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEmps -----------> tblLeavers
xxOrigRemovedxx xxDate&CommentsAddedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Kelvin Middleton]

Cheers Jim but I can't leave them in the same table you see each
employee has a unique ID which is also what they use to access several
of our IT systems. As the system is quite old there is only a finite
number of these unique ID's and as such we re-allocate old ones from
leavers to new starters. So as I said these ID's are unique and thus
they are the Primary Key for each record so I have to remove the leavers
into another table when they go.

Thanks as well to Bas and Mark gunna try your ideas out soon, i'll let
ya know how I've got on.

Kelvin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[4Fraza]

Not trying to achieve much... ...Perhaps a better method might have been to keep unique ID's? For example, at my last job every employee had an employee number that was unique to them. It was used for payroll, etc. If you have something like that it might be a good idea to capture and use because it is truly unique.

However, that's not the case, and not the answer to your question, so here goes...

two queries.
First query gets the data from tblLeavers.
The second query should be an "unmatched records" query (You'll find there is a wizard for this in access). This query should filter out all of the records that are in the tblLeavers AND tblCurrentEmp tables.

You will then need to combine these queries into one... ...to do this you will need to ensure both of your two new queries have the same fields, and in the same order. Once this is the case, go into the SQL view of a new query and type along the lines of:

"SELECT *
FROM qrytblLeavers

UNION

SELECT *
FROM qrytblCurrentEmp_Unmatched;"

Then try that. That will take all of the records from tblLeavers, and only those records from tblCurrentEmp that don't have a matching ID in tblLeavers.

Hope that helps,

Fraser.

Kelvin Middleton <ke**************@dsl.pipex.com> wrote:

You're right Sum(date) isn't really any good to me but I understand your
train of thought. What it is I'm after doing is this. I have a
database of empolyees, everytime one of these employees leaves I appded
that employees details to a leavers table with additional information
such as the date they left and and comments pertaining to that person.
Then the original record is then removed from the current employees
table.

E.G.

tblCurrentEmps -----------> tblLeavers
xxOrigRemovedxx xxDate&CommentsAddedxx

Each employee has a unique ID which when they leave is freed up and can
get re-allocated to a new starter. So if I want to view historical
performance statistics based around the unique ID's of the employees I
have a problem as the ID's could have been re-allocated and a incorrect
name against the performance stats is displayed.

Hence if I can get a list out of my tblLeavers table of all the ID's
after a speific date ignoring any repeated ID's after the first has been
found then I can recompile an accurate list of employees at that point
in time.

Okay so I know this sounds a little mad but I believe the theory to be
sound. What do you think?

Kelvin

P.S. Thanks for your help by the way.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!