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 |