Gamecommunity =GCHQ=
http://forum.gamecommunity.co.uk:8080/

working out %ages in acces?
http://forum.gamecommunity.co.uk:8080/viewtopic.php?f=6&t=25611		 Page 1 of 1
Author:  Scorchedbee =GCHQ= [ Sun Apr 09, 2006 9:06 pm ]
Post subject:  working out %ages in acces?
i have this i have made,

http://rapidshare.de/files/17600419/Vil ... l.zip.html

there are 4 time slots, morning, afternoon, evening and evening extension. these can be used by the ID 1,2,3,4 respectivly.

There are bookings most days and i need to be able to bring up a box and select a month like i have in my "invoice" report. it then, depending on the month selected, needs to create a form that shows monday to sunday and a % of these days sued throught that month!

so i need to create a report that looks up every single time slot on every day and shows all 7 days and their percentage of usage.

So i get something that looks like this for january and february in a access form,(snazzy colours not needed;) )

Image

This is needed as i need to learn how this is done for the exam, the exam consists of questions on this project and we have to answer questions on how we made it and exactly what we did!

so i need to know how i can do this please, i have tried myself but all i can do is create a form with the days all ready i dont know the code to put in to get %ages!!!!

Thanks for any help here!
Author:  Varp [ Mon Apr 10, 2006 7:22 am ]
Post subject: 
so to clarify:

your program needs to know:
1. how many days there are in the current month
2. what day of the week any particular date might be (or is that data already stored in your table ?)
3. you'll then need a few queries which feed into another query (which is then the datasource for your form/subform)
- try looking at the SQL Domain Agregate Functions help in Access, in particular SELECT COUNT([idField]) WHERE WeekDay([myDateField]) = 1
- so you'll also want to look up Access Date functions (for the WeekDay() bit)

PS
I'm guessing missing Friday out from your piccy was just a typo ??
:)
Author:  Scorchedbee =GCHQ= [ Mon Apr 10, 2006 9:43 am ]
Post subject: 
yer friday was a typo, i have a qurey that lets ne picj a certain month, not i just need to get this thing to sort out each day and give me a %age of each time slot :( Too hard this!
Author:  Varp [ Tue Apr 11, 2006 7:10 am ]
Post subject: 
I don't believe you can use loops in JET SQL (Access's query language). You could write a query using recordsets in the form's code (VBA) and loops through that way, but that'll perform quite slowly.

SELECT COUNT gives the number of rows which match some criteria

so you need several SELECT COUNT queries to find out how many records meet each criteria, then an uber query to gather all those results into one set of data to present it to your form. Or you could do it in one nested query.

So that leaves you with writing several different queries each of which give you an element of your final result.

Have you covered Recordsets or SQL in your course ?
(wouldn't want to give you help finding an answer you wouldn't know from your course :) )

SELECT Item, Count(Item) AS Tally FROM Products
GROUP BY Item;

Will give one row for each item type with a count next to it...
that helping ? :)
Author:  Scorchedbee =GCHQ= [ Tue Apr 11, 2006 11:23 am ]
Post subject: 
wow really helpful post, ill try it, though im not 100% whats quite going one, lol.

btwno i havent covered Recordsets or SQL in my course, hardly any of the stuff im doing tbh. Thanks for the help so far, ill see if i cna do this today, though doubt it, because i need to learn what im doing for the exam. Thanks again!!! :D
Author:  Scorchedbee =GCHQ= [ Wed Apr 12, 2006 9:04 am ]
Post subject: 
well i tried it!

i have the count query done, i get either 1,2,3 or 4 for each date, now how do i work the %age in a form in access or do i need to run a query and tell it something like when 4 =100% when 3=75% etc? But how on earth would you do that!

Im thinking it must be fairly easy as its out of 4 so is there some code i need?

Thanks for the continued help!
Author:  Varp [ Wed Apr 12, 2006 11:29 am ]
Post subject: 
It's always better, more efficient and ultimately easier to do as much of the number-crunching in the SQL and just let the form concentrate on the shiney stuff.

Is the maximum possible 4 ?

If it is then your query just needs to have something like

SELECT 100/(SELECT COUNT(myField) FROM myTable) as MyPercentage

If it isn't then you need to do a more complicated sum something like

(Actual Count / Maximum Possible) * 100
Author:  Scorchedbee =GCHQ= [ Wed Apr 12, 2006 4:53 pm ]
Post subject: 
i did this, and i get an "error -3005 no error is defined ....."

Image
Page 1 of 1 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/