Calculations in Access???

[Manaz]

Hi

I am currently setting up a data base for my work and was wondering if there is any way to do the following calculation:

I have a database of automatic doors that are contantly being repaired and i want to keep a record of the accumulated costs involved with this.

I have set up a form to update each door and its accociated repair cost, the problem is when i enter the new repair cost access replaces the current cost with the new and i want it to be added to the current cost so the accumulated cost can be bulit up over time.

I hope this makes sense

Any help would be greatly appreciated!!

 

[pbaldy]

You can do what you ask, but I'd actually recommend a different structure. I'd have a "repairs" table with a record for each repair (fields like door number, date and amount). Then you simply sum up the values to get the total for any given door, but you have a history you can analyze if necessary.

 

[Manaz]

Thanks for that. I will try that way. But how would you go about doing the calculation as i mentioned above?

 

[Manaz]

The reason i ask is because we already have a data base for the ivoices. Also the results from this database will be reported on a web page for the customers to access and if i could do it the way i mentioned it would make the presentation of the data easier. It would be great if somebody could take me through the steps involved.

Thanks

 

[The_Doc_Man]

Whatever the folks see on the web doesn't have to be the whole story.

If you treat each repair incident individually, you can still have a summation query that will add up the incidents for each door and tell you the things you saidyou wanted to know. But having the individual incidents on record also gives you trend information, service technician effectiveness, problem doors, and (depending on what you store per incident) most frequent problem.

 

[Manaz]

Thanks for your reply. So how would i go about doing it that way with these factors:

1. There are 300 stores around the country each containing 1- 11 doors

2. I have to present the data as total costs accumulated over the financial year to repair mechanical breakdown, service breakdown, electrical breakdown and damage on each of the doors.

would it not be easier to have a database of the doors and update them regularly?

And would anyone actually be interested in showing me how

 

[pbaldy]

You've made our case for us. With your design, how do you know the cost this year vs last? With a properly designed repairs table, you can get this year vs last, compare mechanical vs service vs electrical, etc. Off the top I'm guessing you'd need a stores table, a doors table and a repairs table (with perhaps a repair type field too?).

Your original question isn't particularly tricky. One way would be to have the user input the new cost into an unbound textbox. When you're ready to save, add that value to the existing value. However, that's really not the way to go. In fact, what if the user makes a mistake? You've got no way to go back.

Trust me, whatever the powers-that-be may want now, eventually they'll want to group/summarize data in ways more complicated than your proposed layout will handle. Do you want to say "sure, no problem", or "sorry, I can't do it"?

 

[The_Doc_Man]

I'm going to suggest some reading first, then (with that reading in mind), re-read what pbaldy posted.

Go do a Google search on the key phrase "Database Normalization" - then weed your way through the gazillion hits you'll encounter. Look through the ones from .EDU sites or from the .COM sites of known database providers. They will give you an idea of what normalization is and what it does. With Access, we tend to shoot for 3rd-normal form. You can go up to 5th-normal, but if you make to to 3rd-normal, Access will treat you right.

I think you need this general structure...

table Stores - has address info, store ID (Prime Key), other info you might wish to note, name (or foreign key for JOINs if you have a separate personnel table in the same application) of point of contact for that store.

table Doors - has door ID (Prime Key), store id (foreign key), location description, size info, etc.

table Repairs - has door ID (foreign key), Incident number (Prime Key), date of reported need, date of repair completion, cost.

Establish relationships 1-many:stores-doors and 1-many:doors-repairs, with relational integrity enforcement turned on for both cases.

Now you can write a query that lists every door in every store by joining the door table to the store table across the store ID field.

Having that query, you can write a SECOND query that lists every repair on every door by joining the repairs table to the doors table (or JOINed store/door query) across the door ID.

Having that query, you can write aggregate queries that sum the cost across all doors grouped by door ID. You can write reports showing individual door repair histories grouped by store and by door. You have the basis for your history, cost, and all sorts of break-outs of same, grouped by anything that is in that second query.

Having the table structure with proper relationships also allows you to build a data entry form that is based on parent-child relationships (a.k.a. 1-many) very easily. Because once you have the relationships defined, the query wizards and report wizards and form wizards have information to supplement the raw data of the table design. They "know" what field in table A corresponds to a field in table B. Which only helps you, never hinders.

Before you get too deep in this, please consider doing some SERIOUS study of normalization or you will not understand why we took the approach we did. You will also not know where to put things until you understand exactly why normalization does what it does. Consider this as an up-front cost of doing business that will save you a ton of retrofit costs later. Yes, this is literally a "pay me now or pay me later" situation.

 

[Manaz]

Thanks very much for your help. I have been doing alot of reading and am beggining to understand what you mean.

Your help is most appreciated

One further question...

If i wanted to group the repairs on each of the doors into mechanical fault repair, electrical fault repair, damage repair etc would i have to have individual tables for each of the types of repair and then relate them?

Thanks

 

[pbaldy]

Probably not. A field in the repairs table indicating the type would get you that. An exception might be if there was additional info required about each repair, and the types of data required differed significantly enough for the different types. You need to have a handle on all the pieces of information that need to be stored, and then lay out the tables accordingly.

 

[The_Doc_Man]

Here is where you run into "wrinkles."

As PBaldy suggests, you can just store a code for "repair code" in each repair. Where things get hazy though is what you do next.

OK, let's say you have codes for mechanical, electrical, hydraulic, pneumatic, actuators, hinges, broken glass, etc.

If that's where it stops, just add the code.

If you need more information than this, you have to decide what else you need and just how much variation there can be between types of repair and the amount of information you track.

Suppose, for example, you want to know what parts were replaced. You could have a "replacement parts" table, many/one, with the one side being the repair ticket number. You could also have a (somewhat free-form) actions table with the one side being the ticket number and having as many records as there were actions in the repair.

But if the information you wanted to track is more complex than even this structure could support, you will have to very carefully rethink what you are doing. I'm not saying you can't track more complex issues, it is just that there is a point of diminishing returns. You have to ask yourself how often you will use the data you are gathering this way. If the answer is "we won't use it" then why capture it?