Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Does Not Have 1

Status
Not open for further replies.

doquigley

Technical User
Jan 16, 2006
12
IE
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
 
Perhaps:

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

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

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

Duane
Hook'D on Access
MS Access MVP
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top