Need help ignoring or skipping certain records 2

[Trogg]

Good day all,

I have a database of accounts. Each account has a unique number. Multiple transactions are stored for each account and the transactions have unique codes to identify them. So if I query an account I may see 10 records for that one account showing each transaction. What I need to do is skip or ignore an entire account if it has a certain transaction code instead of just ignoring one record. Thanks in advance for your help!

 

[dhookom]

Create a query of accounts and transactions that groups by account and has a criteria of the transaction code. Add this query to your report's record source query with a join that includes all records from the original query. Set a criteria under the new query's account to Is Null.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Duane... if I set the criteria of the account in the new query to IS Null I get no records returned.. did you mean to say set the criteria of the transaction to Is Null? This seems to work in initial testing.

 

[dhookom]

The criteria would be on the account field of the transactions totals query.

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Hi Duane... still not working... let me see if I can illustrate it better. For the sake of simplicity I'll limit it to the fields in question. The first query produces the following results:

Account # Trxcode
11111 stk
11111 char
11111 stor
11111 bak
12345 bak
12345 stk
12345 stor
12345 sav

Per your suggestion I created a second query to pull the accounts with the Trxcode I need to use to eliminate the account... for examples sake let's say it is "char". Using the data above this query returns this:

Account # Trxcode
11111 char

Per your instructions I create a third query from the first two. The join is on the Account # field and I changed the join to include all records from the first query and those that match from the second. The results I need would eliminate the Accounts that have a "char" record in them and return all others as follows using the example data:

Account # Trxcode
12345 bak
12345 stk
12345 stor
12345 sav

Putting "Is Null" as the criteria in the Account # field of any of the queries results in no records being returned at all because the account field is never null as I see it. I am probably doing something incorrect. Thanks for your time and patience. Hope this helps explain a little better...sorry for being a little slow here.

 

[dhookom]

I wish you would have shared your SQL view so we could more easily trouble-shoot. This is the SQL that worked for me:

SELECT qFirstQuery.AccountNum, qFirstQuery.Trxcode
FROM qSecondQuery RIGHT JOIN qFirstQuery ON qSecondQuery.AccountNum = qFirstQuery.AccountNum
WHERE qSecondQuery.AccountNum Is Null;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try something like this:

SELECT *
FROM [first query]
WHERE [Account #] Not In (SELECT [Account #] FROM [second query])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Trogg]

Hi Duane and PH,

Using the examples you both posted I think I was able to get it working. I tested both ways and got the same results. The only thing Duane different from yours is I had to use a left join not right join. Yes I should have posted SQL... so here it is now. Let me know if you see there is anything else I need to do.

Query 1

SELECT Accounts.AccountNumber, Adjustments.TxnProcedure, Accounts.DueDate, Accounts.Facility, Accounts.AcctClass, Sum(Charges.TxnAmount) AS SumOfTxnAmount
FROM (Accounts INNER JOIN Charges ON Accounts.UrnID = Charges.UrnID) LEFT JOIN Adjustments ON Accounts.UrnID = Adjustments.UrnID
GROUP BY Accounts.AccountNumber, Adjustments.TxnProcedure, Accounts.DueDate, Accounts.Facility, Accounts.AcctClass
HAVING (((Accounts.DueDate) Between #10/1/2004# And #9/30/2005#) AND ((Accounts.Facility)<>"emc") AND ((Accounts.AcctClass) Like "sp*"));

Query 2 (same as above except criteria added for TxnProcedure)

SELECT Accounts.AccountNumber, Adjustments.TxnProcedure, Accounts.DueDate, Accounts.Facility, Accounts.AcctClass, Sum(Charges.TxnAmount) AS SumOfTxnAmount
FROM (Accounts INNER JOIN Charges ON Accounts.UrnID = Charges.UrnID) LEFT JOIN Adjustments ON Accounts.UrnID = Adjustments.UrnID
GROUP BY Accounts.AccountNumber, Adjustments.TxnProcedure, Accounts.DueDate, Accounts.Facility, Accounts.AcctClass
HAVING (((Adjustments.TxnProcedure)="char") AND ((Accounts.DueDate) Between #10/1/2004# And #9/30/2005#) AND ((Accounts.Facility)<>"emc") AND ((Accounts.AcctClass) Like "sp*"));

Query 3

SELECT DISTINCT QAccts1.AccountNumber, QAccts1.TxnProcedure, QAccts1.DueDate, QAccts1.Facility, QAccts1.SumOfTxnAmount
FROM QAccts1 LEFT JOIN QAccts2 ON QAccts1.AccountNumber = QAccts2.AccountNumber
WHERE (((QAccts2.AccountNumber) Is Null))
ORDER BY QAccts1.AccountNumber;

Query 3 is Duanes SQL except left join instead of right join. PH your SQL works too as is I think.

 

[dhookom]

I think query 2 doesn't need all the extra output fields since you are only interested in the matching accountnumber.

SELECT Accounts.AccountNumber
FROM (Accounts INNER JOIN Charges ON Accounts.UrnID = Charges.UrnID) LEFT JOIN Adjustments ON Accounts.UrnID = Adjustments.UrnID
WHERE Adjustments.TxnProcedure="char" AND Accounts.DueDate Between #10/1/2004# And #9/30/2005# AND Accounts.Facility <> "emc" AND Accounts.AcctClass Like "sp*"
GROUP BY Accounts.AccountNumber;

Duane
Hook'D on Access
MS Access MVP

 

[Trogg]

Many thanks to both of you for getting me pointed in the right direction and your spot on solutions! Stars to you both!