Probably an easy one: Count a subset and compare to whole set.

[Toxeia]

Okay, I know this has got to be easy. I'm just in a sleep deprived state and I've hit a wall after designing reports all morning long and huffing insecticide. I've tried searching for what I'm missing, but I'm even having trouble explaining what it is I want, let alone coming up with the keywords for a good search.

So I have X patients in the database, and I want to compare that to patients in each county. Patient table has a field that notes which school the patient goes to (foreign key). That school is in a county, and that's how we determine the county they are in.

Query pulls Schools.SchoolCounty, Patients.PatientID, Patients.FirstName, Patients.LastName. It could be that the only things I need in the query are the county and the PatientID, since that's really what I'm counting.

In the report, I can do Count(*). If I group by Schools.SchoolCounty and put it in the header for the county I get just the count of students in that county. If I put it in details I get the count for total patients. I can't think of how to make these interact so I can do the simple math needed. Is it as simple as like.. Count([SchoolCounty="CountyName"])?

Edit: Worded it a tad better. Rather than saying comparing to just one county I should have said compare the total to each county

 

[sneuberg]

How are patients and students related? And what do you want? The count of patients in each county? It might help if you posted a screen shot of the database relationships.

 

[pbaldy]

In the report footer:

=Sum(IIf(SchoolCounty="CountyName", 1, 0))

 

[Toxeia]

Sure, here's a pic of the relations in that query.

 

[Toxeia]

In the report footer:

=Sum(IIf(SchoolCounty="CountyName", 1, 0))

Would that only work for one county? Ideally I'd like to have all 4 counties and their percentages of total patients in a single report/page.

Edit: Looking at my original post I see I said "just one county." My bad. I'll edit the OP and try to word that better.

 

[pbaldy]

If you're familiar with a totals query, I'd create a report based on one. It would be dynamic, though counties don't change much.

 

[Toxeia]

If you're familiar with a totals query, I'd create a report based on one. It would be dynamic, though counties don't change much.

Not really. I think you've got me on the right track though. Crosstab query maybe? This is not elegant at all, and I suspect this is entirely the wrong way to do this.

So I created crosstab query. On the left I have the counties, then the heading is PatientID. It's giving the right number in the total column, but it's followed by an entry for every patient in the database. That's going to explode real good when there's a real dataset in here.

 

[pbaldy]

I wasn't thinking crosstab, just totals (your picture shows a totals query). At the simplest:

SELECT County, Count(*) As HowMany
FROM TableName
GROUP BY County

 

[Toxeia]

I wasn't thinking crosstab, just totals (your picture shows a totals query). At the simplest:

SELECT County, Count(*) As HowMany
FROM TableName
GROUP BY County

I'm really sorry, I'm just not following you. I tried putting that into the SQL view but I'm not understanding the output.

I did... this (photo attached) and now I get a count of the number of patients per county. I think I can use that with =Count(*) and do the math, right?\

Edit: Alright, Count(*) is just counting the number of counties that are populated (I think). I feel like I'm almost there.

Edit 2: That was it, it clicked. Thank you for leading me there pbaldy. So I have [CountOfPatientID] in each county, and I use =Sum([CountOfPatientID]) to get the total across all counties. Then I can use an expression to get the percentage. All I have left to do is get it to show as an actual percentage, which I believe is in the format somewhere.

Edit 3: Yeah, all done. Formatted the math as a percentage and that's it. Where do I sign up for my dunce cap? Cannot believe how much I had to struggle to get a simple expression done. Thanks all for the help!

 

[pbaldy]

Happy to help!