Audit database design

[johnkeaney]

Hi,

Hopefully someone could give me some advice, as I am new to access and am not fully aware of best practices, or even what it can do fully.

I have been given the task of developing a database for my company which will allow us to audit particular pieces of work. It needs to be able to list the number of errors on each payment entry.

Using this information we then want to be able to pull up statistics for the average errors per peice of work, per person, group of people, and be able to pull up these statistics within date ranges as well as totals.

I would also like to add the feature of specifying the common errors made on peices of work.

From what I understand this is mainly query and report work, which I am having trouble with. I wonder if it is something to do with the way i have set up the tables.
Could anyone give me any pointers on how I should set up my tables in order to make my queries/reports process the data how I need it to?

 

[neileg]

You see, you understand what the business requirement is and we don't. Plus you've had a go at creating the tables, but you haven't shared this with us. Makes advice a bit tricky!

There's some great advice in these forums from The Doc Man about how to go about designing your application, and it all begins with a pencil, a white board and some post-its. If you don't understand it well enough to draw it out, chucking it into Access isn't going to fix it for you!

 

[neileg]

Try this thread to see his advice:
http://www.access-programmers.co.uk/forums/showthread.php?t=92377

 

[The_Doc_Man]

Thanks for the vote of confidence, Neil.

John, the first "Old Programmer's Rule" is, if you can't do it on paper, you won't be able to do it in Access. (Why? Because if you can't do on paper, you don't understand it well enough to program it.)

The second "Old Programmer's Rule" is, the computer won't tell you anything you didn't tell it first. (Leads up to the idea that your design must include provisions for capturing all of the data you want to see.)

The third "Old Programmer's Rule" is, we can't do better on your problem than you would. (See rule 1. YOU are the expert in the problem, not us. Despite our expertise, you know the problem better than we do. And in Access, it is the PROBLEM knowledge that is the ultimate winner.)

Neil's link points to one of many posts in which I have suggested a fairly cheap way to analyze a problem. I'll add something to the post that I don't always add, but probably SHOULD add. Don't try the analysis alone. More than one pair of eyes often helps. Sometimes you learn more when doing it this way and one of you says "X" and another says "not X" - leading to at least an intellectual argument if not a knock-down, drag-out fight. You learn more from the disagreements than from the agreements.

 

[johnkeaney]

Thanks for the advice guys, appreciate it!