05-19-06, 10:29 #1Registered User
- Join Date
- Mar 2006
Unanswered: service amount spent by each dept between two dates (was "Require some help please")
Hi there I am working on a database query in Microsoft Access and I am absolutely bamboozled as to where to go. I am working on a database practical that deals with a company and its car fleet. There are 6 tables the names of which are in bold:
ALLOCATION: allocation num, car reg, employee num, hire date, return date, mileage used
CARS: car reg, model, engine size, fuel type, insurance, auction value
DEPARTMENT: department num, department name
EMPLOYEES: employee num, forename, surname, address, job, salary, department
INSURANCE: insurance group, insurance cost
SERVICE: service num, car reg, service date, service cost
I have been working on this for about a week and no matter what I do I cant seem to get the result I am looking for.
I need to design a query that will display the amount spent by each department on servicing between two specified dates. I am having trouble as this is dependant on the employee's department who hired the car and so I am complete and utterly lost as I am relatively new to databases and would appreciate if someone could give me a few pointers.
Your help in this matter would be greatly appreciated.
05-19-06, 11:22 #2Registered User
- Join Date
- May 2004
- New York State
You know where you need to go (your goal), and you know where you are now (your starting point). All you need to know now is the methodology.
You seem to have a comprehensive database, and if the data is conscienciously entered, it's all there for the picking.
Start slowly; work backwards. You need the service dates between DateX and DateY. You know you need the SERVICE table in the query. In that table you have the car reg, which has been allocated to an EMPLOYEE(ALLOCATION) who belongs to a DEPARTMENT. The only redundant tables are the CARS and INSURANCE tables.
Open Access' query designer. Start designing the table as follows. Since the service date drives the query, select it first from the table list. Since that table has the car reg field, which should have a relationship with the ALLOCATION table, select that table next. Since ALLOCATION has the employee num field, and should have a relationship set to the EMPLOYEE table, choose that table next. Finally, EMPLOYEE has the department field, and should already have a relationship set up with the DEPARTMENT table, so select that table.
In case these relationships have not been set up, create them as JOINs now. "Drag" the fields (with the same name) from one table to the other to accomplish this.
Finally, double-click on each NECESSARY field in each table to place the field in the QBE (query-by-example) grid. You don't need, for example, to report the employee's number, department number, or the car's registration number, even though they're critical to the query. The only fields you need are the service date (where it is between two dates), service cost (the sum), and the department name (grouped).
This is the entire basic design. If you do all the above, and read between the lines, your result should be exactly what you want.
Hope this helps,
05-19-06, 15:10 #3Registered User
- Join Date
- Mar 2006
Thank you sam for you reply. I am still unable to resolve my problem though.
I have decided that I am best to forget about the date X and the date Y and just concentrate on getting the total spent by each department. I was able to do this but to do it I had to use one query based on a query.
When I was trying it first I was getting the total service costs for each car but I want to get it to add all of the totals from the related departments together. My query in design looks as follows:
Department Name Service Department Number Car Reg Employee No
DEPARTMENT SERVICE DEPARTMENT CARS ALLOCATION
Group By Sum Group By Group By Group BY
My output was as follows:
Admin £ 179.84
I.T. £ 326.96
I.T £ 166.54
Sales £ 266.42
Could someone tell me how I could display the data so that the I.T. totals are added together by using one query alone?
Thank you for your help :-)
05-19-06, 20:02 #4Registered User
- Join Date
- Apr 2006
- Huddersfield, UK
There could be a reson why your IT is listed twice, and it could be down to having the IT department listed twice in your departments field.
In order to group them, try using the "group by" function in the access query deisgner, if this fails and still lists them twice, it is likely that you have the IT dept listed twice and it is recognising each as individual records with seperate ID's.
To get around this.
A) delete one record of IT in depts.
b) create a new query (copy and paste this one) in the dept field set the criteria to "I.T." AND "I.T"
But looking at your original staement, it looks as though there is a duplication you have I.T. and I.T (without the full stop at the end)
hope this helps
Dansometimes simple is best.... and i'm just a simple fellow.