Query Does Not Have 1

[doquigley]

Hi

I have a table that contains different transaction types for customers and I am looking for a list of customers that does not have a particular transaction type. What I have and it works, is one query that gives me all the customers from that table that have that transaction type and then I have another query that uses that first query with a right join and an Is Null criteria on the linked field to give me those customers that don't have that transaction type. Question : Is there a way of combining these two queries into one sql statement? The two queries are as follows

first query, called CustomersInvoiced:
SELECT CustomersActivity.[cl::code], CustomersActivity.[sbt::typ]
FROM CustomersActivity
WHERE (((CustomersActivity.[sbt::typ])=1));

second query:

SELECT CustomersActivity.[cl::code]
FROM CustomersInvoiced RIGHT JOIN CustomersActivity ON CustomersInvoiced.[cl::code] = CustomersActivity.[cl::code]
WHERE (((CustomersInvoiced.[cl::code]) Is Null))
GROUP BY CustomersActivity.[cl::code];

Any help greatly appreciated

 

[randy700]

Perhaps:

WHERE (((CustomersActivity.[sbt::typ])[red][b]<>[/b][/red]1));

Randy

 

[dhookom]

I don't know what your fields are storing. I think you could create one query with SQL like

SELECT *
FROM tblCustomers 
WHERE CustomerID NOT IN (SELECT CustomerID from CustomersActivity WHERE TransactionType = 123);

Duane
Hook'D on Access
MS Access MVP

 

[doquigley]

Thanks Duane
NOT IN was what I was looking for although something to note was that the sub query as presented in access just choked, the tables are linked tables to a SQL database across a network but I just ran the suggested query on the SQL Server side and that worked a charm (although not really for this forum) but using the access front end I may have to stick with the original 2 separate queries for the sake of expediency unless there is something else I am missing

 

[dhookom]

Since you originally had a group by clause I assume you don't need to update the results and are satisfied with read-only. If this is the case, consider using the power of the pass-through. Pass-through queries use the server SQL syntax and are blinding fast compared with similar queries in Access. You can also use the advanced functionality of the server brand of SQL which often has some really cool stuff.

Duane
Hook'D on Access
MS Access MVP

 

[doquigley]

Thanks again Duane, I have started to convert my major data churning read only queries into pass-through queries, all the time that I will save now not having to watch spinning discs I will use to push my boundaries that bit further, thanks again