relationships between groups of tables

[tjrobert82]

Forgive me if this is a really dumb question, but there is an issue that has puzzled me for a while now. I work at a company which runs many tests using identical formats. The files are all saved as individual excel files and when I import them, the format for each test is a default primary key and two fields containing anywhere from 50-200 records. I have made a table that allows a user to input information about each test, such as who performed it and when. Suppose I have 6 tables - Test1, Test2, Test3, Test4, Test5 and OverallRecords. Each test will therefore have its own raw data table and a single record in OverallRecords. How can I relate these tables so that Test1 can be linked to its corresponding record in OverallRecords? I eventually want to make a form which can cycle through OverallRecords (five total) and have a subform which shows the raw data table (and anything else I want to show along with it, like graphs or Min/Max/Avg queries). If I can avoid having to micromanage each test as it is imported, it would make my life a lot easier. I've already spent more time than I'd like to admit trying to solve this one and if anyone has the solution, I'd really appreciate a reply. Thanks.

 

[The_Doc_Man]

You relate groups of tables to a master table the same way as you would relate a single table to a master table.

The tables have to each have a field in common that is unique in the master table and doesn't have to be unique in the other tables. Then you can have a many-to-one mapping with the master table as the one-side of that relationship.

Or are you asking for more than that?

 

[tjrobert82]

I think I get it

OK, so I would create another field within each of my tests that would uniquely identify it in the master table

eg. Test1
ID(p.k.) Label(unique ID) Field1 Field2
1.............1........................#.........#
2.............1........................#.........#
3.............1........................#.........#
4.............1........................#.........#
5.............1........................#.........#
6.............1........................#.........#

And I could relate the Label field to a field in the master table?

This would work, but is there a way to increment the Label ID for each new test automatically? For instance, is there a way to autonumber in the master table and have the Label field fill in for each test? If I open my "Data Input" form using the master table and input all of the test information like location and date, can one of the fields autonumber and link to the newly imported data and fill it all in for the Label field in Test1? That might be a stretch but maybe it's possible. Thanks again for the help.

 

[The_Doc_Man]

"Ah, I see" said the blind man....

OK, look at this structure and see if THIS is what you might REALLY have wanted.

tblTestMaster
fldTNum, autonumber, prime key
fldWhen, date...
etc.

tblIndivTest
fldINum, number (possibly autonumber but maybe not)
fldTNum, number (foreign key to TestMaster)
(if fldINum is not autonumber then the combo of INum and TNum is your prime key and this combination must be unique)

relationship is between fldTNum in tblTestMaster (one) to tblIndivTest (many)

But I'll take it farther. Why do you have Test1, Test2, Test3,...? (rhetorical) Because something about them is a little different, yet they are somehow related. so let's add one more field to tblIndivTest...

tblIndivTest
fldINum (as above)
fldTNum (as above)
fldONum, number, takes the place of the suffixed numbers 1, 2, 3,... (on Test1, Test2, Test3, etc.) and now you have only two tables.

If these suffixes have special meaning (like Test1 is a specific subject or a specifc time within a training period), you can have one more table to hold the description.

tblTestSpecialInfo
fldONum, number (possibly unique)
description of this particular test and its properties that differ from other tests - i.e. enumeration of its uniqueness.

With at most three tables, two of which are just LOOKUP or date-selector or something like that, you have all the info completely covered in at most two relationships (three if there is also a relationship between the master and the special-info table).

Did that make sense in your context?

 

[tjrobert82]

Thanks!

Well, this is definitely going to work. The consolidation of all of the tests into one table was a great idea, too. My last question, and then I promise I'll stop bugging you, is how can I make the 50 or so records in the DataTable (many) pick up the value of the field in the MasterTable (one) automatically? Can you think of some kind of conditional statement that I could enter into the DataTable test ID field that would make it grab the open test ID in MasterTable? Otherwise, I think I'd have to go in after I imported the spreadsheet and type in "1....1....1...1....1....1...1...1...etc." Or not necessarily a conditional statement, just something that would get the job done without me punching it in manually. Thanks again.

 

[The_Doc_Man]

If you are entering this data through a form, then make the form reflect the structure of the tables in this way.

The form opens the parent table, picks the stuff about a single test. When you have selected whatever you need from the form, open a SUBFORM that holds the lower level, or child, table's records.

In that parent/child form setup, there is a property of the sub-form control that names the field linking the parent to the child. If you name the primary key of the parent as this linking field (and name the corresponding field for that number in the child), then Access forms automagically fill in the linking number. The easiest way to get this right is to build the child form first. When you build the parent form, be sure that the control wizards are turned on. When you build the sub-form control on the parent form, the wizard will ASK you which fields are to be linked. Tell the wizard the right answer and it will be built for you exactly as you need it to be.

If any other records in the parent exactly match the spelling in a child field, that information is also transferred automagically, if I remember correctly how that works. But if not, you can make an event routine for the FormLoad of the sub-form that reaches to its parent form to find the fields it needs.

 

[tjrobert82]

If I understand your post correctly, you were explaining how to create a subform that shows all of the data for the appropriate test. This wasn't really my concern, although it is something I had been doing. My issue was with importing the data into a single table. When I import new data and append it to the end of the giant data table, I want the default value to reflect the Test ID in the Master Table so that all of the new data automatically gets the appropriate Test ID. It appears to me that you can't change the default value to anything conditional in a table, such as "get the value that is currently displayed in Forms!blahblahblah.blah." So, instead I decided that maybe I could prompt the user to tell me what the Test ID should be for the new imported data and take that value and make it the default value for my Test ID field in the large data table, letting the records just naturally pick it up as they are imported. This doesn't seem to want to work either - probably because of my utter incompitence at VBA programming.

What I did was make a button using the button wizard and edited the VBA so that it looks like this:

Private Sub UpdateButton_Click()
On Error GoTo Err_UpdateButton_Click

Dim InputNumber As String
InputNumber = InputBox("Please enter the current Test I.D. number:", "Update Data Table Values")
DataTable![Test ID].DefaultValue = InputNumber

Exit_UpdateButton_Click:
Exit Sub

Err_UpdateButton_Click:
MsgBox Err.Description
Resume Exit_UpdateButton_Click

End Sub


What it tells me when I do this is that it doesn't like DataTable as an object. That means I have to reference it, but isn't creating the table in the first place enough? I don't know. Is this a stupid idea? It seemed pretty good in my head, but if it's not really feasible, let me know so I can stop wasting my time. Thanks again.