If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Simple query?
I'm testing with filter queries.
I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? Thanks, Lars |
#2
|
|||
|
|||
Simple query?
Lars
I may be reading too much into your description... It sounds like your table for Categories include Persons (or at least PersonIDs). In a well-normalized relational database table of categories, there'd be no person-related information. If you are noting persons by category, that would seem to imply three tables, one for persons, one for categories, and one for the junction between them (i.e., person X category). If you want to see a query that pulls all [ID_Person] where Category = A or Category = B, consider creating a new query in design view, setting these selection criteria, then switching the view to the SQL view. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Lars Brownies" wrote in message ... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? Thanks, Lars |
#3
|
|||
|
|||
Simple query?
I have 2 tables:
tblPerson ID_person* Lastname Firstname etc. tblPersonCategory ID_person* Category* (which can have value A, B, or C) Many people can have zero, one or more categories. Since the values are A, B, and C, I felt no need to put them in an extra table. If you want to see a query that pulls all [ID_Person] where Category = A or Category = B, consider creating a new query in design view, setting these selection criteria, then switching the view to the SQL view. I have no problem with that *OR* query. What I need is all the unique ID_numbers that have *both* A and C as their category. Or as another example A and B and C. An AND statement doesn't work in this case. Lars "Jeff Boyce" schreef in bericht ... Lars I may be reading too much into your description... It sounds like your table for Categories include Persons (or at least PersonIDs). In a well-normalized relational database table of categories, there'd be no person-related information. If you are noting persons by category, that would seem to imply three tables, one for persons, one for categories, and one for the junction between them (i.e., person X category). If you want to see a query that pulls all [ID_Person] where Category = A or Category = B, consider creating a new query in design view, setting these selection criteria, then switching the view to the SQL view. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Lars Brownies" wrote in message ... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? Thanks, Lars |
#4
|
|||
|
|||
Simple query?
"Lars Brownies" wrote in message
... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? If I understand your question correctly, you may have multiple records in tblCategory for the same ID_Person, each with a different value for Category, and you want to extract those values of ID_Person for which there is a record for Category "A" and another record for Category "C". Is that correct? If so, then a query like this would probably do it: SELECT DISTINCT ID_person FROM tblCategory C WHERE Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "A") AND Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "C") There are a couple of other ways to do it, I think, but this one follows the logic (as I understand it) closest. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
Simple query?
|
#6
|
|||
|
|||
Simple query?
Lars Brownies wrote:
But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? Does your data look anything like this? ID_person Category 1 a 1 b 1 c 2 c 2 d 3 b 4 a If so, and 1 is the only ID_person value which satisfies your criteria, try this query: SELECT DISTINCT a.ID_person FROM ( SELECT ID_person FROM tblCategory WHERE Category="A" ) AS a INNER JOIN ( SELECT ID_person FROM tblCategory WHERE Category="C" ) AS c ON a.ID_person = c.ID_person; |
#7
|
|||
|
|||
Simple query?
Thanks! Dirk, others,
Indeed that's what I meant. In relation to my other post 'Filter suggestions in form' I don't see that I can add the main table (Person) to this 3-in-1 query. The query wouldn't be editable. So this seems to be no option. I'm trying to normalize a table, and even in 1st normal form, it seems to give me more hassle than benefits. Do you agree? Lars "Dirk Goldgar" schreef in bericht ... "Lars Brownies" wrote in message ... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? If I understand your question correctly, you may have multiple records in tblCategory for the same ID_Person, each with a different value for Category, and you want to extract those values of ID_Person for which there is a record for Category "A" and another record for Category "C". Is that correct? If so, then a query like this would probably do it: SELECT DISTINCT ID_person FROM tblCategory C WHERE Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "A") AND Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "C") There are a couple of other ways to do it, I think, but this one follows the logic (as I understand it) closest. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#8
|
|||
|
|||
Simple query?
Which table do you want to be able to edit? If you're form's record source
is based on Person, then maybe a filter clause like this one based on Dirk's response would work? I think this should work with a form record source like Select * From Person. Exists ( SELECT * FROM tblCategory T WHERE T.ID_person = Person.ID_Person And T.Category = "A" ) And Exists ( SELECT * FROM tblCategory T WHERE T.ID_person = Person.ID_Person And T.Category = "B" ) If not, you see if a form record source like this would leave Person data editable: Select Person.*, Not IsNull(C1.personID) as HasCategoryA, Not IsNull(C2.personID) as HasCategoryB From Person Left Outer Join tblCategory C1 On C1.personID=Person.personID And C1.Category = "A" Left Outer Join tblCategory C2 On C2.personID=Person.personID And C2.Category = "B" "Lars Brownies" wrote in message ... Thanks! Dirk, others, Indeed that's what I meant. In relation to my other post 'Filter suggestions in form' I don't see that I can add the main table (Person) to this 3-in-1 query. The query wouldn't be editable. So this seems to be no option. I'm trying to normalize a table, and even in 1st normal form, it seems to give me more hassle than benefits. Do you agree? Lars "Dirk Goldgar" schreef in bericht ... "Lars Brownies" wrote in message ... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? If I understand your question correctly, you may have multiple records in tblCategory for the same ID_Person, each with a different value for Category, and you want to extract those values of ID_Person for which there is a record for Category "A" and another record for Category "C". Is that correct? If so, then a query like this would probably do it: SELECT DISTINCT ID_person FROM tblCategory C WHERE Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "A") AND Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "C") There are a couple of other ways to do it, I think, but this one follows the logic (as I understand it) closest. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html |
#9
|
|||
|
|||
Simple query?
"Lars Brownies" wrote in message
... In relation to my other post 'Filter suggestions in form' I don't see that I can add the main table (Person) to this 3-in-1 query. The query wouldn't be editable. So this seems to be no option. I think Paul Shapiro addressed this in his reply. I'm trying to normalize a table, and even in 1st normal form, it seems to give me more hassle than benefits. Do you agree? No. Properly normalized tables increase the power and flexibility of your database. Though they may sometimes require more complex SQL to answer certain kinds of questions, they make it possible to answer all sorts of questions that are otherwise difficult or impossible even to frame. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#10
|
|||
|
|||
Simple query?
One more possibility
SELECT tblPerson.* FROM tblPerson WHERE tblPerson.ID_Person in (SELECT ID_Person FROM tblPersonCategory WHERE Category in ('A','B') GROUP BY Id_Person HAVING Count(Id_Person) = 2) This should work since tblPersonCategory has a primary key based on Id_Person plus Category. At least that is my assumption based on the asterisks in your table descriptions. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Paul Shapiro wrote: Which table do you want to be able to edit? If you're form's record source is based on Person, then maybe a filter clause like this one based on Dirk's response would work? I think this should work with a form record source like Select * From Person. Exists ( SELECT * FROM tblCategory T WHERE T.ID_person = Person.ID_Person And T.Category = "A" ) And Exists ( SELECT * FROM tblCategory T WHERE T.ID_person = Person.ID_Person And T.Category = "B" ) If not, you see if a form record source like this would leave Person data editable: Select Person.*, Not IsNull(C1.personID) as HasCategoryA, Not IsNull(C2.personID) as HasCategoryB From Person Left Outer Join tblCategory C1 On C1.personID=Person.personID And C1.Category = "A" Left Outer Join tblCategory C2 On C2.personID=Person.personID And C2.Category = "B" "Lars Brownies" wrote in message ... Thanks! Dirk, others, Indeed that's what I meant. In relation to my other post 'Filter suggestions in form' I don't see that I can add the main table (Person) to this 3-in-1 query. The query wouldn't be editable. So this seems to be no option. I'm trying to normalize a table, and even in 1st normal form, it seems to give me more hassle than benefits. Do you agree? Lars "Dirk Goldgar" schreef in bericht ... "Lars Brownies" wrote in message ... I'm testing with filter queries. I have a table with fields ID_person and Category. Both fields belong to the primary key. Pulling out the unique ID numbers which have Category A is simple: SELECT ID_person FROM tblCategory WHERE (((Category)="A")); But I would like to pull out every unique ID numbers that has for instance both "A" and "C" as its category. How can I do that? If I understand your question correctly, you may have multiple records in tblCategory for the same ID_Person, each with a different value for Category, and you want to extract those values of ID_Person for which there is a record for Category "A" and another record for Category "C". Is that correct? If so, then a query like this would probably do it: SELECT DISTINCT ID_person FROM tblCategory C WHERE Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "A") AND Exists(SELECT T.ID_person FROM tblCategory T WHERE T.ID_person = C.ID_Person And T.Category = "C") There are a couple of other ways to do it, I think, but this one follows the logic (as I understand it) closest. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html |
|
Thread Tools | |
Display Modes | |
|
|