INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query Does Not Have

Query Does Not Have

(OP)
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

RE: Query Does Not Have

Perhaps:

CODE

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

Randy

RE: Query Does Not Have

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

CODE --> SQL

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

Duane
Hook'D on Access
MS Access MVP

RE: Query Does Not Have

(OP)
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

RE: Query Does Not Have

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

RE: Query Does Not Have

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close