Percentages

[Spruk_77]

Hey all,

I need a bit of help with percentages.

I have a table that the basic set up is as such:
Name
Brief Date (Date format)
Brief Date Expires (Date Format)
PCS (yes/no format)

I have a query that runs all people that are have not passed the experation date. The criteria in the date expires field looks like >=Now() and the critera for the PCS is NO.

I would like a percent of all the people whose Brief date fits the format of the date expires field but this percentage needs to be based on only people whose PCS status is NO. I have no idea how I need to do this. I tried the help and it was none. I did a search but was unable to find a post enough like mine to help. Thanks for any help, I hope I have not confused any of you if so I will try to clear it up if ya ask.

 

[KenHigg]

Sorry - This makes no sense:

Brief date fits the format of the date expires field

Could you clarify,
kh

 

[ecniv]

To get a percentage, you need to get a total.
In this case you are going to need to get all those on the date(after date) none expired or whatever and not filter on the pcs. But you will need to count the pcs. Twice.

Once to get the total.
Once to get the pcs=no.

Probably will need a sub query or two queries.

Post up the sql once you have some.

 

[Spruk_77]

Ok here is a basic sample.

I only want the percentage to run on the people who have a no in the PCS field. If you have a Yes in the PCS field I pretty much want the query to ignore you. I hope this helps. Thanks

 

[KenHigg]

Dim intTotal As Integer
Dim intCount As Integer

intTotal = DCount("[lastname]", "PersonalInfo")
intCount = DCount("[lastname]", "PersonalInfo", "[PCSETS] = -1")

MsgBox Format(intCount / intTotal, "percent")

???
kh

 

[Spruk_77]

I have not wrote any SQL. I just go into design mode and write in the critera. Whatever SQL is written is by access. If this request is above my level that is fine. This is something that would have been nice but not required.

 

[KenHigg]

SQL?

kh <= confused

 

[Spruk_77]

Ken,

Sorry I am confusing so much today. As far as the code you posted I am afraid that is above my head. I am not even sure where I would add that.

 

[KenHigg]

That's cool. Where do you need to see the percent value? In a form text box or on a report?

kh

 

[Spruk_77]

I would want to add this into the footer of a report.

 

[KenHigg]

Try putting the following code in the control source property and type 'Percent' in the format property:

= DCount("[lastname]", "PersonalInfo", "[PCSETS] = -1")/DCount("[lastname]", "PersonalInfo")

kh

 

[Spruk_77]

That was it. Thanks and YOU ROCK.

 

[KenHigg]

Cool...

(Never Rocked before! )

kh