Bob-
That depends on the actual layout of your table. I'm having a hard time visualizing why you have separate Spots and Spot Numbers tables.
I'm going to assume the following:
A Location can have multiple Industries and multiple Spots.
An Industry can be at multiple Locations.
A Spot is assigned to only one Location and Industry. (Multiple Industries don't share the same Spot.)
With those assumptions, your tables should look something like:
Locations:
LocationID, LocationDescription, etc.
Industries:
IndustryID, IndustryDescription, etc.
Spots:
Spot Number, LocationID, IndustryID, SpotDescription (Dock, Spout, etc.), etc.
This sets up a many-to-many relationship between Locations and Industries via Spots. Each Spot belongs to one and only one Location and one and only one Industry.
There are two ways to look at and edit this data:
1) Via Locations
2) Via Industries
Looking at a form to edit Locations and their Spots, you would create an outer form to edit the data from the Locations table. In a subform, use a query that links Industries and Spots, being sure to include all the fields from Spots and any descriptive field that you want to display from Industries, but not the IndustryID. Use a Combo Box control to edit the IndustryID, and use a Row Source that returns the IndustryID and description from the Industries table. Make sure that the Link Master Fields and Link Child Fields properties of the embedded subform are set to LocationID. When you use this form, you should see Locations presented one at a time in the outer form. In the subform, you will see all the assigned Spots along with the Industry assigned to that Spot.
Does that make sense?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Aug 24, 2014, at 5:25 AM, bburke@swiftaz.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
In my switchlist program (for a model railroad) I have several tables including Locations, Industries, Spots, and Spot Numbers. A Location could have one or more Industries; an Industry could have one or more Spots; each Spot Number has a unique Spot. I'm not sure when clicking on a specific Location how to bring up the associated Industries and Spot Numbers for that Industry only. For example, the Location Crawford has three industries. Each of those Industries has at least one spot number and spot. Example: Crawford Grain Elevator has a spot number 202001 – the spot is Dock; in addition that industry has a spot number 202002 – the spot is Spout. Any help on this is appreciated.
Thanks,
Bob Burke
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar