Results 1 to 5 of 5
  1. #1
    wsneeden is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2014
    Posts
    4

    Question on properly linking tables

    I am looking for some advice on how to set up my tables for a database that will track inspections on items my company is producing. A currently have three tables for this. The first one 'JobDescription' has the job number, customer name, etc. The second, 'ItemDescrition' has the item number, revision, etc. The third, 'InspectionDetail' has the type of inspections, inspector, date performed, etc.



    My goal is to be able to set up a form where you can pick the job from a list of combo box that would then filter for any items on that job. You could then pick the item you wanted and the form would show you all of the inspections done for that item and allow additional inspections to be made.

    Any help anyone can offer would be greatly appreciated.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I would start at the query builder. Create a query to include all of the fields you need. If you add a table/join to your query that breaks the query, you will need a second query. You could probably associate this second query to a subform in a main form, using a Master/Child link between the two forms/queries.

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    You may need to change your database structure a bit, but a few questions first:

    Is any particular item related to only one job? Can there be more than one of any particular item, either at the same job or at different ones? If so, how do you distinguish between them?

    Does the InspectionDetail table contain a field to indicate which item was inspected? If not, it should.

    Can you provide the table structures in a bit more detail, please?

    John

  4. #4
    wsneeden is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2014
    Posts
    4
    John,
    Items are not always related to just one job and we may have multiples on the same job. I am distinguishing between them in my 'ItemDescription' table by having a 'Unit' field.

    I've attached a screenshot showing my table relationships. The 'JobDescription', 'ItemDescription' & 'InspectionDetail' are the main tables. The others are just for lookups.

    Click image for larger version. 

Name:	DBRelationships.jpg 
Views:	18 
Size:	50.5 KB 
ID:	16018

  5. #5
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The screen shot was a big help.

    What you are missing is a table or field linking Inspections to Items. Notice that your InspectionDetail table has no field in it to indicate what item was inspected - a rather fundamental bit of information, I would think.

    From your diagram, I think an inspection would relate to an ItemDescription, using DrawingID (that will give you the Unit) - so if you include DrawingID in the InspectionDetail table, then everything is related.

    One Job has one or more Items which have one or more Inspections
    (conceivably an item might have no inspections - but that's a minor point)

    What data field(s) uniquely identify a unit? Is it Part# + Unit?
    Is the description the same for each distinct Part#? If so, then your data is not properly normalized - Part# + description should be in a separate table (it would be a lookup).

    HTH

    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-26-2013, 08:14 AM
  2. splitting and linking question
    By TravisB in forum Access
    Replies: 1
    Last Post: 12-05-2012, 01:35 PM
  3. Linking Question
    By Petersona01 in forum Access
    Replies: 2
    Last Post: 08-02-2011, 09:02 AM
  4. Replies: 0
    Last Post: 03-04-2011, 10:28 AM
  5. simple linking question I hope
    By wing8lc in forum Queries
    Replies: 4
    Last Post: 01-21-2010, 03:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums