my first question on SE... most of the time my question has already been asked so I never felt a need to signup in the past. But here we are. It is a bit of a complicated scenario and so I am having trouble even understanding where to begin.

My situation:

I have an accessdb that I am building; this db is primarily a table of results from specific tests we do on products. There are variable results possible for each test type. Therefore, each result is keyed. The results can be grouped by the product conceptually. The results themselves are linked to another table which lists project details that necessitated the tests. Each project is keyed also.

My goal:

Occasionally (and randomly) we perform all the tests we performed on a product twice for quality purposes which means there are two different keys that can be related by result. We evaluate the results to test the quality.

I am trying to figure out a way to pull the two results in the one table (i.e., keyed pairs) into a new table which can then be evaluated using a calculation.

Some of my ideas include:

  • Creating a self-join in the table that allows the two pairs to be linked (manually, by entering the key value of the second result into a new field for that record) but then I am not certain how to take that record and pull the results into a new table... I am assuming query but not sure how. Also, it would seem this method would duplicate the process since there would be two keyed records linked to each other by this process and reported in the new table.

  • Importing a table that lists the two products and their test results on one line, but I have no way of relating this to the result key since that is imported to the database in a separate import which assigns the keys. Also, if I went this route, I could just evaluate the results in this csv negating the need to automate this. Although I could still link them and have it stored there.

Any guidance or suggestions are appreciated


So, I have actually made some progress on this by going with my first idea. I added a DuplicateID to the record and a yes/no button. I then filter the table for the two pairs and go down the list typing the duplicate key into the DuplicateID field of the first sample. I do not do the same for the duplicate to avoid duplicating the result in the query. Then I run an append query on a self-join for the key and the DuplicateID that reports the result for the two values and some metadata for verification then load it in a table. The results are evaluated at a field on the end of the row. When I am done I mark the yes/no box and have the query only run on those that are not marked.

It works but it still is far from automated which is the ideal. If there are any suggestions on how to make this even more automated its appreciated!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.