One-to-Many Problem is One Too Many Design Problems

[Earl Anderson]

Maybe I can't see the forest because the trees are blocking my view, but
what should be simple is now becoming perplexing.

In tracking the tenants' employee's vehicles parked in our garage, I have
[tblTenants] and [tblVehicles] in a one-to-many relationship. The
tblTenants' PK is a text-number based on their assigned building electronic
access card PIN (PINID) and the table has the usual/appropriate contact info
fields. The tblVehicles' PK is an autonumber (VEHID) with a FK of PINID and
the table contains fields relating to the particulars of the tenant's
employee's vehicle (VehMake, VehModel, VehColor, VehYear, VehState, VehPlate
and VehPermit). Understanding that the employee may have more than one
vehicle that may be driven to work and parked in our garage, a sfmVehicle
may have none, one or two vehicles (so far, none has three) belonging to the
employee listed there within the main form.

The problem arises when I use query data from the combined tables to merge
into a Word document. A section of this document list the tenant employee's
vehicle information in columns for "Vehicle 1" and "Vehicle 2". Since the
construct of the document uses the vehicle's particulars as inserted data
fields, when the employee has one vehicle, the merge document prints the
information perfectly. If the employee has two vehicles, the document
prints the same vehicle info twice (once in each "Vehicle" column) instead
of the two different vehicles' particulars output in each of the columns.

The obvious (and probably wrong and unwieldy) solution is to make distinctly
separated vehicle particular fields in tblVehicles, i.e. Veh1Make, Veh1Year,
Veh2Make, Veh2Year, etc. This would seem to present some FK and PK
problems. Trying not to be too verbose here, I apologize for the length of
this post, but I wanted to describe the situation and process to elicit a
proper response.

Please help-thanks,
Earl Anderson

 

[Paul]

The Word merge document will just print what is in your query, so I would
expect that if the employee has two vehicles you would get two letters where
the employee details are the same, but the vehicle details are different on
each letter.

If you are trying to get the details of both records onto the same letter
you could use one of the following techniques.

Use the 'Next Record If' merge field to move to the details of the second
vehicle for employees with more that one vehicle. You may need to add a
field to your query that is the count of the employee's vehicles so you have
something to test. It's a while since I've used this, but you could get more
help on one of the Word newsgroups if the online help doesn't sort you out.

Instead of using Word for the letters, use an Access report. Use a subreport
so you can list out all vehicles for each each employee.

The second is the better option in that it will allow for any number of
vehicles. With the first, I believe that you will need to 'guess' what the
maximum number of vehicle is ever likely to be and code for that number.

Hope that helps.

Paul

see www.pdtech.co.uk for Access developer resources.

Maybe I can't see the forest because the trees are blocking my view, but
what should be simple is now becoming perplexing.

In tracking the tenants' employee's vehicles parked in our garage, I have
[tblTenants] and [tblVehicles] in a one-to-many relationship. The
tblTenants' PK is a text-number based on their assigned building
electronic access card PIN (PINID) and the table has the usual/appropriate
contact info fields. The tblVehicles' PK is an autonumber (VEHID) with a
FK of PINID and the table contains fields relating to the particulars of
the tenant's employee's vehicle (VehMake, VehModel, VehColor, VehYear,
VehState, VehPlate and VehPermit). Understanding that the employee may
have more than one vehicle that may be driven to work and parked in our
garage, a sfmVehicle may have none, one or two vehicles (so far, none has
three) belonging to the employee listed there within the main form.

The problem arises when I use query data from the combined tables to merge
into a Word document. A section of this document list the tenant
employee's vehicle information in columns for "Vehicle 1" and "Vehicle 2".
Since the construct of the document uses the vehicle's particulars as
inserted data fields, when the employee has one vehicle, the merge
document prints the information perfectly. If the employee has two
vehicles, the document prints the same vehicle info twice (once in each
"Vehicle" column) instead of the two different vehicles' particulars
output in each of the columns.

The obvious (and probably wrong and unwieldy) solution is to make
distinctly separated vehicle particular fields in tblVehicles, i.e.
Veh1Make, Veh1Year, Veh2Make, Veh2Year, etc. This would seem to present
some FK and PK problems. Trying not to be too verbose here, I apologize
for the length of this post, but I wanted to describe the situation and
process to elicit a proper response.

Please help-thanks,
Earl Anderson