By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Problem with DISTINCT query ?

P: n/a
Hello Folks,
can anybody tell me how to do the following very simple thing:
I have a table that has say departments, divisions and units of an
organisation and what i am trying to do is find a divison. One
department has many divisions, and one division many units. I am think
therefore a SELECT DISTINCT query on the division would make sense and
to put my search criteria for department on the WHERE. Of course that
doesnt work since you need the where clause to refer to a field in the
SELECT DISTINCT clause and if I put the field there it would make that
distinct as well. I know it must be simple but i acnnot figure out how
to do it.

May 10 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
In fact I can put the criteria in the WHERE even if they dont appear in
the SELECT (i just tried it in access Query design). Why then does it
not work when I put exactly the same in me.form.filter ??? (and it is
not the filteron = true or the quotation marks)

May 10 '06 #2

P: n/a
Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

May 10 '06 #3

P: n/a
The problem is that your form's recordset does not contain chrL2Description.

In another of your posts you say that this works in a query. That's because
the recordset for the query is the table, and so any of its fields are
available. In your form, you've limited the recordset to a single field,
and that's all that it knows about. To get it to work, you must include
chrL2Description in the form's recordset. The field does not need to be
displayed on the form; but it does need to be in the form's recordset.

HTH,

Rob

"Farmer" <kn******@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

May 10 '06 #4

P: n/a
Is chrL2Description a different field than chrL4Description, or is it
spelled wrong?

"Farmer" <kn******@gmail.com> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Right to be more precise with the problem: here is the query
on the form's record source:

SELECT DISTINCT tlkpLocationDirectory.chrL4Description FROM
tlkpLocationDirectory ORDER BY tlkpLocationDirectory.chrL4Description;

and here is the problematic code:

Me.Form.FilterOn = False
strWhere = "(((tlkpLocationDirectory.chrL2Description)="" " &
cboL2D.Value & """" & "))"
Me.Form.Filter = strWhere 'at this point the all too hostile enter
parameter box comes up and requests the value of
tlkpLocationDirectory.chrL2Description
Me.FilterOn = True
What did i do wrong? I got the query from the query design and it was
running.

May 10 '06 #5

P: n/a
L2 stands for level 2 (deparment) and L4 level 4 (branch)

May 10 '06 #6

P: n/a
Thank you Rob for your reply, if I did that then L2Descriprion would de
"DISTINCT" and I don't want it to be

May 10 '06 #7

P: n/a
And also in the query it was the exact same query that worked i.e.
SELECT DISTINCT tlkpLocationDirecotry.L4Description FROM...

May 10 '06 #8

P: n/a
You know something Rob...
You are absolutely right and I made a silly mistake. The mistake is: If
you make a SELECT DISTINCT query on two field it will return the pair
once and not each field once. Of course that is the only way it makes
sense otherwise you would not get meaningful results. I did not
understand that very well until now, it is my first Access program.

May 10 '06 #9

P: n/a
Glad you've got it!

Rob

"Farmer" <kn******@gmail.com> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...
You know something Rob...
You are absolutely right and I made a silly mistake. The mistake is: If
you make a SELECT DISTINCT query on two field it will return the pair
once and not each field once. Of course that is the only way it makes
sense otherwise you would not get meaningful results. I did not
understand that very well until now, it is my first Access program.

May 10 '06 #10

P: n/a
"Farmer" <kn******@gmail.com> wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

[the first of a half dozen posts threading out the description of
the problem over several articles]

Next time you want help, pause and think about how to describe it.
Then write ONE POST describing the whole problem.

Any time I see a thread with a chain of posts from the original
poster, I skip to the next thread, because I just don't have time to
sort through the confusion of assembling the multiple posts into a
coherent description of the problem.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.