Relationships between tables and querys

[Jack Leach]

Hello all, thanks in advance for any insight.


I'm trying to figure out the best way to utilize relationships with tables
and queries.

I have a table called tblItems, which is a complete inventory listing of all
items and an ItemClass (mfg goods, fasteners, consumables, etc). this table
has a one to one relationship to other tables that hold the detail
information for each type (tblIMGs, tblIFSs, tblICSs to hold specific info
for each type). Here's the One to One relationship for Items and
Manfuactured Goods...

tblItems ----------- tblIMGs
fldCode (PK) fldCode (PK)
fldType fldRevLevel
fldDesc fldCageCode
(etc)


Now, the mfg goods table (tblIMGs) has relation one to many tables for
Documents, BOMs, Routing, etc. These relationships are set up on
tblIMGs.fldCode as the parent and the appropriate in the child table.
Straightforward enough so far.

So next comes my query (qryParts) which pulls the required fields from
tblItems and tblIMGs:

SELECT
tblItems.fldCode,
tblItems.fldType,
tblItems.fldStatus,
tblIMGs.fldCompany,
tblIMGs.fldRevLevel,
tblItems.fldDescription (ect)
FROM
tblItems
INNER JOIN
tblIMGs
ON
tblItems.fldCode = tblIMGs.fldCode
WHERE tblItems.fldType = 0

I'm not positive this query is correct, but I think it is. If I try to pull
tblIMGs.fldCode as the source for the part number, then tblItems.fldCode
isn't populated on a new record. But this way, pulling tblItems.fldCode as
the source automatically populates tblIMGs.fldCode on a new record. Which
leads me to believe that I have the correct query setup.

So this qryParts as shown above is the recordsource for my form frmParts.
Works fine. But now, I want to include subforms for Documents, Routings and
BOM Items. My area of question is this: in the table design (in the
backend), these child tables are linked by tblIMGs.fldCode (as seems
appropriate from a table design perspective), but in my qryParts, I don't
reference tblIMGs.fldCode, rather I reference tblItems.fldCode (which seems
appropriate from a query design perspective). So when creating a subform,
what parent/child fields do I link?

Here's what I think I can do...


1) Link qryParts.fldCode to tblIMGDocs.fldCode in the subform

My issue with this is that qryParts.fldCode is not directly related to
tblIMGDocs.fldCode, so therefore it doesn't seem correct to me


2) Create a query qryPartDocuments from tblIMGDocs so that I can then create
a one to many relationship between qryParts and qryPartDocuments (these
relationships would be created in the frontend rather than the back,
obviously).

My issue with this is that I would now have to create a few otherwise
needless queries. Not a big deal, but I've always considered that if I have
to create extra objects for something like this, there must a better way.



3) In the FE, set up a one to many relationship between qryParts and
tblIMGDocs

My issue with this is that I would have to remove the backend
relationships... and that just sounds like a very bad idea to me



4) Go to the backend and relate tblItems.fldCode as the one side of the
Documents, Routings, BOM list, instead of using tblIMGs.fldCode as the parent.

I don't have any particular issues with this approach, but from a table
design perspective relating tblIMGs seems to make more sense than relating
tblItems.



Sorry for the long post! Hopefully I've been able to explain well enough
without going overboard. Not sure which approach to take... this is a rather
new dilemma for me and I seem to be having some trouble thinking through it.
Any "standard practice" ideas for working with these types of relationships??
Any help would be greatly appreciated!

Thanks,


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

nevermind.... I got it. thanks anyway
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)