Use a temporary table - is this a good solution to the problem?

[Steve]

Greetings:

I am creating a database to track test outcomes. The simplified table
structure appears as follows:

Table Structure 1

tblTest tblSubTest
PK idsTestID PK idsSubTestID
dtmTestDate FK intTestID
chrsubTest
intSubTestResult

These tables are joined in a 1:many relationship using the primary key
"idsTestID" and foreign key "intTestID". Each test is associated with one or
more subTest(s) and subTest results. This all works fine until we get to the
data entry. Each test can potentially be associated with numerous subTests
and subTest results. Having the user have to individually choose and fill in
each subTest from a long drop down list of potential subTests and enter the
subtest result is cumbersome at best.

Alternatively, I could "hard wire" the subTests into the Test table as
follows:

Table Structure 2

tblTest
pk idsTestID
dtmTestDate
intSubTest1
intSubTest2
intSubTest3 (and so on)

This format simplifies data entry because I can design a form that has
subTests bound directly to its own datafield. The user can tab quickly down a
series of defined controls and enter the subTest results. But this approach
is problematic because not every subtest is used during each test, some will
be rarely used. To make matters worse each series of subTests can be repeated
under different conditions. This would create the potential for numerous
blank datafields and inefficient storage. The question therefore is how to
keep the original table structure but provide the user with a convenient data
entry platform.

The solution I am proposing is the use of a temporary table that would
resemble table structure 2 above and have a field for each of the subTests.
This would be bound to a data entry form where each subTests would be present
as its own control on the dataentry form. When the user is done some sort of
"finalize" button would be clicked and each of the data fields in the
temporary table would be saved as a separate record in tblSubTest using the
desired table structure structure 1 shown above and the tempTable would be
cleared.

Does this seem like a good solution to the problem? Does anyone have a
different solution? Does anyone have an example of the coding used to
accomplish what is being proposed (i.e., moving the data from the temp table
into tblSubTest)?

Thanks for any and all help.

 

[Stefan Hoffmann]

hi Steve,

Does this seem like a good solution to the problem? Does anyone have a
different solution? Does anyone have an example of the coding used to
accomplish what is being proposed (i.e., moving the data from the temp table
into tblSubTest)?

Basically, yes, as it doesn't conflict with any kind of calculation or
workflow based on that data, e.g. a wrong record count.

The only problem I see, is that you have to create that table
dynamically, which is easy to do, but may bloat your front-end.

I would consider using a second table in the back-end, same structure as
tblSubTest* for data entry only. Fill this table before editing. After
editing copy only the entered values and delete it. This also ensures
referential integrity.

*)
Add a field to identify the editing user.
Maybe you need a field to track the entered state, e.g. a Date/Time
field, if it is <> NULL then the user has entered a value. This depends
whether NULL is a valid input as a result.


mfG
--> stefan <--

 

[Jeff Boyce]

Steve

Won't you have to modify that temp table and your routines if there is ever
a change in the number of subTests? That sounds like a maintenance
nightmare.

Another potential solution might be to use Excel to gather the data (in that
"flattened" structure), then import the data into Access and use queries to
parse it into a more-normalized (and permanent) table structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tina]

assuming that each test has x different potential subtests associated with
it - even though every one of those subtests may not be performed in every
test instance - then it sounds nearly identical to a test/subtest solution
that i use in a working database every day at my job. it doesn't require
temp tables, or spreadsheets - just an extra table to list the subtests that
apply to each defined test, a form/subform setup, and an Append query that
runs programmatically.

if you're interested, i can throw together a simple demo database for you
and email it to you. but don't post a clear email address in the ngs or
you'll get spammed to death. disguise your email address, and explain how to
decode it. for example

tinaTAKaEOtUTmyemailTHEdoCAPSTcom

take out the caps, and replace at and dot with their symbols. (and no, that
isn't my real email address, of course.)

hth

 

[Tony Toews [MVP]]

Alternatively, I could "hard wire" the subTests into the Test table as
follows:

Table Structure 2

tblTest
pk idsTestID
dtmTestDate
intSubTest1
intSubTest2
intSubTest3 (and so on)

I would never, ever use the above hard wired fields.

The solution I am proposing is the use of a temporary table that would
resemble table structure 2 above and have a field for each of the subTests.

I also would not use this approach as every time the users add a
subTest you will need to make a change to the forms.

I would suggest creating a subTests "Template" or "Master" table with
all the predefined subTests per Test in it.

Then use a temp table where you create an empty record for each of the
subtests and present that to the user on a continuous subform where
the main form has the test data. When they close the form you would
append all records where they entered any data to the main table.
Then clear all the records from the table.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/