Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working
From: Lars Brownies on 21 Apr 2010 16:34
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









From: Jeff Boyce on 21 Apr 2010 16:54
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" <Lars(a)Browniew.com> wrote in message
news:hqnnhh$2l0f$1(a)textnews.wanadoo.nl...
> 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
>
>
>
>
>
>
>
>
>


From: Hans Up on 21 Apr 2010 17:29
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;
From: Lars Brownies on 21 Apr 2010 17:09
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" <nonsense(a)nonsense.com> schreef in bericht
news:eakpbTZ4KHA.4964(a)TK2MSFTNGP05.phx.gbl...
> 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" <Lars(a)Browniew.com> wrote in message
> news:hqnnhh$2l0f$1(a)textnews.wanadoo.nl...
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
From: Dirk Goldgar on 21 Apr 2010 17:13
"Lars Brownies" <Lars(a)Browniew.com> wrote in message
news:hqnnhh$2l0f$1(a)textnews.wanadoo.nl...
> 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)

 |  Next  |  Last
Pages: 1 2 3
Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working