Query Help

[Confused]

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[SuzyQ]

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

 

[SuzyQ]

Typo... Query 6 - Union of queries 3, 4, & 5

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[Confused]

I was really hoping for something along the lines of a subquery if you or
anyone knows the SQL for it?

Typo... Query 6 - Union of queries 3, 4, & 5

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[John Spencer]

You want
== records where System ID is 6 or 8
== records where system id is 2,3, or 4 if Customer does not have system id of 6
== records where system id is 1 if Customer does not have system id of 8

I think this may work. However, it could be slow if you have a large amount
of records.


SELECT *
FROM SomeTable
WHERE [System ID] in (6,8)

OR (CustomerID NOT In
(SELECT CustomerID
FROM SomeTable
WHERE [System ID] = 6)
AND [System ID] IN (2,3,4))

OR (CustomerID NOT IN
(SELECT CustomerID
FROM SomeTable
Where [System Id] = 8)
AND [System ID] = 1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I was really hoping for something along the lines of a subquery if you or
anyone knows the SQL for it?

Typo... Query 6 - Union of queries 3, 4, & 5

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.


:

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[Confused]

Suzy,

How do you mean set up query 1 and 2? Only customers that have 6 and 8.
If so Query 3 would already exclude these system IDs.

Thnanks.

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[Confused]

John,

Updated-You have listed what I want. I ended up with this SQL starting at
the Where Clause, but it still shows all of the records that have 6. The
only thing it seems to do is exclude other systems that I didn't want anyway,
such as 11, 12, and 5. Do you know how I should modify?


WHERE ((([CLECS2].CLECID) In (Select CLECID

FROM [CLEC Systems3]
WHERE [CLEC Systems3].[System ID] in (6,7)))) OR ((([CLEC Systems3].[System
ID]) In (2,3,4)) AND (([CLEC Systems3].CLECID) Not In (SELECT CLECID
FROM [CLEC Systems3]
WHERE [CLEC systems3].[System ID] = 6))) OR ((([CLEC Systems3].[System
ID])=1) AND (([CLEC Systems3].[CLECID]) Not In (SELECT [CLEC Systems3].CLECID
FROM [CLEC Systems3]
Where[CLEC Systems3].[System Id] = 7)));

You want
== records where System ID is 6 or 7
== records where system id is 2,3, or 4 if Customer does not have system id of 6
== records where system id is 1 if Customer does not have system id of 7

I think this may work. However, it could be slow if you have a large amount
of records.


SELECT *
FROM SomeTable
WHERE [System ID] in (6,8)

OR (CustomerID NOT In
(SELECT CustomerID
FROM SomeTable
WHERE [System ID] = 6)
AND [System ID] IN (2,3,4))

OR (CustomerID NOT IN
(SELECT CustomerID
FROM SomeTable
Where [System Id] = 8)
AND [System ID] = 1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I was really hoping for something along the lines of a subquery if you or
anyone knows the SQL for it?

Typo... Query 6 - Union of queries 3, 4, & 5

:

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.


:

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[Confused]

OK I finally got to Query6. But how do I "union" queries 3, 4, and 5? If
I join them on CustomerID, I get no results.

Suzy,

How do you mean set up query 1 and 2? Only customers that have 6 and 8.
If so Query 3 would already exclude these system IDs.

Thnanks.

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[John W. Vinson]

OK I finally got to Query6. But how do I "union" queries 3, 4, and 5? If
I join them on CustomerID, I get no results.

Press the F1 key.
Search for UNION in the Help.

It's pretty clear; if you have trouble with it post back.

 

[John Spencer]

Ok, so now where did CLECS2 come from? I would expect to see

WHERE [CLECS System3].CLECID in (6,7)

OR ([CLEC Systems3].[System ID] In (2,3,4)
AND [CLEC Systems3].CLECID
Not In (SELECT CLECID
FROM [CLEC Systems3]
WHERE [CLEC systems3].[System ID] = 6))

OR ([CLEC Systems3].[System ID]=1
AND [CLEC Systems3].[CLECID]
Not In (SELECT [CLEC Systems3].CLECID
FROM [CLEC Systems3]
Where[CLEC Systems3].[System Id] = 7))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

Updated-You have listed what I want. I ended up with this SQL starting at
the Where Clause, but it still shows all of the records that have 6. The
only thing it seems to do is exclude other systems that I didn't want anyway,
such as 11, 12, and 5. Do you know how I should modify?


WHERE ((([CLECS2].CLECID) In (Select CLECID

FROM [CLEC Systems3]
WHERE [CLEC Systems3].[System ID] in (6,7)))) OR ((([CLEC Systems3].[System
ID]) In (2,3,4)) AND (([CLEC Systems3].CLECID) Not In (SELECT CLECID
FROM [CLEC Systems3]
WHERE [CLEC systems3].[System ID] = 6))) OR ((([CLEC Systems3].[System
ID])=1) AND (([CLEC Systems3].[CLECID]) Not In (SELECT [CLEC Systems3].CLECID
FROM [CLEC Systems3]
Where[CLEC Systems3].[System Id] = 7)));

You want
== records where System ID is 6 or 7
== records where system id is 2,3, or 4 if Customer does not have system id of 6
== records where system id is 1 if Customer does not have system id of 7

I think this may work. However, it could be slow if you have a large amount
of records.


SELECT *
FROM SomeTable
WHERE [System ID] in (6,8)

OR (CustomerID NOT In
(SELECT CustomerID
FROM SomeTable
WHERE [System ID] = 6)
AND [System ID] IN (2,3,4))

OR (CustomerID NOT IN
(SELECT CustomerID
FROM SomeTable
Where [System Id] = 8)
AND [System ID] = 1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I was really hoping for something along the lines of a subquery if you or
anyone knows the SQL for it?

:

Typo... Query 6 - Union of queries 3, 4, & 5

:

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.


:

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks

 

[SuzyQ]

select query3.*
from query3 union select query4.*
from query4 union select query5.*
from query5

The caviat is that each query must have the same number of fields and in the
same order.

Suzy,

How do you mean set up query 1 and 2? Only customers that have 6 and 8.
If so Query 3 would already exclude these system IDs.

Thnanks.

You might try creating a query of all the customers that have system ID 6;
create another query for all customers that have system ID 8. Then use those
queries to limit the choices in two other queries and then another which
excludes everthing from previous queries. Then union the last three queries
together.

IE
Query 1 - customers who have system ID 6
Query 2 - customers who have system ID 8
Query 3 - system IDs for customers who are in query 1 excluding system IDs
2, 3, & 4
Query 4 - system IDs for customers who are in query 2 excluding system IDs 1
Query 5 - system IDs customers who are not in queries 1 or 2 not excluding
any system IDs
Query 6 - Union of queries 4, 5, & 6

Query 6 will have the results you seek.

Of course there may be a simpler method and there are others who post that
have much more experience than I but this is how I would probably go about
this problem.

I have a query that has CustomerID and [System ID] etc. The [System ID]
field has systems 1, 2, 3, 4, 6, and 8. How do I make it exclude records
for systems 2, 3, 4, only on customers that have system 6? At the same
time still show all of the records for system 1 unless that custsomer also
has system 8?

Is this even possible? Thanks