A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Simple query?



 
 
Thread Tools Display Modes
  #11  
Old April 22nd, 2010, 04:33 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Simple query?

Yes, the 'exists' filter works. I'm still able to edit. Didn't know that was
even possible! Thanks.

Lars

"Paul Shapiro" schreef in bericht
...
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


  #12  
Old April 22nd, 2010, 04:34 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Simple query?

Point taken.
Thanks,

Lars

"Dirk Goldgar" schreef in bericht
...
"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)

  #13  
Old April 22nd, 2010, 04:43 PM posted to microsoft.public.access
Lars Brownies[_2_]
external usenet poster
 
Posts: 157
Default Simple query?

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" schreef in bericht
...
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


  #14  
Old April 22nd, 2010, 06:53 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default Simple query?

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" schreef in bericht
...
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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.