Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working
From: Lars Brownies on 22 Apr 2010 11:33
Yes, the 'exists' filter works. I'm still able to edit. Didn't know that was
even possible! Thanks.

Lars

"Paul Shapiro" <paul(a)hideme.broadwayData.com> schreef in bericht
news:uYjtdFa4KHA.4016(a)TK2MSFTNGP05.phx.gbl...
> 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: Lars Brownies on 22 Apr 2010 11:34
Point taken.
Thanks,

Lars

"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> schreef in bericht
news:46189A0C-9A02-4547-9A97-38D81011B02C(a)microsoft.com...
> "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: Lars Brownies on 22 Apr 2010 11:43
Yes, I use a composite key. Your solution works as well and I'm also able to
edit! Thanks.

One additional questions regarding performance:
When working with great amout of records, what option will be faster/better?
The filter option or the 'record source' option? Any other benefits on using
one over the other?

Lars


"John Spencer" <spencer(a)chpdm.edu> schreef in bericht
news:eLwLsli4KHA.1888(a)TK2MSFTNGP05.phx.gbl...
> 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
>>
From: John Spencer on 22 Apr 2010 13:53
I don't know which would be faster. I would have to build the tables and try
the different options.

If I was guessing I would guess that the option I proposed would have a good
chance of winning a speed race. However; if you can make the second option
proposed by Paul Shapiro work in Access, it may be as fast or faster.
Although I would have used INNER JOINS since you only want to return records
that match.

The exists option would be the slowest since it is using two correlated
sub-queries - which means that two separate queries would run for every record
in the Person table


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

Lars Brownies wrote:
> Yes, I use a composite key. Your solution works as well and I'm also
> able to edit! Thanks.
>
> One additional questions regarding performance:
> When working with great amout of records, what option will be
> faster/better? The filter option or the 'record source' option? Any
> other benefits on using one over the other?
>
> Lars
>
>
> "John Spencer" <spencer(a)chpdm.edu> schreef in bericht
> news:eLwLsli4KHA.1888(a)TK2MSFTNGP05.phx.gbl...
>> 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  | 
Pages: 1 2 3
Prev: Repeat Corruption Of Database File
Next: Can someone help; pipe delimited text import not working