SharonInGa said:
*** still stuck ****
Many truck make many deliveries to many destinations. Current relationship
is set to: Inbound Truck table (key:TruckID)
Notice that in your example below you list the key as [Truck ID], with a
space. Space is OK (since you can surround the field name with
brackets), but your spelling needs to be consistent. Spelling of a
foreign key does NOT need to match that of the primary key, but I think
it's a good idea anyway, to help you keep track. Also, in Query Design
View, Access will automagically link Tables that have key fields with
matching key names, saving you a bit of trouble.
to JOIN table (keys: Truck ID
and PO# for each box) to Product table (Key: PO#). the destination field
resides in the Product table since I have to assign a destination for each
box. I need to see all the box POs from each Inbound truck. This works.
I give an example of that below, but since you say that you have a
working version, maybe you don't need my example.
Since you did not say much about [Product] besides that it contains a
[Product].[destination] field, I can't give much advice on that.
I also need a main form for each destination that lists all associated PO's
in a subform.
Before you set up a subform, I suggest you get your Tables and Queries
to work properly, then create your Forms and subforms based on your
(tested and working) Queries.
Inbound Truck table JOIN table Load Table
key: Truck ID Truck ID PO#
PO#
List all boxes that arrive in each truck
Assign a PO# and destination for each box
List all boxes associated with each destination
Cosmetic suggestion: You might want to rename your [JOIN] Table to
something more suggestive, like [Trucks_Loads]. With your current
naming convention, what would you call your next Table that you'd use to
support a many-to-many JOIN? Maybe [JOIN1]? I think you want names
that remind you of what you want your objects (Tables, Forms, &c.) to do
for you.
Also, it appears that you are using [PO#] as the primary key in [Load].
I would rename either the Table or the field so that the names would
be more similar, such as calling the Table [PO Data]. You might look at
the "Northwind Traders" sample database for ideas of how to organize
these kinds of information.
Here's my example for your first Query (using my versions of your names):
[Truck] Table Datasheet View:
Truck ID
--------
Truck 1
Truck 3
Truck 7
[Trucks_POs] Table Datasheet View:
Truck ID PO#
-------- ------
Truck 1 113-58
Truck 1 114-72
Truck 7 114-72
Truck 7 114-91
[PO Data] Table Datasheet View:
PO#
------
113-58
114-72
114-91
115-23
[Q_BoxesPerTruck] SQL:
SELECT Truck.[Truck ID],
IIf(IsNull([PO Data]![PO#]),"<nothing at this time>",
[PO Data]![PO#]) AS Boxes
FROM Truck
LEFT JOIN ([PO Data]
RIGHT JOIN Trucks_POs
ON [PO Data].[PO#] = Trucks_POs.[PO#])
ON Truck.[Truck ID] = Trucks_POs.[Truck ID]
ORDER BY Truck.[Truck ID],
IIf(IsNull([PO Data]![PO#]),"<nothing at this time>",
[PO Data]![PO#]);
[Q_BoxesPerTruck] Query Datasheet View:
Truck ID Boxes
-------- -----------------------
Truck 1 113-58
Truck 1 114-72
Truck 3 <nothing at this time>
Truck 7 114-72
Truck 7 114-91
.... and notice that PO# 115-23 does not appear on this list, as it was
not included in any shipment, according to the information in
[Trucks_POs].
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
