Need help with query to get totals as records (1 Viewer)

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
Good morning. I have a table that lists 30 bills that are paid monthly, and separate checkbox fields for husband and wife. As a bill gets paid, I check the box for who paid. I need a query that will produce a list of how many bills the husband paid, how many the wife paid, and how many are outstanding, like this:

Bill Payor | Total
Husband | 4
Wife | 6
Remaining | 20

I know I can just use total fields and get it across one record, but I need it specifically in that format.

I have been knocking my head into a wall for a few days on this...
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,582
Ideally you shouldn't have Husband and Wife fields. Instead you would have a [BillPayor] field that defaults to "Remaining" and then you can change to either the value "Husband" or "Wife". When you do that, this query becomes a simple aggregate query:

https://support.office.com/en-us/article/sum-data-by-using-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

With your set up you could double count paid bills--if you check both the Wife and the Husband fields. The way to prevent it is to not allow it to be a possibility in the first place.
 

The Rev

Registered User
Joined
Jan 15, 2003
Messages
48
Ideally you shouldn't have Husband and Wife fields. Instead you would have a [BillPayor] field that defaults to "Remaining" and then you can change to either the value "Husband" or "Wife". When you do that, this query becomes a simple aggregate query:

https://support.office.com/en-us/article/sum-data-by-using-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

With your set up you could double count paid bills--if you check both the Wife and the Husband fields. The way to prevent it is to not allow it to be a possibility in the first place.
I appreciate your response. Ideally, you are correct. But this is just a small subset of the overall database, and I have coded and queried based off those checkbox values, so I am stuck with either a full redesign or getting this, what honestly should be simple, thing to work.

I have logic on the form that will not allow both to be selected, and some additional fields that only become active when one is selected. Again, too much work to redesign.
 

MickJav

AWF VIP
Joined
Nov 28, 2005
Messages
1,278
The bills got to be paid by one of you so you shouldn't have any remaining.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,582
Make a new query and have [BillPayor] be a calculated field using IIf statements to classify each record into the 3 groups (husband/wife/remaining). Then it's a simple aggregate query where you GROUP BY [BillPayor]
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom