 | |
Excluding subscription types with a self join?
comp.database.ms-access comp.database.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Excluding subscription types with a self join? -
07-07-2004
, 09:25 AM
#2
| |||
| |||
|
|
Table accountNo PK subType subType domain is (a,b,c,d,e). Customers can have a subscription of any combination of types. I want to find all those that only have a subscription of type 'a'. It is possible that some people have a subscription to types a&b, a&c, a&d, a&e, b&c etc. I am using the following query: SELECT t1.subType, t1.accountNo FROM <table> as t1 INNER JOIN <table> as t2 ON t1.accountNo = t2.accountNo WHERE t1.subType = 'a' AND t2.subType Not In ("b","c","d","e"); However this doesn't give me what I need. Thank you for your help. Best wishes, Simon. |
I have a question. Are the letter combinations in one field? If so,
put this into the criteria of your field:
[Enter Letter Combination]
This will allow you to type the letter a and only get the records
that have it in the field. If you type ab you'll get those records, etc.
Regards,
Ray
#3
| |||
| |||
|
|
thisisjustaspamdump (AT) hotmail (DOT) com (Simon Long) wrote in message news:<40118e41.0407070625.12384808 (AT) posting (DOT) google.com>... Table accountNo PK subType subType domain is (a,b,c,d,e). Customers can have a subscription of any combination of types. I want to find all those that only have a subscription of type 'a'. It is possible that some people have a subscription to types a&b, a&c, a&d, a&e, b&c etc. I am using the following query: SELECT t1.subType, t1.accountNo FROM <table> as t1 INNER JOIN <table> as t2 ON t1.accountNo = t2.accountNo WHERE t1.subType = 'a' AND t2.subType Not In ("b","c","d","e"); However this doesn't give me what I need. Thank you for your help. Best wishes, Simon. Hello Simon, I have a question. Are the letter combinations in one field? If so, put this into the criteria of your field: [Enter Letter Combination] This will allow you to type the letter a and only get the records that have it in the field. If you type ab you'll get those records, etc. Regards, Ray |
Thanks for the reply. Unfortunately they are not in the form of a
combination.
For example:
accountNo subType
1 a
1 c
2 d
3 e
1 e
4 a
5 c
etc.
I need to find all the accountNos where the subType is 'a' AND 'c'.
In this instance '1'.
Thanks for your help.
Best wishes,
Simon.
#4
| |||
| |||
|
|
Table accountNo PK subType subType domain is (a,b,c,d,e). Customers can have a subscription of any combination of types. I want to find all those that only have a subscription of type 'a'. It is possible that some people have a subscription to types a&b, a&c, a&d, a&e, b&c etc. I am using the following query: SELECT t1.subType, t1.accountNo FROM <table> as t1 INNER JOIN <table> as t2 ON t1.accountNo = t2.accountNo WHERE t1.subType = 'a' AND t2.subType Not In ("b","c","d","e"); Hi Ray, Thanks for the reply. Unfortunately they are not in the form of a combination. For example: accountNo subType 1 a 1 c 2 d 3 e 1 e 4 a 5 c etc. |
A silly question ...
I am wondering why in your where statement you state the <Not in>
part? I would think that if you are trying to get all the accountNo's
that have <a> in it and they are all set up as above then if you just
do the query for all accountNo where subType = 'a' then it would just
select those accountNo's you want. When they join is on accountNo it
would onlny display those accounts that have a subType <a>. Sure this
would also return a result for accountNo <1> <c> but I expect that you
would have all the account information in a single record.
Some comments from a newbe to SQL
Bernard
#5
| |||
| |||
|
|
raycait (AT) excite (DOT) com (Ray) wrote in message news:<20ba9f4a.0407080837.4a677f63 (AT) posting (DOT) google.com>... thisisjustaspamdump (AT) hotmail (DOT) com (Simon Long) wrote in message news:<40118e41.0407070625.12384808 (AT) posting (DOT) google.com>... Table accountNo PK subType subType domain is (a,b,c,d,e). Customers can have a subscription of any combination of types. I want to find all those that only have a subscription of type 'a'. It is possible that some people have a subscription to types a&b, a&c, a&d, a&e, b&c etc. I am using the following query: SELECT t1.subType, t1.accountNo FROM <table> as t1 INNER JOIN <table> as t2 ON t1.accountNo = t2.accountNo WHERE t1.subType = 'a' AND t2.subType Not In ("b","c","d","e"); However this doesn't give me what I need. Thank you for your help. Best wishes, Simon. Hello Simon, I have a question. Are the letter combinations in one field? If so, put this into the criteria of your field: [Enter Letter Combination] This will allow you to type the letter a and only get the records that have it in the field. If you type ab you'll get those records, etc. Regards, Ray Hi Ray, Thanks for the reply. Unfortunately they are not in the form of a combination. For example: accountNo subType 1 a 1 c 2 d 3 e 1 e 4 a 5 c etc. I need to find all the accountNos where the subType is 'a' AND 'c'. In this instance '1'. Thanks for your help. Best wishes, Simon. |
Now that it's a bit clearer concerning the number of fields being
used, just concantenate the two fields into on like below and make
another field to use the criteria:
NewField: [accountNo] & [SubType]
Then, in the criteria row type:
Like '*[Enter First Set*' and Like '*[Enter Second Set]*'
What this should do is find all records with 1a and 1c.
The * symbol is a wild card character being used just
to be safe.
Regards,
Ray
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.