Select Distinct

[phpatrick]

Hello,

Problem : a person may only appear ones on the same day in a query, how do you say that, can you do this with a sql statement ?
fieldperson fieldDate
Jan 2006/08/06
Jan 2006/08/06 -> warning, not possible

Can we solve this via distinct, can you distinct on a field or a composed fiels (name + familyname)

help, thanks.

 

[fneily]

In your query design, right click the top portion of the query pane and select Properties. You'll see the options Unique Value and Unique Records. Change one of them to yes. Unique Value only looks at the fields you have in your query matrix. Unique Records looks at all the fields in the underlying recordset.

 

[Golom]

Can you clarify?

Are you asking for a constraint that limits what can be inserted in the base table? (i.e. do not allow more than one entry per day for a user?)

OR

Do you already have a table with multiple entries per day and you are just trying to display only one of them?

 

[phpatrick]

thanks fo your follow up !

I have a drop down list with persons. Once a person elected he will appear on the form and remain there for the day.
Many persons can be selected but however there should be only one record for a distinguished person.

I should avoid (100%) that Jan should get two record the same day. The drop down list containts all the persons, if one is selected a record is added in the table time for this person (a refField to person). On one day only one record for a person may be added.

 

[phpatrick]

It is this :

Are you asking for a constraint that limits what can be inserted in the base table? (i.e. do not allow more than one entry per day for a user?)

 

[Golom]

The most obvious way to do that is to make the User and date fields part of a unique, non-null key ... possibly even the primary key. If you attempt to do that while there are still duplicates in the table, it will fail. You will need to remove all duplicates from the table before you make such a change.

 

[phpatrick]

Many thanks ! I love easy solutions that fit 100%
I was trouble my head about it for many hours ...

 

[phpatrick]

I made one key of refPerson and day, but now I try to link to the ID_Person in the table Person. This is refused, I can't update my table Time

 

[phpatrick]

sorry, it was related to the fact that I had changed the query properties (unique values)