Access 2003 "IF" function question

Many Thanks John. Thankyou soo much for being Patient.

That works like a dream. I have a query pulling all the information I need
to figure out what to pick and then a drop down box linked to that query

"John W. Vinson" wrote:

On Wed, 1 Jul 2009 16:59:00 -0700, Nadihaha
wrote:

Thanks John,

This will work for now we don't have any last name duplicates. However I
forsee a problem. I currently have the last name and the first neame stored
in separate fields (everything I've read so far about access says this is
what I should do) However what will happen when we get bigger and do
encounter name duplicates?

Store a unique ID, the last name, the first name, and some other field or
fields that would let you identify the person: nickname, department, position,
phone number, whatever works in your situation. You can display up to ten
fields in a combo box; and you can and should concatenate the actual name
fields into one, e.g. a query like

SELECT PersonID, [LastName] & ", " & [FirstName] & (" " + [MiddleName]) AS
FullName, [Department], [Position] FROM peopletable ORDER BY LastName,
FirstName, MiddleName;

Adjust the field and table names and use this as the rowsource of a combo box.

I would rather counteract this problem now. My thinking is to select by last
name and if there is only one with this last name store the ID but if there
is more than one record give me a choice of first names....is this at all
possible?

The above is simpler and lets the user see and select the firstname.

The other problem I have is when I run my report it displays the ID's not
the name so the report is useless without being able to access the database
to check the ID's.

Do not base the report on the Table. Instead base it on a Query joining your
table to the "people" table by ID. Pull the person's name from the people
table, and the other data from your main table.

You're using a relational database, not a spreadsheet! You DO NOT need to (nor
should you) put everything in one table to display it; use Queries to combine
data from all of the relevant tables, and base your report *on that query*.

Gah I feel like this is a bit to hard, but I know it will be better in the
end.

I just want it to be basic to use, so that it will be utilised!!

To a certain extent, the more work you do up front in the design of the
database, the less work your users will need to do in using it. And you'll
only be building the database once, and your users will hopefully use it for a
long time.

That's what I'm hoping!! They use a hideously coloured spreadsheet at the
moment!!
--

John W. Vinson [MVP]