Add existing entries to another entry

[Fairlane]

I have a database that tracks our assets (serialized). I can successfully add groups of items using subforms and this is how the serials are added. But now I want to test a number of these parts.



1. I want to have a table of assets and a table of tests.
2. the test table should have a test number(text), test type(lookup list), test document(Attatchment), and list of assets tested (lookup from table w\multiple values).

What I want to do is create a form that will allow me to associate many assets with a test so that I may querry an asset and see the tests that have been done to it. I have been unable to figure out how to procede. I would want the tester to create a test and add the parts to it. But the challenge is that he will have to sift through 10,000s of serials to find the 20 he is testing. (the tests are batch process, normally a pressure test). It would be easier if he could type or even sort the assets (the assets have an order number entry). I can add a sample of my database if need be.

[June7]

What is the relationship of these data entities?

Can each asset have more than one test?

Can each test associate with more than one asset?

Will the same test be performed repeatedly on a cycle (monthly, annually, etc)?

Does sound like need a 'junction' table to associate asset with test event, something like:

tblAssetTesting
AssetID (foreign key)
TestID (foreign key)
TestDate
TestResult

[Fairlane]

I am playing around with the querrying and some random "tests" to see how the relationships are maintained.

each asset should be able to have more than one test.
each test will associate to many assets
each "testID" will only happen once as it refers to an event, a new test will be created each time a test is performed

[Fairlane]

Do I need an entry in the junction for every asset on every test? My biggest concern is how to set it up.
How does this look (pardon my format, I am absolutely new to DB and Access

Form-
Record source: tblTest
Input: TestID, TestDate

Subform(continuous)-
Record source: tblAssetTesting
Input: AssetID, AssetID2, TestID (default value [Form]![thisone]![TestID])

[June7]

I work in a lab so my frame of reference is for an assortment of tests to be selected from depending on the nature of the sample. Each sample can have many different tests performed and each test will be performed on many samples. I have a reference table of tests, each test has a unique ID. I have a login table for samples, each sample has a unique ID. Then I have junction table:

SampleTests
SampleID (fk)
TestID (fk)

Each SampleID will have multiple records in the junction table, a record for each associated test.

Test data is stored in multiple tables, each test has its own results table to store raw test data.

What are you testing on each asset? If you want the 'test' to be an event where multiple similar assets receive the same testing (which is what - just making sure the asset will power on?), then consider:

tblAssets
AssetID (PK)

tblTestEvent
TestID (PK)
TestDate
TestingBy

tblAssetTest
TestID (FK)
AssetID (FK)
TestResult

[Fairlane]

I am an engineer in an oilfield equipment warehouse and fabrication shop. We buy and build equipment to support oilfield production. I am setting up a database to track material records and testing records.

The test I am interested in is pressure testing. We will buy 30 valves, string them together and pressure test. The chart is then scanned and attatched to the testID. This keeps us from scanning and attatching the same document 30 times. In the future the valves will be tested again.

[June7]

So my second scenario fits?

You will have a result for each valve (data in tblAssetTest) or one result for the event (data in tblTestEvent)?

The alternative to having the two 'Test' tables is to denormalize. This would mean 30 fields in tblTestEvent for the Asset IDs.

[Fairlane]

Sorry, one result for 30 valves. If I could back up a bit it may make more sense: I want to create a document then have that document refer to many assets. I then want to stumble on those documents when looking at an asset

Document1 tests a green valve
Document2 tests a red valve
Document3 tests both valves

green valve shows Document1 and Document3
red valve shows Document2 and Document3

It seems having two tables makes more sense. This is what I think I will need:

tblAssets
AssetID (PK)

tblTestEvent
TestID (PK)
TestDate
TestResult

tblAssetTest
TestID (FK)
AssetID (FK)

I would then want to generate a report which lists all TestResult for an AssetID.

I think I know where to take it from here as I think form for tblTestEvent with subform for tblAssetTest will allow me to acomplish what I want need.

[Fairlane]

Thanks, I was able to get the tables in and with a little finageling I got the report to do exactly what I wanted.