looking up lookups and getting actual data not just the # associat

[ArielZusya]

I've been playing with more distributed data in a DB I'm setting up for a
friend. The Database has a number of tables with relationships to other
tables. Here's the basics:

tblMain:
ID_Main (autonumber, key)
Date (date/time)
Case (lookup to tblCase)
Supervisor (lookup to tblSupervisor)
User (lookup to tblUser)
what (lookup to tblWhat)

tblCase:
ID_Case (autonumber, key)
CaseName (text)
CaseIdentifier (text)

tblSupervisor:
ID_Supervisor (autonumber, key)
SupervisorName (text)
SupervisorNumber (Number)

tblUser:
ID_User (Autonumber, key)
UserName (text)
UserNumber (Number)
UserFor (lookup to tblFor)

tblWhat:
ID_What (Autonumber, key)
What (text)

tblFor:
ID_For (Autonumber, key)
For (text)

In terms of the relationships I've got:

tblMain:tblCase 1:1
tblMain:tblWhat 1:1
tblMain:tblSupervisor 1:1
tblMain:tblUser 1:1
tblUser:tblFor 1:1

I've got the relationships all working nicely but I have one small issues
(and I say small because I think I know how to fix this in a form so the user
may never actually see it) but something I thought I'd ask about in case
there was a solution of which I am unaware. As you can see from the
structure above, tblMain looksup to tblUser and stores that reference. I'd
like to make it prettier in the combobox in the field in tblMain under the
User field. Right now I've got the combo displaying UserName, UserNumber,
and UserFor but for the UserFor it shows the tblFor.ID_For value rather than
the actual tblFor.For value. Like I said, I think I know what to do to get
this to be prettier (to display the for rather than the ID linking the for
with user). Is this impossible? Have I chosen the wrong structure? Where
did I go wrong?

 

[Duane Hookom]

I'm not sure what the "for" value is for. It seems you are attempting to
store two values in a single field. The Row Source of a combo box can contain
more than a single table.

I am concerned that you are using lookup fields in tables. If so, consider
reading http://www.mvps.org/access/lookupfields.htm.

Also, are you sure your relationships are 1 ot 1?
tblMain:tblSupervisor 1:1 <- suggests the primary key value of tblSupervisor
can be used only once in tblMain. I would expect this relationship to be 1 to
Many.

 

[ArielZusya]

Oops... my fault... you're absolutely right about the relationships. They
are not 1:1. Chock that up to friday afternoons. No rest for the weary. The
correct relationships should be:

tblCase.ID_Case : tblMain.Case 1:Many
tblFor.ID_For : tblUser.UserFor 1:Many
tblUser.ID_User : tblMain.User 1:Many
tblSupervisor.ID_Supervisor : tblMain.Supervisor 1:Many
tblWhat.ID_What : tblMain.What 1:Many

So... Here's a bit more background on the database. The idea is to have a
table that I can record every date a particular user (their info stored in
tblUser, there can be an unlimited number of users each of whom will have a
unique user ID) does a particular job (the job data stored in tblWhat and
would be either a consult or repair) for a particular type of client (stored
in tblFor... each user only represents one type of client, either walk-in or
telephone) on a particular case (stored in tblCase, there can be an unlimited
number of cases each with a unique case number) and which supervisor oversaw
the job (stored in tblSupervisor which could contain an unlimited number of
supervisors each with their own unique supervisor id). So... If user John
who has the worker ID of XJ623, on 11/15/07, does a consult job for a walk-in
client, Susan, on case number 425AB643, while being supervised by Gregg who
has the supervisor ID of MRT499, I'd like to be able to drop that in tblMain.
Right now it all works fine as set up. My only issue (again, not much of an
issue... just me being anal I suppose) is that when one looks at the combo
box in the table for tblMain.User, one will see John|XJ623|1 rather than
John|XJ623|Consult. So... is there a way to pretty this up so the system
recognizes the tblFor.ID_For being stored in tblUser.UserFor? Perhaps I've
over complicated the db. Perhaps instead of distributing this to a second
level out from the tblMain I should simply set the "for" options right in the
tblUser.UserFor field. One final note... what I describe above is just a
simplified description of a more complex and large dataset... so if you're
looking at this above and thinking, why such a complex structure for such a
simple dataset, take my word that the dataset is much more involved. *GRIN*
Anyhow... Looking forward to your thoughts. Thanks!

ArielZusya

 

[Duane Hookom]

I suggested you could add tblFor to the Row Source property of your combo box
to display the text value rather than the ID.