Query update

[Christopherusly]

I have a table which contain information about a report, part of the record set includes team members, some are qualified as team leaders, some as team members and then others as team observers.

I have used a query to create the list behind the drop down box to select the "person" for each of the positions.

(The queries are based on the yes/no value i.e. Dr Livingstone is set as yes for audit leader, member and observer and so will be availble to select, where as jane doe, is only set as an audit observer)

What i want to be able to do, is somehow update my query so it removes the name of person used (if they are set for the role) in say the Audit team leader, so you cannot select the same person to be both an team leader and a team member or observer.

Having had a look around i am yet to find an answer, but wonder if it is somehow possible to exclude a record entry based on if it has already been used.

Any suggestions are most glady appreciated. Thank you.

 

[Mile-O]

Do you, perchance, have three fields that are tickboxes in the table - one field called member, leader, etc?

 

[Christopherusly]

I decided upon a value list which contains "Yes";"No", the query is then based on

SELECT Tbl_Mytable.PersonID, Tbl_Mytable.name, Tbl_Mytable.Grade_ID, Tbl_Mytable.Leader, Tbl_Mytable.[COR Int]

FROM Tbl_Mytable

WHERE (((Tbl_Mytable.Leader)="yes"));

I used the value list, rather than the tick box, so it was perfectly clear to the manager using the database as to what they needed to do (it will never cease to amaze me how the simplist things are made complicated)

 

[Mile-O]

SELECT Tbl_Mytable.PersonID, Tbl_Mytable.name, Tbl_Mytable.Grade_ID, Tbl_Mytable.Leader, Tbl_Mytable.[COR Int]

FROM Tbl_Mytable

WHERE (((Tbl_Mytable.Leader)="yes"));

First of all, Name is not an advised name for a field as it is a reserved word. Use two fields - Forename and Surname. It's mcuch easier to combine two names than to split one apart.

Also, for the checkbox, the criteria is True or False depending on what you wanted.

 

[Christopherusly]

heh heh, duely noted



but back to my original question ...

anyone ?

 

[Christopherusly]

ok, so i now have a tick box for the position that each person fills ...

 

[Christopherusly]

Works a treat, many thanks I also must be getting the hang of the VBA (to some extent) as it is now making increasing sense to me)

Thanks once again.