Tracking test results over time - new field needed?

**JP Romano** · 03-07-2011, 02:22 PM

Hello all, I'm trying to convert an Excel spreadsheet, loaded with forms and macros, into an Access DB to accomodate growing size and number of users participating. This may end up being a little verbose, so I do apologize, but will put *** near my actual question towards the end.

Right now I'm using this spreadsheet to track the results of regression tests over time. Tests are run by an automated system a few times per day, and all of the failures are logged in a text file and distributed by email. I extract the text and plop it into a spreadsheet, which does some clean up work. I track all those failures, log new failues, and assume any of the previously failing questions which are not in that log are now passing the regression tests.

So, anything in the log is a failure. I use a combination of user id and timestamp from the actual question to determine if it's a new issue(in which case it's added to the bottom of the list) or an existing query which had previously been passing, but now fails (in which case the line containing the original query is marked with a FAIL. Everything else is updated as a PASS.

*** In order to replicate this in Access, where a single query needs to be updated 3x per day, and I need to KEEP the previous updates for that item, do I need to add a new field to my table every time I want to log results?***

I apologize if this is unclear - just trying to get my bearings, and will be happy to clarify however I can.

Regards, and if you've made it this far, THANK YOU!

**alansidman** · 03-08-2011, 01:12 PM

I am confused by your explanation. Perhaps if you laid out your db design listing the tables and fields with Primary Keys and foreign keys, it might be easier to understand. Here is a link on data base normalization that will help you to understand. Access and Excel do not work similarly so you might have a need for several tables.

http://forums.aspfree.com/microsoft-...es-208217.html

This will also be helpful

http://forums.aspfree.com/microsoft-...el-349267.html

Alan

**JP Romano** · 03-08-2011, 02:03 PM

Alan, thank you so much for your time, and my apologies for not being clear. This database is brand new, so I'm not tied to any particular structure, but in my preliniary testing, here's how it's set up. A little more info, though - every day, our development staff runs an automated regression test against the engine of a financial data question & answer system. The regression tests include approximately 4,000 queries which had previously been answered correctly, plus any new questions asked of the system on the previous day. So we have an ever growing list of questions (though growing by only a few a day) being run through the engine three times per day.

When the regression test is completed, the results are emailed in a pipe delimited text file. I currently copy that file into Excel, and compare the current results against the previous set of results.

Each query has a unique number comprised of the user's numerical code (anywhere from 5-9 digits) + hhmmss.

In Access, I have two tables. The first table (called "Questions") uses that unique number as the primary key, and contains fields QUERY, OWNER, ID (the key), CURATION NOTES (memo).
The second table (called "Results"), uses the same unique number as the primary key, and contains fields ID (the key), PASS/FAIL, EXPECTED RESULT, RECEIVED RESULT, FAILURE CATEGORY

What I'm attempting to do here is retain the results for each test run. The file is shared, so that the OWNER can review his/her failures and take actions to mitigate. We also review the results to a single query over time - so we can see that a particular question was fine until the last test, or a question has gone from pass to fail repeatedly because of the downstream effects of something else...

I hope that helps - I'm certainly not adverse to starting from scratch and doing it the right way (problem is, I've been awful with MS Access since the 2000 version!)

Thank you for your time, and any guidance.
Regards.

**alansidman** · 03-08-2011, 05:23 PM

Ok. I think I got it.

Table Questions
----------------
ID -- Autonumber (PK)
Query----Just a note here that you may need to change this field name asit may be a reserved word in Access. Look at the link atthe end of this message.
Owner
Curation Notes

Results table
--------------
ResultsID -- Autonumber (PK) I think you should have this to identify eachrecord uniquely. Then use the QuestionID as your ForeignKey to link to your Questions Table.
QID--(FK)
Pass/Fail -- You may have to remove the "/" as I believe Access will notaccept this in a field name. See other link at end ofmessage
Expected Result
Received Result
Failure Category

I think that if you add the new ID then you will have a one to many relationship between your Questions Table and your Results Table and you should be ok. Now you can have multiple results for unique questions

Here are the links on reserved words and unacceptable characters.

http://office.microsoft.com/en-us/ac...010030643.aspx

Post back if you encounter any issues.

**JP Romano** · 03-08-2011, 06:16 PM

Wow... that makes a lot of sense... thank you so much...
I do have a (hopefully) final question.... and please forgive my ignorance here, but I'm trying to edit the relationship via the "Edit Relationship" pop up.
I'm attaching a screenshot of what I see now - how do I get that relationship type to One-To-Many? (In my resultset table, the unique id is ASFB UDTM)

FYI, the field names are a little different than what I noted previously. I was scaling them down to a minimul number to avoid any unnecessary confusion.

I'm sure I'm missing something stupid here...

Thank you again.

**alansidman** · 03-08-2011, 07:29 PM

Your ASFB UDTM should not be a primary key on your second table. Make it the foreign key and add a new field as the primary key and set it to autonumber. You should not be concerned about setting it as a one to many. That will occur naturally once you begin data population. In fact, I usually do not even make the relationship unless I am going to use subforms. I join the tables in my queries. I suppose it would be proper to make the associations ahead of time, but I don't. Make sure you read the links I sent you especially the one on relational databases. Once you understand that concept and data normalization, the whole thing will make more sense and pieces will fall into place.

Alan