Query Expression

PeteB

Registered User.
Local time
Today, 05:34
Joined
Mar 15, 2016
Messages
78
Payment of club subscriptions can be in the form of different 'Payment Types'. I need to create a query upon which to base a pie chart which illustrates the different payment types as a percentage of the whole membership but am struggling with the expression which returns a count of the different payment types. 'Payment Types' are held in a two-column table (where the fields are 'Payment Type' and 'Payment Type ID') which feeds a drop-down list comprising 'Payment Types' within a 'Payments' table. Relevant data are stored as 'Payment Type ID' within the 'Payments' table which also holds a 'Member ID' field to link members with payment types. Can anyone come up with an expression to count the number of different payment types? I would really appreciate the help.
 
Try doing this:

  1. On the CREATE Tab click Query Design
  2. On the Show Table dialog Add both Tables Payment Types and Payments Table
  3. Join the two table (if not already) by dragging the primary key of the Payments Types to the Payments Table
  4. Double click on the Payment Type Field and Member ID field to add them
  5. In the ribbon click on the sigma icon (Totals)
  6. In the Grid below change the Total for the Member ID from Group by to Count
  7. To change the name of the count change the field name to Desire Name: Field Name. Example: Type Count: Member ID
  8. Close and save the query

Also check out https://support.office.com/en-us/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8
 
Thanks Sneuberg,
That sort of works. There are 7 Payment Types of which only 3 currently have data entered in the Payment Type field. Your solution correctly returns records where the PT field currently holds data but ignores the majority of records where the PT field holds no data. Even if I add an an 'Undefined' pt to the pts table and an expression to the query so that it returns 'Undefined' where the pt field contains no data, the query is still ignoring records where the pt field contains no data. Currently 100 odd records are returned when it should be 800 odd. Any further ideas on how I can tweak this to return all of the records? As I am an Access novice, assistance would be much appreciated.
 
I think you might be able to fix this with a minor alteration of your query. You need to change the join type to what's called an outer join. Open the query and double click on the join line. You should see a Join Properties dialog that gives you three options. Choose the one that includes all records from the Payment Type table. Also if your query is counting the MemberID that won't work now as they will be Null for these additional records. Change the view to SQL View. You can right click on the title or click the down arrow in View in the ribbon to see the option for the SQL view. In the SQL View where it has Count(MemberID) change it to Count(*). The asterisk will make sure all records are counted

See https://support.office.com/en-us/ar...-a-query-09c75798-8cc7-4877-a8a3-b39572400c19 for more info on the join.
 
Last edited:
I reread you post and now I'm not sure it my proposed fix will work for you. I don't understand how you could have records in the payments table that don't have related records in the payments types table. If you upload your database I can figure this out and give you a solution.
 
make an aggregate query (Total query).
Code:
Field:    Payment Types    | Payment Type    | TotalType: DCount("*", "Payments")
Total:    Group By    | Count        | Expression
 
Hello Sneuberg,
You are correct, I tried your fix and although the three remaining Payment Types showed up in datasheet view of the query, they all had the same 'CountOf MemberID' of 1, which I did not understand, and when I attempt to produce a pie chart based on the query, Access presents a parameter box requesting Payment Type and the chart area opens up but with no chart.
The reason that not all payments have a payment type allocated is that I am in the process of taking over the role of Membership Secretary and my predecessor kept the membership data in an Excel spreadsheet which I have transferred into Access. Not all of the inherited records have payment type data recorded and it may not be until I have gone through a complete cycle of subscription renewals that I can add the missing data - it could be the end of November before all of this data materialises.
I am not in a position to send the database for reasons of British data protection regulations/confidentiality.
I hope the above is an adequate explanation. I am very grateful for your help so far and hopeful that you may yet be able to crack the problem but if not, at least you have tried.
PeteB.
 
I am not in a position to send the database for reasons of British data protection regulations/confidentiality.
What if you made a copy, deleted all of the objects except for the ones relevant to this problem and them deleted the data in them. Note that would have to start with the relationships, but then you can scroll down through the objects holding the shift key to select them all and then deselect the ones to keep with Ctrl Click to deselect, then press the delete key. It doesn't take much time to strip down a database. Compact and Repair would help reduce the size.

I think I could give you a query or some code that would update the payments types table that you could use temporarily to get it up to date.
 
I will give it a try.

Hello Sneuberg,
Stripped down database attached as JF4.4n. Hope you can do something with it.
PeteB
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom