The big problem with using the lookup feature at the table level is that
you, or your software never knows when it is supposed to show the ID, or the
actual text of the look up. The same problem occurs when you use sorting and
grouping in a report. Do you want to sort on the actual ID, or the text.
Worse, sorting and grouping will group and sort by the ID, but display the
text. Further, when you build a query on this table, do you type in the
text, or the id for the lookup field? ie:
select * from tblCustomer where LookUpfield = 123
or
select * from tblCustomer where LookUpfield = "some text from other table"
Thus, using lookup is source of UTTER confusing. Take note, and read the #2
in the following list:
http://www.mvps.org/access/tencommandments.htm
Now, do take note that you will have TONS and TONS of lookup values in other
tables as you explain. So, the ONLY horrible thing we are talking about here
is using the built in lookup feature at the table level, as this feature
causes much confusing, and gives you little control since you can't decide
when you want the actual id, or you want the actual text. Further, often (as
in your case) you will have to go and write a query to pull in the text
values anyway. Learn to use a query, as that skill will work with virtually
any database.
Thus, since this lookup feature gives so much confusing, virtually all of
the developers here avoided this feature. The workaround is that when you
build a form, you use the combo box wizard (it is fast...and only a minor
extra step).
Anyway, to solve your problem, you need to build a query. When you build
query, you simply throw in those extra lookup tables into the query (if you
have 5 fields that needs other values from 5 other tables..then you throw in
those 5 tables). Now, draw the join lines from the field(s) to the approable
tables. You can now freely drop in the "text" field from the table(s) that
you need. Now, for your graph, or export to ms-word, or for reports you can
use the text names as if they were regular fields (and the id will not
show). And, in the case where you want/need both the id, and text to
display..you can do that also.
Do note that if you have to drop in more then one table used for lookup
values into the query builder, you have to make them left joins.
To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button
You get three options:
Only include rows where the joined fields from both tables are equal
(this standard default inner join)
Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal
(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join
Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....
For forms, and sub-forms, and related tables, left joins are quite
important.
If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.
http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html
tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!
The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).
So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.
