1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to create statistics in Access

Discussion in 'Business Applications' started by pc_doctor, Jan 25, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. pc_doctor

    pc_doctor Thread Starter

    Joined:
    Jan 22, 2005
    Messages:
    484
    Hi,

    Im trying to create statistics for a database.
    These statistics could possibly outputted into Excel.

    For the statistics i want for example, what percentage of the village hall is being used a month.

    Has anybody got any ideas?
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Um, I believe we'll need to know quite a bit more about the database. What comprises its dataset? What's its relational structure? On what relations do you want statistics?

    If you don't feel comfortable giving that kind of information, Access queries support some scalar standard deviation functions and population functions, but that doesn't sound like what you're after. Are you having trouble with the math? Not knowing what you store, the following is just a guess based on your example statistic:

    Code:
    SELECT MonthOfUse, (DaysUsed / DaysInMonth) * 100 AS PercentageOfUse
    FROM tblVillageHallUsage;
    
    That's just a guess at what you're after. I'm not very good at using the QBE editor, so I'm not exactly sure how that'd translate into a QBE-designed query.

    Anyway.

    chris.
     
  3. pc_doctor

    pc_doctor Thread Starter

    Joined:
    Jan 22, 2005
    Messages:
    484
    Hi,

    Im trying to get statistics/calculations for the total money taken for occasional bookings and the percentage useage, including regular and occaional bookings, for each weekly slot of the Village Hall.

    I have attached a print screen of the relationships between my tables and the field names.

    I am happy to send the database over as this is only a piece of coursework, but trying to work with access isnt easy!!!

    Cheers,

    Jon
     

    Attached Files:

  4. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I am too busy to do your course work for you but I can point you in the right direction.
    Make a select query which contains your items to be calculated. In the query design view click on "View" on the main menu and tick the "totals" and try out the different maths you can do with the new row it creates.
     
  5. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Well, I'm not going to do your coursework for you, I doubt many people would. However, I don't think you should be intimidated by the problem at hand, because the math is pretty simple. The percentage of any thing is the specific quantity divided by the general quantity, times 100. So then,

    percentage occasional bookings = (number of occasional bookings / number of total bookings) * 100

    Calculations of totals are basically summations of like numbers, so you need to find a way to sum a group of numbers. Look at the scalar function Sum and the SQL keyword GROUP BY. What reference material does your course use?

    chris.
     
  6. pc_doctor

    pc_doctor Thread Starter

    Joined:
    Jan 22, 2005
    Messages:
    484
    Hi,

    Its AQA - we dont get any reference material.

    I have been able to get the total cost for all the bookings made.

    Im still struggling with the amount of time its in use.
     
  7. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    First, try a SELECT query to pull only the occasional bookings for a single month.

    Then, try a SELECT query to pull all the bookings for that same month.

    See if you can't combine those principles with the logic I supplied earlier to come up with a percentage. The final solution will probably include a GROUP BY.

    chris.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - create statistics Access
  1. estyMonsty
    Replies:
    2
    Views:
    393
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/437125

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice