Am I on the right track?

[froggiebeckie]

I need a reality check, before I move any further.

I've created a db (in A2007) to track test results on specimens.

Everything starts with a roll, which has specific attributes.
From the roll, samples are taken.
From the samples, specimens are taken.
A variety of tests are then done on the specimens.
Not all tests are done on all specimens.
Data needs to be traceable back to the rolls, so relationships are critical.

I have several tables.
Rolls (Roll # and attributes)
Samples (Roll ID, Sample # and Sample attributes)
Test A Results (Sample ID, Specimen # and readings)
Test B Results (Sample ID, Specimen # and readings)
Test C Results (Sample ID, Specimen # and readings)

etc.

I can query and write reports to show all this info based on Rolls.

Problem is, the folks who asked for the db are more comfortable with Excel and now want an easy way to dump all data into Excel, so they can manipulate the data.

SORRY TO BE SO LONGWINDED, BUT, FINALLY, HERE'S THE QUESTION:
What's going to be the easiest way to do a data dump for them, without losing the relationships between the tables?

I'm thinking about working with queries, till I have all the data in one place. Then I can automate the export of the final query.

Does this make sense?
Is there an easier way I'm overlooking?

Thanks for taking the time to read through all this, and I'll appreciate any advise or insight offered.

BeckieO

 

[plog]

From what you've described you only need 4 tables: Rolls, Samples, Specimens and Tests. You missed a table between tests and samples (Specimens) and then you needlessly created a table for every test (Test A, Test B...).

You don't need multiple test tables, instead of storing the type of test in the table name, you create a field for it and store that value there:

Tests (TestType, Specimen_ID, Readings)

However, this is all moot if...

Problem is, the folks who asked for the db are more comfortable with Excel and now want an easy way to dump all data into Excel, so they can manipulate the data

It's fine to spit out data into Excel for reporting purposes, not for manipulation purposes. How do you propose their changes get back into the database? A database is definitely the right choice for this project, but if you are just going to interact with the data through Excel, then there's no point in using anything but Excel.

 

[froggiebeckie]

Thanks for the feedback.
The reason I went with specific test tables, (instead of one combined table) is to simplify adding test data to the db.
The program the testing facility uses, exports an Excel sheet with results grouped by Test.
Each test (A, B, C, etc) has multiple outputs, some standard throughout all tests,(like weight, length) some unique to a particular test.
By creating different Test tables, I can open a split form and do a quick copy and paste, into the appropriate table.
Remember, I'm trying to make it user friendly, because I really dont want to still be dumping data for these folks 20 years from now.

I've supplied several canned reports, but I think they are hoping to be able to use Excel to sort out something like:
How many Specimens from Roll X, have YYY readings of >.666.

The only other thing I can come up with to keep them in Access, is to maybe create a query that will let them select what data they want to see.

So, if they wanted to know how the ]Molding Temp effected the Thread Count for Specimens made from blue rolls, they'd maybe select:



Roll #
Color
Sample #
Molding Temp
Specimen #
Thread Count


Anyway, thanks again for your input. I''m not sure where this is going to go.