tearing my hair out query

[gold....@gmail.com]

Please help me solve this.

I have a table city with city and citydata like below

san francisco - a,b,f,z
seattle - j,a,w,z
new york - a, f, j


I have another table called elements which just has
a
b
f
j
w
z

I want to create a report that gives me:

a - san francisco, seattle, new york
b - san francisco
f - san francisco, new york
j - seattle, new york
w - seattle
z - san francisco, seattle

How?

[Bob Barrows]

You can start by fixing your database design. Storing multple peices of data
in a single fiield is not a good idea and you are now seeing why. The
preferred design would look more like:

san fancisco a
san fancisco b
san fancisco f
san fancisco z
etc.

If you store other pieces of data about the cities, then you need two
tables, the first table without the cityparts column that has a single row
per city, and the CityElements table that has multiple rows per city, with
the two tables related by city.
This design makes it easy to sort by element and see cities by element.
Unfortunately, your report requirement goes beyond the simple

a
san francisco
seattle
new york
b
san francisco
etc.

which report could be created in a couple of minutes. assuming that your
table design was fixed.

If you are locked into the table design, or your report requirements are
non-negotiable, you will need to use some VBA to achieve the required
result. I need to leave for work now but I will check back tonight and if
nobody has been able to help, I will take a stab at it.

[JHB]

<gold....@gmail.com> skrev i en meddelelse news:695c2fad-4c4a-4d2a...@googlegroups.com...

Try.

SELECT Elements.Element, City.City
FROM City, Elements
WHERE (((City.CityData) Like "*" & [Element] & "*"));

Regards
Jørn

[gold....@gmail.com]

Jørn,

This would work. But what is I have hundreds of elements in the elements table?

Bob is right. Table design is key. However, I got this list as is. Perhaps some ASP coding will work.

[David Hare-Scott]

gold....@gmail.com wrote:
> Jørn,
>
> This would work. But what is I have hundreds of elements in the
> elements table?
>

Then performance will be poor as a brute-force search is done through these
strings.

> Bob is right. Table design is key. However, I got this list as is.
> Perhaps some ASP coding will work.
>

I don't see the point here. Why would coding the parsing of these strings
in some other language be of more use? The quality of the saddle makes
little difference to speed if you are riding a three-legged cow.

Instead use proper design where you can index the fields being searched or
joined. If you can search within such a multi datum field using VBA then
you can parse it and stuff the values into a normalised table, they your
future development will proceed much more smoothly and performance will be
better whenever queries need to be run. Fix it once.

David

[The Frog]

Having managed many odd data types for integration into larger
systems I would suggest to you that the suggested redesign approach
is the best one. To make this easier you can use ETL tools to break
up the data into a more manageable form. I use Pentaho Data
Integration as it is easy to learn, powerful, free, and drag n drop.
Generate a unique row for each of your existing rows combinations of
state and letter, then dump it to a table or csv. You should be right
from there.

--
Cheers

The Frog

[bobh]

where do you get(url) Pentaho Data Integration for free???
thanks
bobh.

[The Frog]

That's an easy one Bob!
WWW.Pentaho.com
You can find a link to it on the main webpage. You'll need a java
runtime to run it - any recent one should do fine. Their forums
(community) are excellent for support too.

--
Cheers

The Frog

[Patrick Finucane]

If you know how to write code to read/write a recordset and loop through the records using VBA, why not generate the tables formatted like others recommended. VBA functions that would be used; Instr(), Mid(), Split(), Lbound(), and Ubound().