To be honest, I have NO doubts that the database is probably poorly structured, as it started as a way to show my boss that we could do things better, and then suddenly I was tasked with building one with no real databasing knowledge. I managed to find a work around, but this whole damn database has just been cobbled together, and I could probably use some help to make it more efficient.
ANYWAY, the purpose of the database is to aggregate the results of tests of over 250 computer programs and eventually output a summary of the results. Each program test has between 3 and 40 tests, and no two tests are the same. Each test has to be completed by at least 2 testers. The summary needs to state who tested the program, any defects, the relevant defect number and the date the tests were completed.
I have an application list, tester list and a results list to normalise as much data as I can.
Currently data is entered directly into the table, and then put through a query with a nested 'iff' function to calculate whether it passes or not. The individual test results are amalgamated using union queries. This is where I encountered problem number 1, Access cant handle 200+ union queries. As an access noob I just created another few queries and then unioned THEM together. Eventually it got too complex for access to handle so I had each list appended first.
The summary output was problem number 2. It Concatenated the testers and comments into one field, as well as running another nested 'iif' function to see whether the program passed as a whole. And this of course runs incredibly slowly as well, so ended up having to append that to a table as well.
I would upload it, but honestly it would probably make you cry.
Greg
