database structure - problem deciding primary key

[smftraining]

I am at a roadblock trying to figure this out, maybe I have been looking at it too long. Here are the basic tables I have :
Item; ItemID, Name, Description (all possible items that may be used at locations)
Location; LocationID, StoreName, TempleteNum?? (only certain items need to be checked for each location)
Templete; TempleteID, ItemID, LocationID,TempleteNum (this is the problem...)
Inspection; InspectionID, DetailsID, Date (similar to an invoice or order )
InspectionDetails; InspectionDetailsID, ItemID, CurrentTemp (Like the details of an order form. CurrentTemp is a number I enter kindof like the quantity field in an order)

Form Goal:

Inspection ID
Date



Item Number (but only those items for the specific location as recorded in template, Item Description, currentTemp (for each item at location)
I just don't think I am thinking about the template idea correctly. Another way to describe what I am doing is like if there are 10 kinds of fruit and you have 5 produce stands. Some stands only carry certain fruits. Daily you need to take the temperature of each fruit, then report on that.

Hope someone can help

[June7]

I do not understand the issue. What is a Templete (did you mean Template)? What is this db for? Why don't you use autonumber field for primary key? What are your data entities and relationships?

Maybe this will help http://www.rogersaccesslibrary.com/

Should not use special characters in field names, like those ?? marks.

[smftraining]

I didn't actually name the field with ?? I was trying to say that I am not sure that is the correct way.

Let me trying explaining better. I do inspections; there are items I look for. There are about 200 items I look for at each location. Some locations may use all items some may not. So I need a template (my name) for what items are being checked at each location. Problem is with my current layout the Id field in template doesn't relate to anything. I think I am looking at this wrong. I even considered in item adding a field for each location and using an on/off. But I know that is not the right way to do it. I shouldn't have to add a new field everytime I get a new location. Hope that makes more sense. Oh and ID's are all primary Key

[June7]

So you have standardized inspections and you want to set up a table (Template) that associates inspection location/category/type with the elements of inspection. Then you need a table (Inspection and InspectionDetails) that documents the actual inspection event. I presume you periodically visit the same locations.

So before you head out on an inspection trip, pre-create a set of detail (response/evaluation/comment) records for that inspection. Run an INSERT SELECT query to create the records using the Template table as a source. Then during inspection open a form filtered for the inspection event and a subform lists the pre-established detail records for the inspection elements. Just fill in response/comment for each detail item.

Otherwise, creating the InspectionDetail records would be a data entry process of manually selecting each inspection item and creating records during the inspection (or from a paper check off and comment form).

Inspections table needs a FK field for LocationID. DetailsID does not go in Inspections table - InspectionDetails needs a FK field for InspectionID.

I do not really see a need for saving a Template primary key ID as a foreign key anywhere.

This is similar to a questionnaire/survey type of database. A common topic. Search forum.

[smftraining]

here is a screen shot of my relationships. I must have something wrong because when I try to do a query that includes the templete table I get the recordset is not updateable.

[June7]

Why would you include Templete table in query? Only purpose of Templete table is to assist in pre-establishing records in InspectionDetails.

[smftraining]

Okay, this is where I am at a road block. I have many items for and several locations I inspect. I set up templete table thinking I needed that to know which items I need to inspect for at a specific location. EG Location1 I need to check for Item 1,3,7,150 and record a value (temperature) for each item... Location 2 I check 1,3,9,149...

When I create the form I need to display the list of only items for that location and update the temperature for that item.

I was using templete to identify which items go with which location...then perform query including inspectiondetails fields and item fields...That would be used in the form and be updated with the value. But my theory is not working here...

I actually know Mysql and PHP but for some reason the transition to access is not going easy. I could have this done in a webbased program but I don't always have internet where I go.

[June7]

The form should be a form/subform arrangement displaying records from Inspection and InspectionDetails. Template table can be used to establish set of records in InspectionDetails (this will require an INSERT SELECT sql action and maybe some VBA code) but it is not useful when you want to actually do data entry for the inspection event. Unless you want to use it as a source for a combobox to manually select items one at a time for InspectionDetails records which means there is not a pre-created set of records for that inspection event. Cascading comboboxes might be useful for the manual approach http://datapigtechnologies.com/flash...combobox2.html

[smftraining]

I do need to update the inspection details with the value. I do not want to use a combo box as there are over 200 items for each inspection. I may have to ditch idea and just to an excel spreadsheet for each location...I know this should be able to be done but...

[June7]

As I said, Templete table purpose is to pre-establish a set of records in InspectionDetails for an upcoming inspection event. This requires selecting some parameters and running an INSERT SELECT sql action. First have to create the parent record in Inspection table. Do this manually through bound form. Then on that form can have a button "Create Inspection Detail Records" with code like:

CurrentDb.Execute "INSERT INTO InspectionDetails (InspectionID, ItemID) SELECT " & Me.ID & " AS InspectionID, ItemID FROM Templete WHERE Location=" & Me.LocationID

Now when you go do the inspection open the form/subform filtered to the established Inspection record and enter comments into the established InspectionDetails records.