Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working
From: Steve on 21 Apr 2010 17:25
In your form to enter PersonCategory, do you enter Category through a
combobox with a rowsource of a value list? If not and you type in A, B, C,
then you rin a large risk of a typo in a record making the data in
TblPersonCategory unreliable.

Steve
santus(a)penn.com


"Lars Brownies" <Lars(a)Browniew.com> wrote in message
news:hqnphq$2lmd$1(a)textnews.wanadoo.nl...
>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: Lars Brownies on 21 Apr 2010 17:33
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" <dg(a)NOdataSPAMgnostics.com.invalid> schreef in bericht
news:u5Y1zdZ4KHA.6060(a)TK2MSFTNGP04.phx.gbl...
> "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)
>
From: Paul Shapiro on 21 Apr 2010 18:24
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" <Lars(a)Browniew.com> wrote in message
news:hqnquu$2mab$1(a)textnews.wanadoo.nl...
> 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" <dg(a)NOdataSPAMgnostics.com.invalid> schreef in bericht
> news:u5Y1zdZ4KHA.6060(a)TK2MSFTNGP04.phx.gbl...
>> "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

From: Dirk Goldgar on 22 Apr 2010 09:34
"Lars Brownies" <Lars(a)Browniew.com> wrote in message
news:hqnquu$2mab$1(a)textnews.wanadoo.nl...
>
> 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)

From: John Spencer on 22 Apr 2010 10:38
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" <Lars(a)Browniew.com> wrote in message
> news:hqnquu$2mab$1(a)textnews.wanadoo.nl...
>> 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" <dg(a)NOdataSPAMgnostics.com.invalid> schreef in bericht
>> news:u5Y1zdZ4KHA.6060(a)TK2MSFTNGP04.phx.gbl...
>>> "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
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working