variable data type

[dave k]

I need a table that can record an unknown set of records for testing. For
example, I want the user to record test results. But, until the tests are
created, I won't know the information to be recorded. In one test it may be
months and a required signature of Y/N. In another it may be a list of
invoice numbers, customer names, dates and amounts. Is there a way to create
a field or list of fields with pointers that can record unknown or varying
data types?

I don't think there is a solution to my problem and so I have left tests in
an Excel files outside of the database, but I would really like it all in one
file. Putting the Excel table in Access doesn't work either since reporting
/ printing the results doesn't work very well.

Any ideas?
Thanks in advance

 

[_Bigred]

You might look at making ALL the excel data text (select all the cells,
format cells, set as text).

If a piece of data is always in the same column in the excel file, you can
import it into access.

If you give a better example of your problem, and better details maybe we
can come up with a solution.

_Bigred

 

[dave k]

I have a database that lists controls in an accounting department (Table1).
These controls are tested in another table(Table2). The problem is that each
control will have varying results from the test. For example, in one test
the Accounting Managers signature on the monthly report is proof of the
control thus a month field and a Y/N. In another control invoices are signed
off by department managers. So, a sample of 30 invoices is selected. For
proper documentation the invoice number, venderID, vendor name, date, amount,
and signer initials are needed. I am not sure how to structure Table2 to
handle an unknown result. I can use one-to-many for the different sample
sizes, but the fact that one result can be 30 records with 8 columns and the
next is 2 records with 2 columns!

Beyond only having text fields and having the tester put in a number of
fields required 8 or 2 in the example above, I am stumped. In Excel I link
to the Access table and import the fields describing how to perform the test.
Then, the user format the lower portion of the form and inputs results in
whatever format works. Then, after the test, the tester puts pass or fail in
the Access database results table (table3). But, I would really like to have
all info in the database vs. some in Access and some in Excel.

Thanks!

 

[Duane Hookom]

I'm not sure I understand this completely however, assuming:
- items that need to be controlled.
- An item could be an invoice or report.
- Control could be a signature or date or number

tblItems
==============
itmItmID autonumber primary key
itmDocReferenceNumber
itmOriginationDate
itm .... etc

tblControlTypes
===============
ctyCTyID autonumber primary key
ctyTypeName text like MgrSign, DeptSign, VendorName, InvDate,....
ctyDataType text, date, number, yes/no
ctyLowerLimit lower limit for numbers
ctyUpperLimit upper limit for numbers

tblItemControls
================
itcItCID autonumber primary key
itcItmID long integer link to tblItems.itmItmID
itcCTyID long integer link to tblControlTypes.ctyCTyID
itcValue actual value recorded

 

[dave k]

I will have to think this one over. I never thought about breaking up each
test item further. One thing I don't get is Table "tblControlTypes". Is
this a list of all the possible field types and with a many to many link to
tblItemControls?

Interesting!

 

[Duane Hookom]

tblControlTypes would store values that you might have considered field
names in an un-normalized table structure.

There wouild be multiple records in tblItemControls matching a single record
in tblItemTypes.

 

[dave k]

Thanks for all the help. One last question, and I think this is the answer
to my problem, what is an un-normalized table structure?

Dave

 

[Duane Hookom]

Check the links at
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.