Access 2003 - Database Question

J

Janine

Table with Client ID and Client Name
Tabe with Client Matter Info

I want best way to show CLIENT name in full when using ID in the Client Info
Table

Are lookups - satisfactory - they allow you to select client name and show
in full?

Is there a better way?
 
D

Dirk Goldgar

Janine said:
Table with Client ID and Client Name
Tabe with Client Matter Info

I want best way to show CLIENT name in full when using ID in the
Client Info Table

Are lookups - satisfactory - they allow you to select client name and
show in full?

Is there a better way?
Click to expand...

A lookup field in the table itself can be handy if you work directly in
your tables, but it can be misleading because what you see -- the client
name -- isn't really what's stored in the table -- the client ID. It's
better, IMO, to do your work in forms that are bound to the tables,
rather than in the tables themselves. In that case you can use a combo
box on the form with its rowsource set to the Client Info table. The
combo box would be bound to the ID field, but would show the client's
name, just the way a lookup field in the table would. However, when you
look in the table you'll see the actual ID that is stored in the table.
 
J

Janine

Thank you Dirk
The main problem is yes the form (even though ID) does show client full name
but the client (sole practitioner) works in the tables as he created this
database himself through a course. He adds data directly to the tables (not
a pretty form) and no he hasn't go to add delete clear buttons yet so I do
not want to go there.

I just put a column in a table he uses for client info (drawing from his
client table) and made it a lookup to show the client name in full because as
you can image the client id 1 2 or 3 etc means not much when you are in the
table itself?

I am taking the simple way out. Meaning I do not think I want to delve into
a database that has trust moneys attached to the client matter as well if you
get my gist.

I want to just fix it easily but not unacceptably.

I have suggested he create a separate table per client per matter for his
merge documents. He can then keep his database the way he created it. Also
it is economical (from my perspective).

Thank you for the advice.
 
J

John Vinson

Is there a better way?
Click to expand...

Yes.

Use a Form based on Clients (showing any or all Clients fields) with a
Subform based on the related table.

Table datasheets are of VERY limited utility and capability, and
should not be used for routine interaction with data.

John W. Vinson[MVP]
 
J

John Vinson

Also it is economical (from my perspective).
Click to expand...

Ummm...

Persuading a client to use a bad design, or to use a good tool
incorrectly, is NOT economical in the long run. Financial suicide is
perhaps a closer approximation.

John W. Vinson[MVP]
 
D

Dirk Goldgar

Janine said:
Thank you Dirk
The main problem is yes the form (even though ID) does show client
full name but the client (sole practitioner) works in the tables as
he created this database himself through a course. He adds data
directly to the tables (not a pretty form) and no he hasn't go to add
delete clear buttons yet so I do not want to go there.

I just put a column in a table he uses for client info (drawing from
his client table) and made it a lookup to show the client name in
full because as you can image the client id 1 2 or 3 etc means not
much when you are in the table itself?

I am taking the simple way out. Meaning I do not think I want to
delve into a database that has trust moneys attached to the client
matter as well if you get my gist.

I want to just fix it easily but not unacceptably.
Click to expand...

I understand the considerations you're forced to take into account. If
the client insists on working in the tables, and needs to see the client
name, then a lookup field is the best solution I can offer. However,
anyone who builds queries on that table is going to have to take the
lookup field into account.
I have suggested he create a separate table per client per matter for
his merge documents. He can then keep his database the way he
created it. Also it is economical (from my perspective).
Click to expand...

I'm not sure I follow you here. I can't think of many circumstances in
which I'd advocate having multiple tables to hold the same type of data,
such as a separate table for each client. But you're obviously working
under strong client constraints, and the client, if not always right, is
always the client.
 
Top