Results 1 to 9 of 9
  1. #1
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13

    Question Add existing entries to another entry

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13
    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

  4. #4
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13
    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])

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13
    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.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13
    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.

  9. #9
    Fairlane is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Posts
    13
    Thanks, I was able to get the tables in and with a little finageling I got the report to do exactly what I wanted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-18-2012, 05:10 PM
  2. Crosstab query with no existing entry
    By Omenphaux in forum Queries
    Replies: 1
    Last Post: 06-06-2012, 03:04 PM
  3. Replies: 11
    Last Post: 05-23-2012, 08:42 AM
  4. Replies: 6
    Last Post: 04-27-2011, 06:12 AM
  5. Replies: 0
    Last Post: 02-09-2011, 03:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums