Newbie in deep

[computerfreaks]

I'm an IT technician, but have no experience in access, I'm trying to make a Cost of Quality database:

Whenever theres a non-conformance it has to be detailed as to its costs involoved, there is then a printed record of that non-conformance. Then these records must be collated for a monthly review meeting in the form of monthly statistics compared to sales e.t.c These monthly reports are then collated for a yearly report.

I'm having big trouble getting it all to work and need loads of help.....

I have tables for different costings, e.g Machining cost, Admin cost, each of these has fields for time in hours, minutes, rate per hour e.t.c, sometimes a non-conformance doesn't hav one of these costings, so when I try to collate them into a query the query turns out blank due to some tables being empty!

The other problems I have had are from having so much information to work on and re-calculate, for example, the non-conformace is broken down into loads of costs, I then need these summing, the sums must then be summed for the monthly, and those sums summed for the yearly.

Please help, I'm in the deep end and haven't learnt to swim yet!!

 

[KKilfoil]

You need to rethink your basic structure. What you now have is what db gurus would call a non-normalized structure.

You should have a single table for all of your CostingCodes with a Primary Key (PK). It can have a field for Type, where you can catagorize into Machining, Admin, etc. Add any other fields you need.

You need a table for your non-conformaces, with a PK. In fact, all tables should have a PK, even if you don't think you need it. Trust me on this.

Finally, you need a linking table, with a PK, and two Foreign Key (FK) fields, one for each of the Nonconformances and CostingCodes. Here is where you store elasped time, etc.

Ensure referential integrity (RI) is set everywhere.

After that, you can begin population of the db and design of queries, etc.

You can filter on the CostingCode Type field in any queries, etc, if you want to limit your records to, say, Machining.