SQL statement help

  • Thread starter Thread starter DP
  • Start date Start date
D

DP

Sorry for not being able to put a more precise description in the Subject
line of this posting, but I wasn't sure how to describe the problem.

I'm trying to figure out whether it's possible to run an order query like
this in order to begin populating a Treeview:

strsql = "SELECT DISTINCT Keyword FROM tblkeywordmasterOR;"

which will get me all ***distinct*** rows from tblkeywordmasterOR.

However, this will produce lots of records like this because each value
below is, of coruse, distinct:

D - xxxxx
D - yyyyy
D - xxxxx
D - aaaaa
D - bbbbb
Word - xxxx
Word - yyyy
Word - aaaaa


What I'm trying to do is separate out the initial string up to the " - ",
which is easy enough to do, ******BUT******* then I want only ONE VALUE
returned:

D -
Word -

Can I do this?!

The rest I can solve, and would then produce a treeview display something
like this

D -
xxxxx
yyyyyy
xxxxx
aaaaa
bbbbb

Word -
xxxx
yyyy
aaaa

But I'm stuck on limiting the returned value of the multiple first word
instances (D - , Word - ) to only ONE.

Can anyone help me?

Thank you, David Pie
 
Sorry, there's an extra - confusing - word in the following paragraph that
should be ignored. There is no such thing as an "order query."

I'm trying to figure out whether it's possible to run an order query like
this in order to begin populating a Treeview:

The sentence should read simply:

I'm trying to figure out whether it's possible to run a query like
this in order to begin populating a Treeview:
 
This is what happens when you store multiple pieces of information in the
same field. A field in a table should be atomic, meaning that it cannot be
divided any further. Since you want partial information from the field
means that it is NOT currently atomic. These should be stored in separate
fields.

HOWEVER, since you are already set like this, I'd suggest something like the
following:
strsql = "SELECT DISTINCT Left(Keyword, InStr(Keyword, "-") AS KeyGroup FROM
tblkeywordmasterOR;"
 
Roger,

Yes, of course you are right, but there are good reasons - and unavoidable,
I believe - for doing it like I have.

What you suggest, I believe, is creating an alias, which allows for
considerable flexibility, and I'm sure it will work.

However, having just tried it, I'm getting a Type Mismatch error message.

Any idea why? I changed "Keyword" to the full designation
tblKeywordMasterOR.Keyword, but still get the error.

I'd be grateful for any additional suggestions.

David Pike
 
Sorry. Without knowing the experience level of a poster, I assume the
lowest and give the proper solution in addition to answering the specific
question.

Hmmm. I just tested it in a test query of my own, and it worked fine:
SELECT DISTINCT Left([auname],InStr([auname],"-")) AS NameGroup
FROM au1;

I wonder if "Keyword" is a reserved word or somehow confusing Access. Have
you tried putting brackets around it?
 
Roger, thanks for the additional help - it was my problem.

Naturally, when referring to the "Keyword" field in trying to populate the
Treeview with a recordset, I could no longer refer to rst!keyword, but
rather had to change all these references to rst!NameGroup [or whatever
alias I used]. Then it worked just fine.

NOW my problem is going to the next level of "node adding" for the Treeview
to generate code that will properly populate the second level of the tree.
Instead of 350 "D" lines in the original table, I now have - as I wanted -
only one "D" - in the alias table.

I have to figure out how to place ALL original 350 "D"s at the second level
underneath the single "D."

It can be done, I'm sure.

But your original suggestion was very helpful, thanks so much!

David Pike


Roger Carlson said:
Sorry. Without knowing the experience level of a poster, I assume the
lowest and give the proper solution in addition to answering the specific
question.

Hmmm. I just tested it in a test query of my own, and it worked fine:
SELECT DISTINCT Left([auname],InStr([auname],"-")) AS NameGroup
FROM au1;

I wonder if "Keyword" is a reserved word or somehow confusing Access. Have
you tried putting brackets around it?
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

DP said:
Roger,

Yes, of course you are right, but there are good reasons - and unavoidable,
I believe - for doing it like I have.

What you suggest, I believe, is creating an alias, which allows for
considerable flexibility, and I'm sure it will work.

However, having just tried it, I'm getting a Type Mismatch error message.

Any idea why? I changed "Keyword" to the full designation
tblKeywordMasterOR.Keyword, but still get the error.

I'd be grateful for any additional suggestions.

David Pike



cannot
be like
the KeyGroup
FROM " -
",
Click to expand...
Click to expand...
 
Back
Top