Database to track due dates, can this be done?

[Skyhaze001]

Hi



I'm virtually a complete novice at Access so don't really know it's limitations, I have a tiny bit of coding experience from a few years ago so I would appreciate some help please.

At work I have a lot of information to track, a few examples are:

Risk assessment review dates and issues raised
Inspection due dates (LOLER for fork lifts etc)
Training refresher due dates
Supplier certification expiry (ISO9001 etc)
Customer complaints and their close out deadlines
Legislation review
Internal audit due dates and issues raised

I currently use a number of different spreadsheets to track different things but it's a PITA to keep up with and things always get forgotten. I am a quality manager with two staff that have responsibility for keeping different bits up to date so I would like a central place to make keeping track easier. I know that I can create different pages within a database to track each different area but I would like a page that essentially says within the next 30 days you have these risk assessments due, this supplier's certificate is going to expire, you have this many customer complaints still open. Is this possible? And if it is possible is it very easy for someone with limited knowledge to create? My company won't pay for a system ready made with this functionality but it will make my life so much easier that I want to use my spare time to create something.

[ranman256]

In a query, use DateDiff to alert user the date is near:

Iif(DateDiff("d",[ExpireDate],Date())<30),"Alert","")

[Skyhaze001]

Thanks. Can you create a kind of landing page that links to all the different sheets you have?

[Cottonshirt]

yes, this can be done.

the danger is that in your haste to get it all put together you overlook the essential differences between a database and a spreadsheet.

to explain, let's look at just one of your processes, Training Refresher due dates.

on a spreadhseet you would probably list staff in column A and have columns for each type of training and enter dates when they take that training. next to that you have a column that calculates when their next refresher is due. something like that.

on a database you have a table listing the staff, and the temptation is to have "columns" for each training just like a spreadsheet. but this would be the wrong way to do it.

on a database you would have a table for each training type, and a field for the frequency at which that training needs to be completed.

then you would have a third table with fields for employee, training type, date completed

so you would have something like this.

a staff table:
tbl_stafflist.staffID
tbl_stafflist.surname
tbl_stafflist.firstname
tbl_stafflist.joindate
tbl_stafflist. other fields as necessary

a table listing training
tbl_trainingtypes.trainingID
tbl_trainingtypes.trainingtype
tbl_trainingtypes.provider
tbl_trainingtypes.frequency
tbl_trainingtypes. other fields as necessary, phone number, price, etc.

a table showing who has done what training
tbl_trainingdone.doneID
tbl_trainingdone.staffID identifies the person who did the training
tbl_trainingdone.trainingID identifies the training they did
tbl_trainingdone.traindate identifies the date it was done

a database like this will allow you to calculate who has done what training, when their refreshers are due, and display a report showing which training needs to be booked in advance, who to book it with and their phone number / email address, how much it costs, what your training budget needs to be and anything else you might possibly want to know.

but this won't look anything like a spreadsheet, and to someone who is coming to databases from spreadsheets this difference is the biggest hurdle to developing efficient databases that work and are easy to maintain and use.


good luck with your project,


Cottonshirt

[Skyhaze001]

Hi Cottonshirt

Thank you very much for that explanation, you are right that I was looking at it as a spreadsheet. I spent a couple of hours playing around creating tables a little while ago and I was just building spreadsheets in a database. But I can see how doing it that way does make things more efficient. You create one list of companies you deal with and that allows you to track supplier complaints and approvals just by using their ID number rather than repeating the list for both databases. Seems I have a lot to learn!

[ssanfu]

"Old Programmer's Rule" is this: If you can't do it on paper, then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



I stole the following from a post by Ajax:
------
Access is not a bigger excel - it is completely different. Excel combines data and presentation (i.e. calculations) in a single view and the data is typically stored is 'wide and short'. Access stores data in tables and presents through queries which are then used in forms and reports. Data is stored 'narrow and tall'

Access works by using the rules of normalization - in its simplest terms, stores data only once, Excel is about as far away from normalization as you can get. Consider your list of gym member activities - how many times do you repeat the same gym members name or other details? In Access you have a table to list the members details (once), another table to list the exercises and yet another the measurements. You can even have a table of categories and instead of using the iif statement you would lookup the category for any given BPM. Benefit here is if you decided to change the category, or the BPM range it applies to, you would not have to go into the code to change it, you would change it once in the table.



And I stole the following from a post by orange:
------
"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."


Post back when you have questions...... BTW, you realize this is not a quick process? It might take several months, if not longer. But it is Well worth it.


Good luck with your project.

[CarlettoFed]

If you can attach the excel file, where there are the various sheets that will have to be transformed into tables, with at least a couple of lines each and replacing any sensitive data, it would be easier to help you by creating an example file.