Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2013
    Posts
    16

    Unhappy Unanswered: Sql queries - please help

    Okay - Thanks
    Last edited by NewBie13; 12-12-13 at 04:53.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried
    what worked
    what hasn't worked
    what are you stuck on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013
    Posts
    16

    Queries

    I have had an attempt at all the queries using various methods, such as sum, count and for others NOT NULL.

    I am so confused because they all seem to give me blank databases or countless errors.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you actually tried.

    first off get your table design in good order. that means defining relationships (use referential integrity

    1. Which staff have received training for driving an electric car?
    assuming you have correctly defined the relationships then open the query designer
    put the tables that have the relavent data onto the designer
    put the required columns into the box
    run the query
    it will probably look something like
    Code:
    SELECT [FirstName] & " " & [lastname] AS Staffname
    FROM Staff INNER JOIN DriverDetails ON Staff.StaffNumber = DriverDetails.StaffNumber;
    as to:-
    2. How many bookings were taken for travel by petrol cars during the week
    beginning 1st Sep 2013?
    3. Which staff member was the last one to book an electric vehicle?
    4. List all the staff members who have cancelled bookings and when.
    5. During 2013, what percentage of bookings were not used and therefore expired?
    make an effort post what you have done and explain where you are struggling
    Attached Files Attached Files
    Last edited by healdem; 12-10-13 at 07:19. Reason: added (unchanged) zip file from OP
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2013
    Posts
    16

    queries

    Thank You i will use this template to try and devise the queries and then let you know. Appreciate this very much.


  6. #6
    Join Date
    Dec 2013
    Posts
    16

    Red face Relationships

    Hi

    I have tried to amend the table and the relationships however i am stuck because when i attempt to enforce referential integrity where required i often gives me errors.

    Vehicle Details registration is 1 to many with vehicle booking registration as vehicle can be booked several times but there is only one of them.

    Similarly staff staff number is 1 to many with booking staff number as staff can take various number of bookings for themselves

    Manager Manager Number would be 1 to many to booking manager as one manager may authorie more than one booking (same with receptionist)

    however i get error messages such as no unique index or violation to referential integrity rules. Please can you help me in troubleshooting this so the relationships are proper which should help create the queries.

    Please help me with this and thanks for your support so far.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Because you entered data into the tables without properly defining the relationships now you have a problem. The problem is straightforward. You have data in the child table which is t in the parent table

    Fix the data first then fix the relationships. If needs must use one of the query wizards to identify tbe problem data

    Where the message states there is no unique index it means there is no primary key in the parent table. In a relational db EVERY row must have something that uniquely identifies that row
    Last edited by healdem; 12-11-13 at 05:26.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2013
    Posts
    16
    cheers for the advice
    Last edited by NewBie13; 12-11-13 at 05:56.

  9. #9
    Join Date
    Dec 2013
    Posts
    16
    I will do some editing and then get back to you, cheers
    Last edited by NewBie13; 12-11-13 at 05:56.

  10. #10
    Join Date
    Dec 2013
    Posts
    16
    Ignore this message
    Last edited by NewBie13; 12-11-13 at 05:56.

  11. #11
    Join Date
    Dec 2013
    Posts
    16

    Relationships

    I have got some of them working however the receptionist receptionist number to booking receptionist number is the only one remaining. it gives error message violation to referential integrity as some records in the foreign table do not exist in the primary.

    How can i solve this issue?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sit back and think about what the error message is telling you

    then check your db and see if the error message is reasonable, and if so take appropriate action

    learn by doing.....
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2013
    Posts
    16

    Relatiosnhips

    Thanks i did that and it was a silly mistake i had only three receptionists (1-3) but in the booking table i had 1-5, the relationship now works. I tried the first query

    SELECT StaffNumber, LicenceNumber, ElectricTrained
    FROM DriverDetails, Staff
    WHERE DriverDetails.StaffNumber = Staff.StaffNumber AND ElectricTrained IS NOT NULL;

    It asks me for the values and then displays blank database

    Second query has missing expression

    SELECT BookingNumber, FuelType, DateFrom, DateTo (*)*100/(SELECT COUNT (*)
    FROM VehicleDetails, Booking) AS [Petrol Cars Booked First Week September]
    FROM Vehicledetials, Booking
    WHERE DateFrom >#01/09/2013 AND <#09/09/2013# AND VehicleDetails.FuelType = "Petrol";

    Thanks
    Last edited by NewBie13; 12-11-13 at 06:56.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are being asked for values then sql engine cannot find the column and or table names. meaning either
    you have a typo AND/OR you have a missing table

    you need to look at what the compiler is telling you
    why do you think the second query has a missing expression?
    why do you use the # symbol?

    aside from the fact the where clause is garbage
    WHERE DateFrom >#01/09/2013 AND <#09/09/2013#
    any expression must evaluate to a boolean value (a true/false test)

    computers are pedantic and idiosyncratic. you have to explicitly tell them what you want to do.

    if you want an upper and lower limit then
    WHERE acolumn >10 AND acolumn<50 'is fine
    WHERE acolumn >10 AND <50 'is garbage there is no column specified for the second test

    if you need to do multiple comparisons then like an equation isolate elements with a pair of brackets, eg
    Where (Username = "Smith" AND postcode like "B*") OR (Username = "McKenzie" and (postcode like "EH*" or City = "Edinburgh")

    consider using the between construct

    NULL is a special construct to test for NULL you cannot use = NULL instead you must use a function such as ISNULL(mycolumn)

    what do you think makes you need to test for NULL on the column 'electrictrained'?

    to be honest you need to spend time working out what is going on and fix it. its part of the learning process, not hitting a problem and asking here.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2013
    Posts
    16

    Queries

    Thanks for the query support have been trying to work out what is wrong as i stared this DataBase time ago but just kept getting mistakes. I understand what you mean by the learning process and i will take that on board to try and fix the issues.

    Thanks

Posting Permissions

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