Results 1 to 4 of 4
  1. #1
    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.
    Thank you

  2. #2
    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,


  3. #3
    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
    Group By Sum Group By Group By Group BY

    Show Show

    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 :-)

  4. #4
    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

    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts