MS Access question - criteria filtering

Factual Questions
1

Normally I’m pretty proficient in Access and can usually logic my way out of any scenario. This time, whether because it’s actually hard or because my head is pounding, I can’t figure out how to write this in a query.

I have a table of transactions with trans numbers, trans types, and account numbers. One account may have several transactions. I want to see just the accounts that DON’T have a particular trans type. I don’t want to just see all transactions except that trans type, I don’t want to see those accounts at all. Alternately, I also want to see the accounts that do include the trans type, but only those that include this trans type along with others.

Specifically, I want to make one report that shows customers and their transactions that are in a credit balance who don’t have any invoices in with their credits. I want a second report that shows me the customers and their transactions where the customer has a mix of credits and debits. I can put crieria in to filter in or out the transactions, but I need the filter not at the transaction level, but at the customer level.

Any Access gurus out there?

StG

2

I don’t have access (ha) to a copy of Access at the moment, but can’t you use the WHERE NOT EXISTS SQL clause to filter out the ones you don’t want?

Something along the lines of



SELECT id FROM Customers AS c1 WHERE NOT EXISTS (SELECT id FROM Customers AS C2 WHERE transactionType = 'NotWanted' AND C1.id = C2.Id)

Without knowing your table structure, of course, it’s hard to tell exactly how it would look, but I think you should be able to get it this way.

3

Dervorin - I’m not quite getting it, but I’ll post a sample of the table when I get to work. Thanks for your help!

StG

4

Here’s an example of the table:



Acct #	Cust Name   Reference	Trans Type  Trans Num	Balance
2000	Cust A	73333475	DI	    49308	                 786
3000	Cust B	040411LY4M3222	DI	    49309	               1834
2000	Cust A	6723659	               CM	    600345               -998
2000	Cust A	10007	               CM	    600132               -1084.18
2007	Cust A	8199927	                CM               600044               -165.15
3000	Cust B	JRL C/B TO SO916    CM               60094                 -291.7
5

There are two ways to do this: either using an SQL query in the criteria field, or by making a separate query to pull the list of accounts, then joining that query to the the base tables to build your final output query. That second way makes for a more complicated structure, but it’s probably easier to wrap your head around.

Eg. write a query grouping by account number, linking to the transactions table, and filtering on the transaction type you want to exclude. This gives you a list of accounts that have that transaction type. Then, in your report query, add this query as a data source, and link it with an outer join to the main accounts table. (I.e. double-click on the join line, and select e.g. ‘Show all records from accounts, and only records from excludequery where the joined fields are equal’.) Then, put an ‘is not null’ filter on the join field from excludequery; now, your main query will only show the records for accounts that don’t show up in the query you wrote.

6

Unfortunately, that’s not a very good sample, to be honest, because it only contains two customers, both of which have two transaction types (I’m assuming you want to filter on Trans Type). I’ll make up a few more rows to demonstrate what I mean.

Also, does the table have a primary key that is not listed in your sample above? That would make things easier and faster, but is not essential.

There are several ways in which you could write this query. Assuming this sample data set, with the table called Book1 (because I imported from Excel and was lazy):



ID	Acct #	Cust Name	Reference	Trans Type	Trans Num	Balance
1	2000	Cust A	73333475	DI	49308	786
2	3000	Cust B	040411LY4M3222	DI	49309	1834
3	2000	Cust A	6723659	CM	600345	-998
4	2000	Cust A	10007	CM	600132	-1084.18
5	2007	Cust A	8199927	CM	600044	-165.15
6	3000	Cust B	JRL C/B TO SO916	CM	60094	-291.7
7	1000	Cust C	Ref 1	TL	18882	827
8	1001	Cust C	Bootle	CM	8282	-938.82
9	5000	Cust D	Bumtrinket	CM	7272	625.72
10	5000	Cust D	Lemonade	DI	1662726	756.23

This was my first approach:



SELECT *
FROM Book1
WHERE [Cust Name] 
NOT IN 
(SELECT DISTINCT [Cust Name]
 FROM Book1 
WHERE [Trans Type] = 'DI');

This filters out all customers who have a transaction of type DI by excluding them from the set. Based on your data volumes, this may be sufficient, especially if the table is properly indexed, but it’s fairly inelegant.

Result:



ID	Acct #	Cust Name	Reference	Trans Type	Trans Num	Balance
7	1000	Cust C	Ref 1	TL	18882	827
8	1001	Cust C	Bootle	CM	8282	-938.82

You can also write the query as



SELECT *
FROM Book1 b1
WHERE NOT EXISTS (
SELECT DISTINCT [Cust Name] FROM Book1 b2 
WHERE [Trans Type] = 'DI' AND b1.[Cust Name] = b2.[Cust Name]);

Which is more elegant and may give you better performance. Some experimentation may be necessary, but I hope this has given you a pointer of the direction in which to go. Feel free to give me a shout if you need more clarification. I find that with complex queries it’s easier to write them in SQL View rather than using the graphical query designer, so you can specific exactly what you want.