Results 1 to 8 of 8
  1. #1
    shahidnc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    11

    HELP Database questions(MS Access)

    Hi everyone,
    I have 2 questions to solve with MS Access, can anyone please guide me how to solve these, I'll be very thankful. Please fins the attached file for the tables.
    5. Create a report to return the ten most lucrative customers in descending order, which displays their Surname, First Name, Total spent (which is the sum of all their bookings), their last booking date and total number of bookings.


    6. Create a report to provide the total price for a specific booking. The report should take the booking number as input and return the booking number, customer surname, total time, hourly rate and total price of that specific booking. Call the report Booking Invoice.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    cross posted here with answers https://www.access-programmers.co.uk...d.php?t=294432

    And I agree - sounds like homework

    please read this link about the etiquettes of cross posting http://www.excelguru.ca/content.php?184

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    shahidnc,

    This is a forum of volunteers who offer help, advice, guidance, suggestions, relevant articles/links etc to those who have questions, or are looking for options, or direction - and who have made honest attempts to find answers.
    This is not a spot for you/posters to hand others your homework or assignment in the hopes that someone will do the work.

    If you have a specific question, ask away and show us your current work/efforts. Give specific instructions to recreate/navigate to the issue in question.

    Good luck.

  4. #4
    shahidnc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    11
    I am stuck with Total spent. how to count it. is design view or in SQL view, and how to count total bookings of a specific person ,I am not asking to do the work for me, but to guide to right directions , I know this is a volunteers form and I really appreciate the people who use their time to help others.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For a query Bookings By Customer

    try
    Code:
    SELECT Count(Bookings.[Booking Number]) AS [CountOfBooking Number]
    , Customer.[First Name]
    , Customer.Surname
    FROM Customer INNER JOIN Bookings ON
    Customer.[Customer ID] = Bookings.[Customer ID]
    GROUP BY Customer.[First Name], Customer.Surname;
    Last edited by orange; 06-25-2017 at 07:13 PM. Reason: reviewed earlier post with database

  6. #6
    shahidnc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    11
    Thanks for your help. I am having a little problem, could you please help me what is wrong in here. why for some "total hours" I am getting result in 0.999999 format. but others are fine.
    Last edited by shahidnc; 06-26-2017 at 04:46 PM.

  7. #7
    shahidnc is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    11
    here is my updated access file and screenshot of the issue
    Attached Thumbnails Attached Thumbnails q3 0.99.JPG  
    Attached Files Attached Files

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    adjust your total time to

    Total Time: Round((([Bookings].[End Time]-[Bookings].[Start Time])*24),2)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-11-2016, 07:30 AM
  2. Questions database
    By williec00 in forum Access
    Replies: 3
    Last Post: 08-02-2013, 10:22 AM
  3. Database of Maths Questions
    By akash2824 in forum Access
    Replies: 2
    Last Post: 05-01-2011, 06:05 AM
  4. First Database Questions...
    By qu1ckdry in forum Database Design
    Replies: 0
    Last Post: 03-01-2010, 09:33 AM
  5. questions on designing database
    By schultzy in forum Access
    Replies: 1
    Last Post: 07-18-2009, 03:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums