Counting multiple fields in a report (1 Viewer)

Duramax75

New member
Local time
Today, 03:52
Joined
Feb 7, 2019
Messages
3
Hello,

I cannot for the life of me figure out how to get a report I'm working on to count fields that contain specific text.

For example, I am trying to build a report that will count the number of grants within a county. Lets use "Mason" county as an example.

I have Fields 1-8 that capture "County", "County2", "County3" etc. all the way to "County8". Each county could have the capability of listing a single one of Michigan's 83 counties. A record could look like:
  • County - Monroe
  • County2 - Mason
  • Couunty3 - Saginaw

Then the next record could only have 1 county, that being Mason.

How can i create a field in a report that will look through all 8 "County" fields and count the number of instances that "Mason" is in there, or "Saginaw" or whatever the county I need may be.

ultimately, I need the report to list every county in Michigan, and show the number of associated grants with it.

I know, clear as mud. but help... please :)
 

plog

Banishment Pending
Local time
Today, 05:52
Joined
May 11, 2011
Messages
11,613
SQL was built for achieving this exact thing very simply....Unfortunately, you haven't set up your tables properly.

When you start numbering field names (County1, County2, etc.) its time to put that data in a new table. In a database data should be accomodated vertically (with more rows) and not horizontally (with more columns). So instead of 8 columns for that data, it should be in 8 rows of another table:

tblCounties
GrantID, County
1, Mason
1, Jackson
1, Washington
2, Jackson
2, Jefferson
3, Lincoln

Then, when you want to find out how many times each county is present, this is the SQL:

Code:
SELECT County, COUNT(County) AS CountyTotal FROM tblCounties GROUP BY County

You need to fix your tables.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:52
Joined
Jul 9, 2003
Messages
16,245
I have Fields 1-8 that capture "County", "County2", "County3" etc. all the way to "County8"


That's the problem. Extracting useful information from that type of arrangement is difficult.

You should have one field:- County with counties listed like this:-


County ---- Count This
County1 ------ data
County2 ------ data
County3 ------ data



Sent from my SM-G925F using Tapatalk
 

Duramax75

New member
Local time
Today, 03:52
Joined
Feb 7, 2019
Messages
3
SQL was built for achieving this exact thing very simply....Unfortunately, you haven't set up your tables properly.

When you start numbering field names (County1, County2, etc.) its time to put that data in a new table. In a database data should be accomodated vertically (with more rows) and not horizontally (with more columns). So instead of 8 columns for that data, it should be in 8 rows of another table:

tblCounties
GrantID, County
1, Mason
1, Jackson
1, Washington
2, Jackson
2, Jefferson
3, Lincoln

Then, when you want to find out how many times each county is present, this is the SQL:

Code:
SELECT County, COUNT(County) AS CountyTotal FROM tblCounties GROUP BY County

You need to fix your tables.

I have a Counties table that lists each County, and associates a DatabaseID with each county. the problem I'm running into is how to link multiple counties to a single grant record, then be able to report on that.

I'm confused on how to capture that data efficiently...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:52
Joined
Jul 9, 2003
Messages
16,245
I'm confused on how to capture that data efficiently...

Yes it's very confusing because you are thinking in terms of Excel instead of MS Access. I've done several blogs on this very subject, however I get the impression that you might respond better to an example of a similar problem being solved. PeggyC09 had a similar problem to yours, See this thread:- https://www.access-programmers.co.uk/forums/showthread.php?t=296463 I demonstrated how this problem could be solved in a YouTube video, and I posted a blog about it on my website here:- http://www.niftyaccess.com/normalization-tool/
 

plog

Banishment Pending
Local time
Today, 05:52
Joined
May 11, 2011
Messages
11,613
I'm running into is how to link multiple counties to a single grant record, then be able to report on that.

My initial post detailed that exact thing. Notice the name of the first column in my tblCounties.
 

Duramax75

New member
Local time
Today, 03:52
Joined
Feb 7, 2019
Messages
3
My initial post detailed that exact thing. Notice the name of the first column in my tblCounties.

Yeah, I got that. And created a table, tblCounties, just as you suggested, however, in my form, how can I build it to where a user can enter up to 8 counties that will be tied to that GrantID?
 

June7

AWF VIP
Local time
Today, 02:52
Joined
Mar 9, 2014
Messages
5,425
This is a many-to-many relationship and needs 3 tables.

tblCounties
has a unique record for each county

tblGrants
has a unique record for each grant

tblGrantCounties
multiple records associating grants with counties
GrantID_FK
CountyID_FK
Amount

Main form bound to tblGrants, subform bound to tblGrantCounties with a combobox using tblCounties as RowSource to select county.
 

Users who are viewing this thread

Top Bottom