I recently posted a querry problem that is traced back to my Lookup
Field use in tables. Well if they are so evil, perhaps the good people
here can explain how I can work around the following problem:
Well, they actually are not that bad, but what happens is when you run
into a problem such as yours, the solution would have been to
do this correctly in the first place.....
and, not only does the lookup problem bite you in your case, but
if you build a report, it will display the lastname, but when you
sort, or group the report based on that field...you sort by the
actual id...and not the lookup value. This is rather confusing,
and often not what you want.
So, lets solve the first problem (of building a form) to work
without the lookup table.
If you bulled a form, then you will find that for your 3 fields
Supervisor, CoWorker1, and Coworker2.
the id will display. the solution here is to simply use the wizard
to build 3 combo boxes, and thus you now have a form that
functions just like before. The combo box will display the
text, but store the number. this is good!!!
By using the combo boxes, then it becomes quite clear to
you as the developer that Id is being stored there, and thus
NO confusing occurs. further, in a minute you learn how
to deal with the search, and this information will also apply
to your report building. the end result of this is that you
will walk away with BETTER sql skills.
So my question is... how do I get around this problem of the lookup
field? And please explain in very basic step by step terms if you can,
for I am a self taught person and need quite a bit of guidance in some
areas....I am so stumped...
The simply solution is to base your form on a query.
Drop in the table. now, drop in the Employee table. Draw a join line from
supervisor to the employee table id
(the direction you draw this line in is important). Further, you MUST make
this a left join.
So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !
So, double click on the join line you just drew and choose the one that says
Include ALL records from "DailyWorkAssign"" and only those records from
"Employees" where the joined fields are equal
this is our left join. So, our main table (in this case) is DailyWorkAssing
(or whatever
you called it). This means that child records do not have to be set/exist
This is left join
now, repeat the above process for the next two look up fields
(by the way, this is not a normalized design..but, lets leave this for
another day).
Note that you have to drop in the Employee table again. So, when I said
repeat the above, I also mean to drop in the employee table again (you can
not join those 3 fields to the same table by drawing lines from all 3 fields
to the ONE table...you MUST drop in the employees table each time..and do a
left join).
The next problem to solve is that when you search for a Employee name, which
of the 3 fields are you looking for?
I would suggest that now that you have this query built, you include the
field names from EACH of the repeated employees table, but give them a
unique name
So, drag the firstname, and last name from the first employee table in the
query designer.
for the filed name, type in a new name
eg:
for lastname, lets type in
LastName1:LASTNAME
And, now drag latsname for the Coworker2 join...lets call it
LastName2:LASTNAME
further, once this query is built, then you can search on each of these
additional fields we added to the forms data source. In fact, you can
actually drag and drop in the above 3 new fields (and place them beside the
combo box if you want). This fields would now be searchable.
Further, you now have a choice, and can search by ID by searching CoWorker1
field, or search by last name by searching in LastName1 field.
If you used lookups, then you have NO choice..and always searched by ID.
Further, the above means that when you view the table, you see the id's, and
not a lookup value (this is more clear), and if you open up that new query
as a query view, then you will see both the id, and the new additional
columns that define your text values. (and, these new values are
searchable..and even editable!!! CAUTION!!!...SINCE IF YOU edit a name...it
will be reflected everywhere... -- I would try this for fun however just to
see how this works).
You will also find the above query MUCH MORE useful in a report, since your
report now will sort correctly by the name if you for example need to sort
by coworker1....with a lookup field, you were hoped in this case...
--------------------------------------------
Here is a re-post of mine on using left joins:
A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.
So, if we have Customers, and Invoices tables, a left join would give us:
CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344
Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).
So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !
A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:
CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344
So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.
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.
