Is there a way to track not user changes, but the workflow of a database?

[Carri]

Hello,



I started using Access recently when it became clear that the data I manage is too complicated for me to manipulate it effectively in Excel. The main issue is that the data undergo so many transformations that when suspicious results were found, I couldn't determine where an error may have happened. I am hoping that I can track the flow of data management more effectively in Access. It would be nice to be able to say "Look, here are the steps of transformation that were done to that data set; let's look and make sure the calculations were correct." I was thinking that that the relationship page would do this, but even in my test database I'm starting to lose track of which query did what. With all our many data subsets included I think it will be a similar situation to the many, many datasheets in Excel that made it so hard to figure out what had happened to the data previously.

How do you-all manage this issue? Is there some kind of output tracking I can implement? I'm thinking of either a Stella-type diagram page that would map the workflow of the database, or an output log like statistics programs provide, but any other reasonable solution would work.

Thank you,

Carri Marschner

[ItsMe]

I am going to go out on a limb and say the key is normalization. When you open your tables you should hear angelic music in the background. There should not be any evil lurking between any two integers. If all is well with the tables, the rest is easy peezy.

What are some tables that you consider to be "main tables"? Which tables are the ones that get updated most often. Can you post their names and the field names/types? I would expect to see several fields listed within that act as foreign keys to other tables.

[Carri]

Hello ItsMe,

Thank you for the response; I'm certainly not hearing any angelic music yet. The purpose of this database will not be data entry, but data management. Data is entered in Excel, and I upload the completed, proofed data (theoretically, anyway) into Access so I can combine years of data, combine different datasets, and run calculations on the data without an endless series of error-prone copying, pasting and sorting processes. That's the plan, anyway. So all the data are going in as flat files, undergoing normalization, and then being combined with other datasets. The end result will be output back into Excel for statistical analyses of an angelically-accompanied unified dataset for the whole experiment. Being able to defend this final dataset is the reason I want to track the workflow through normalization, combination and transformation to standard units.

Once I complete normalization, my main tables will be crop yields, weather, cover crop biomass, cover crop nutient data, weed biomass, weed counts (by species), labor and operations, field actions, maybe insect and disease data, soil data, and crop nutrient data. There will be a few additional peripheral datasets that contain side experiments. The anchoring tables will be plot information, which will link to all the data collected at the site, probably weather information which will tie to the years, a species table for weeds, and I was hoping to tie the various datasets together using year-crop-plot identifiers to link the data from similar years together.

All of these data sets will come into Access in their own tables, which then will undergo the various process required to create the final dataset. The set I've started with is the most complicated, which is weed counts. They have multiple samplings per year, up to 50 species per year, and two sample types. LOTS of normalization to do! I've attached a shot of the file for one year out of ten.

I hope this helps; I'm not sure how clear this is to anyone else. What I'm looking for is advice on tracking the workflow through normalization, etc.

Thank you,

Carri

[ItsMe]

It is a difficult thing to transfer operations from excel to an RDBMS. It is probably a good approach to first find a solution that imports the data in a normalized fasion and then introduce users to an interface where they can add data directly. It is rare that a spreadsheet easily imports into a DB, especialy when workbooks are used for production purposes. The simple fact a spreadsheet is intuitive will usualy equate to a spreadsheet that is not suitable for importing. You may want to revisit how the spreadsheets are structured and how the users interact with them.

Some complex VBA code may be the best solution to import the appropriate data and place it into its respective tables within the DB. Simply mirroring a spreadsheet inside an RDBMS will not suffice. You need to somehow determine which data needs to be imported. This is why I was asking about a table that gets more updates than others. For instance, it would be a mistake to import the literal text "2008Soy7/11/2008All12A" and duplicating the text in every row of a table that logs events. YearCodes should reside in their own table and the value for YearCodes should be represented by its primary key in a foreign key field. This is where complex coding would come into play.

Also, the weed types should have their own table. A junction table may be in order to include the many weed types. You may have a many to many relationship that you will want to query where many year codes relate to many weed types due to the fact there are several quadrants included within any given year code.

Once you have these tables sorted in a way that is intuitive from an RDBMS perspective, it will not necesarily play well with a spreadsheet. At least not for importing the many fields of information necessary. YOu have to select the appropriate fields within the excel files to CREATE records and choose the other cells/fields that will simply reference PK values for records that already exist within the RDBMS.

[rpeare]

