Don't use lookups.
Just create a query with the additional tables dropped in.
So, if you have 5 fields that hold the "id", but need data from 5 other
tables, then just build a query, and drop in the main table.
now, start dropping in the additional tables with the information that you
need. if you have 10 additional tables, then drop those 10 tables into the
query builder.
Note that the additional tables MUST BE A LEFT join (else you will only be
able to drop in one extra table).
I explain left joins here:
---
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) for a query.
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....
Now, the concept of a left join is NOT very important for you combo box
lookups, except for the fact that it don't work unless you use left joins!!
for the word merge, you can use that query in place of the forms reocrdset
if you want. how to do this is explained here:
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html
Note that all of the above informaton stil only means that ONE reocrd can be
merged to ONE docuemnt in word. This means that while you can easliy pull
data from other tables into the word merge, you can ONLY pull ONE row of the
child reocrds. If you need to palce more then one row of data into the word
document, then my word merge code is OF NO USE. There is however some
samples here on how to do this:
There are samples and ideas on how to do this at:
http://homepage.swissonline.ch/cindymeister/MergFram.htm
look on the left side for speical merges. The one you want is - Multiple
items per condition
