Query percentages

[AuroX7]

Hey guys.
I have a boolean expression which I want to calculate the percentage of.
I have the field 'Regular Booking' which is either true or false. I have 6 true and 5 false, equalling to 11 in total. I've tried using: 100*[CountOfRegular Booking] / [TotalRegular Booking] but this gives me the same percentage for both true and false. So if I enter 5 I get 45.54% for both true and false... why is this? I've had trouble with this for ages now and I'm pulling my hair out, lol.
Thanks guys!

 

[mschwent]

I'm not sure if SQL evaluates BOOLEAN to a 1 for true and 0 for false or not, but if it does you could try the following:

100* [SumofRegularBooking] / [CountofRegularBooking]

and false would be =(1 - (the same thing))

If it doesn't do this automatically you might be able to do sum with cint(field). Let me know if it helps ya, I'm curious to see if it'll work myself.

 

[AuroX7]

I would be really chuffed if you could possibly look at it and see what it is. I've tried for ages and nothing's become of it. Thank you.
If you don't then I guess I'll have to battle on and find the solution...meh. If you do then the password for the database is: yojimbo17
The query is named "qryUsage"

Thank you...I'd appreciate it so so so so much if you could figure it out. I know I shouldn't be asking you to do it for me.

 

[mschwent]

Yeah, I'm a retard and apparently didn't read all of your post, I'll work on it and let you know if I come up with anything

 

[mschwent]

Okay, apparently I don't have permissions to upload files at work, so I'll copy and paste the modified query here for you:

SELECT DISTINCTROW [tblBooking].[* Regular Booking] AS [Booking Type], Sum([tblBooking].[* Fee Amount]) AS [Money Taken], Count([tblBooking].[* Regular Booking]) AS [# of Bookings], [tblBooking].[* Time], (select distinct -100*SUM([* Regular Booking])/Count([* Regular Booking]) from tblBooking) AS [Percentage Paid], (select distinct (100+(100*SUM([* Regular Booking])/Count([* Regular Booking]))) from tblBooking) AS [Percentage Unpaid]
FROM tblBooking
GROUP BY [tblBooking].[* Regular Booking], [tblBooking].[* Time]
ORDER BY [tblBooking].[* Regular Booking], Sum([tblBooking].[* Fee Amount]), Count([tblBooking].[* Regular Booking]), [tblBooking].[* Time];

There has got to be a more elegant way to do this, but I'm a hack and just keep trudging away til I get it with no thought to strategy. The blue part is an added nested query to calculate the true and false fields.

Keep posting this, maybe you'll get a better way eventually, but if you just need a solution, should work. Good luck.

 

[AuroX7]

Ooh, I see you've made it so it shows the percentage paid and unpaid which is cool. Although is there a way to make it so it calculates the percentage of the # of bookings? For example...

Regular Booking|Money Taken|# of bookings|Time|Percentage Usage|

T| £0.00| 2| A| 18.18%
T| £15.00| 2| E| 18.18%
T| £24.00| 2| M| 18.18%
F| £0.00| 1| M| 9.09%
F| £0.00| 2| A| 18.18%
F| £30.00| 2| E| 18.18%

Sorry for the wonky layout...it doesn't align it correctly on these messages. :\
The usage of the number of bookings, so because most of the times (M/A/E) have 2 bookings so that's 2 out of 11 which is 18.18% (re-occuring) then there's only 1 bookings for M which is 9.09%... that would be absolutely fantastic.
That would be the perfect setup. Thanks buddy, you're a real lifesaver.

 

[EMP]

Try this query:-

SELECT [* Regular Booking] AS [Booking Type], [* Time], Sum([* Fee Amount]) AS [Money Taken], Count([* Regular Booking]) AS [# of Bookings], Round(100*[# of Bookings]/DCount("*","tblBooking"),2) AS [Percentage %]
FROM tblBooking
GROUP BY [* Regular Booking], [* Time]
ORDER BY [* Regular Booking], Sum([* Fee Amount]), Count([* Regular Booking]), [* Time];


To show the column heading [Booking Type] in the query results, you may need to delete the Caption from the [* Regular Booking] field in table design.

^

 

[AuroX7]

IT WORKS! OMG OMG OMG! I appreciate this so much! Thank you to both of you guys for taking out time to find a solution to this problem. It might seem easy but I'm not overly great with Access, lol. You've both made me understand SQL more too which can only be a good thing! I can't thank you guys enough. I've been struggling with this one thing for ages.

Once again thanks guys.

 

[AuroX7]

I'm going to use that as my general Usage query, but I want to separte the usage data into both months. Since the data is based on January and February, how can I make it so it's divided into each month. I have three queries:
qryUsage, qryUsageJan, qryUsageFeb

The qryUsage, as you pointed out above, is the all the usage data for both months. Now how can you make it so I have all the usage data for just January in one query and the uage data for February in the other. So for January it would be between 01/01/2006 and 31/01/2006, and for Feb: 01/02/2006 - 28/02/2006

I tried: Between #01/01/2006# and #31/01/2006# but that gave me the same percentage for all of the bookings, which was the problem I kept having before.

 

[EMP]

For Jan:-
SELECT [* Regular Booking] AS [Booking Type], Sum([* Fee Amount]) AS [Money Taken], Count([* Regular Booking]) AS [# of Bookings], [* Time], Round(100*[# of Bookings]/DCount("*","tblBooking","[* Hirer Date] Between #1/1/2006# And #1/31/2006#"),2) AS [Percentage %]
FROM tblBooking
WHERE [* Hirer Date] Between #1/1/2006# And #1/31/2006#
GROUP BY [* Regular Booking], [* Time]
ORDER BY [* Regular Booking], Sum([* Fee Amount]), Count([* Regular Booking]), [* Time];


Since [* Hirer Date] is solely for record selection, it should appear in the Where clause only.

For Feb, you can use Between #2/1/2006# And #2/28/2006#
In SQL statements (as well as in VBA), the dates delimited by the # signs must be in the US date format, that is, m/d/yyyy.

^

 

[vgersghost]

Just a question on the meaning of " * "

[* Regular Booking] What does the * represent in the code?
thx

 

[EMP]

A good question. The * in the field names doesn't have any special meaning at all.

Though Access accepts * and space in field names, it is considered bad practice using them in field names.

We must put such field names in square brackets in SQL statements. Sometimes such field names may cause problems or give unexpected results when used in VBA.

^

 

[AuroX7]

Oh I just put them in some of my field names to indicate they're required fields. Sorry for any confusion. :/
I've managed to divide my monthly queries into 4 weekly slots. Is there any way of displaying the actual date when you look it up? I.e...
Week #1: Between 1/1/2006 and 1/7/2006
When I look it up it displays all the bookings within that week, but doesn't actually display the date of it. E.g.,

Booking:| Fee Amount:
Bob's Sewing Club| £12.00|

Any way of making it so the date of that particular booking is displayed too?

E.g.,

Booking:| Fee Amount|Date:
Bob's Sewing Club|£12.00|2/1/2006

Thank you.

 

[lydz]

im doing the same project
and thought the percentages were week by week..?