So in your example spreadsheet each column starting with TRFPRR is a weed type and the numbers are the quantity of that weed that was found on the plot listed?

If that's the case it's pretty easy to normalize your data for this particular spreadsheet, and by default, the remainder of your spreadsheets if they are all set up like this. But it doesn't sound like you're having trouble with the transformation, just the documentation of what happened when to your data. If you are looking for an automatic process to document your changes to data as you process it one does not exist. You'd likely have to build an auditing table that was linked to your Plot field (assuming that's the unique key of your data) that had a list of the dates/times and the query names that performed the transformation

So let's say you run three sets of transformations on your data you'd have something like

Plot  TransDate  TransQuery
11A   1/1/2013   QueryAAA
11A   1/5/2013   QueryAAB
11A   1/14/2013  QueryAAC

This table would likely have to be part of a vba script or run at the same time your updating/transforming queries run to properly update or add records to your auditing table. Without knowing the transformations you're working on (formulas etc) I don't know that I can think of a better way to do this. It seems like after you get done with your process you're ending up with some wonky results and you want to be able to backtrack through your steps to find out where the expected value started to go wrong if that's the case you could also do an auditing table for value so you can step back through the value as it transforms through your process. This would sort of rely on you having a consistent value through your process or the 'final' value of a calculation and a record of all the items that went into performing that calculation.

Another option may be to put in a table that records tolerances for values so that when you're processing you can cycle through your records and see if you're going to end up with an unexpected value and address it before the data is transformed.

[Carri]

Hello rpeare,

That is the problem we were having; wonky results and a real challenge to find them or redo the data without having more, similar problems. For instance, our spelt weed data wound up being twice as big as it should have been, because I failed to remember that we use a quadrat half the size of the ones we use in other crops. It took quite a bit of time to track that one down in Excel, figure out what had been changed after that point, redo it and get the corrected numbers into the final datasheet. It seemed like it would be easier to re-run a query in Access and then update all information downstream of that change.

That coded table you've suggested above would be a big step forward; it would help me figure out what I'd done when, and where to look for mistakes. Are you suggesting that I could build a script that would create this as I worked, or would I just keep it updated manually as I went forward? I also like the idea of record tolerance; that would help with making sure the end numbers are in the right ballpark. Is that an existing function?

ItsMe: I agree that it would be preferable to shift into Access; unfortunately I'm the only one in my work group that feels this way . So I'm trying to create with a system that circumvents some of my common problem situations while maintaining an input/output that the rest of the group is comfortable with. I may have selected the wrong software, and should be building in SAS Enterprise or some other stats-based database platform; Access just seemed like it had the lowest learning curve of the available options. If there is a more suitable software you could recommend, I'd be grateful.

[ItsMe]

To me, Access is two things. It is a data management system and it is an application development tool. If there is an off the shelf solution that will manage your data, go for it. I think you are choosing wisely by looking into Access. Get the data into tables. Create queries. Then worry about a public interface.

I think you will have the same issues trying to place your square peg into a round (off the shelf) solution. You will still be burdened with the same task of normalizing your data. I believe your solution will need to be unique. Help us understand the points. Right now we are guessing. Do some research or tutorials and then ask some more questions. The more knowledge you have the better you will be able to communicate in this forum and the better the support will be.

I will say this. It won't be easy and then you get to deal with the users and office politics. Fun stuff

[rpeare]

I don't know what your transformation process entails so I couldn't suggest whether you updated it by script (automatically) or by hand.

I would love to know, for instance, how you transform the data in the spreadsheet you showed into your 'final' output. Without knowing the exact steps I don't know if a tolerance table would work for you or not, it just sounds like you have the same calculations going on by crop type, but your parameters may change (i.e. multiplying by .25 for a quarter size plot as opposed to a 1 or perhaps even a 2 or 4 or 8 multiplier for other crop types)

If you were to have a normalized table you'd have to have something like

Weed_ID  WeedDesc ----> calculation related fields (possibly)
TRFPRR
TRFPRS
PALAMAR
PLAMAS
SPELT

where WEED_ID would match your column headers on your excel spreadsheet and and WEEDDESC would be whatever you wanted, then you could have your variables you want to plug into your various formulas in the related fields to ensure that once it is set up there are no human related errors in the calculations.

and your normalized table would be something like:

Plot  Weed_ID  Weed_Count ---> other weed specific information
11A   SPELT    4
11A   TRFPRR  10
11A   TRFPRS  1