How to use GROUP BY in a form flter

[poyraz1108]

I have developed an accounting database which includes, among others, tables for Transactions and Creditors. I want to query the data for a particular time period to find out how much I have paid to each creditor. At the moment I can do this by having a Main Form based on Creditors, and a subform based on Transactions that contains a filter to display only those records that fit into the selected time period.

Now, there are some creditors to whom I have paid nothing during that particular period, so the subform shows no records. That’s fine, but as I have several hundred creditors, it’s tedious to pan through each creditor to see if I have paid anything.

What I want is for the subform to display records from ONLY those creditors to whom I have paid something. I can view these records with a select query by using the GROUP BY function. However it seems that a form’s filter property will not accept a WHERE clause that includes a GROUP BY clause.

Does anybody know how I can program this in VBA, i.e. to mimic a select query’s GROUP BY function for use as a form filter?

 

[jdraw]

Have you tried Google? Group By works with aggregate queries.

You can find all sorts of examples of commands and syntax at
http://www.techonthenet.com/access/functions/ and for sql statements and samples try
http://www.w3schools.com/sql/sql_intro.asp


In general for form/subform, you have tables that are related in a 1 to many relationship. eg 1 creditor has many transactions

The linkfields would be CreditorId and you would "constrain" the transactions by CreditorId and TimePeriod.

There's a sample video at
https://www.youtube.com/watch?v=YZKN_-P6wck