hi bob, many thanks for your quick response.

the first problem isn't a problem as i wrote this just for the post to
make it clearer for the readers.

the second tip you gave really helped thanks. it's what i was actually
trying to do - thanks for clearing it up for me.

take care

[quoted text, click to view]

Bad, bad bad bad bad.

1. This is the minor problem: You have a nonstandard character in the
"Index.Branch" filed, requiring you to take special precautions whenever you
query that field.

2. This is the major problem: You are storing multiple pieces of data in the
Index.Branch field, which is a violation of first normal form. It is this
violation that is causing your problem now.

What you have here is a "many-to-many" relationship between these two
tables. I.E. many records in the first table can be associated with/related
to many records in the branch table. The way to resolve this relationship is
through the use of a "bridge" table (other people have other names for this
type of table). In this case, the bridge table would look like this:

K_Nr Bundesland Bezirk Branch
1 2 2 1
1 2 2 2
1 2 2 3
2 5 3 2
2 5 3 3
etc.

This makes it possible to write queries without having to break up multiple
pieces of data in a single field.

Give it a try and see for yourself. And do yourself a favor: use only the
letters a-Z for your database object names.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Nigel, a table by definition is unordered. If you are looking at the data
and want it "grouped together" then use an ORDER BY clause... that's what
it's for.

--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.




[quoted text, click to view]

hi,

i am trying to run a asp sql query containging many tables.

the main table though is a simple ID based table contains all ids from the
other tables.

eg:

K_Nr Bundesland Bezirk Index.Branch
1 2 2 1,2,3
2 5 3 2,3
3 2 2 2
4 2 2 2,5,8

etc.

BRANCH TABLE:

1 branch1
2 branch2
3 branch3
4 branch4

etc

i can easily acces all records that contain customer_region=2 or customer_nr
= 1 and so on.

but where i am having difficulties is where i need to access all Branches in
Region=2 - "as text"

due to the commas in Index.Branch I had to make the field "text" which then
confilcts against the "number" field in Branch .Table

i can run a loop on the Index.Branch field, but then i get a unordered list
that is not grouped together.


REQUIRED RESULT FOR BEZIRK 2

Index.Branch
branch1
branch2
branch3
branch5
branch8

if it helps here is my SQL

SELECT DISTINCT Index.K_Nr, Index.Bundesland, Index.Bezirk, Index.Branche,
Bundesland.BL_Bundesland
FROM branchen, (Bundesland INNER JOIN (BL_Bezirke INNER JOIN ([Index] INNER
JOIN Kunden ON Index.K_Nr = Kunden.K_nr) ON BL_Bezirke.BezirkID =
Index.Bezirk) ON (BL_Bezirke.BL_Nr = Bundesland.BL_Nr) AND (Bundesland.BL_Nr
= Index.Bundesland)) INNER JOIN Auftrag ON Kunden.K_nr = Auftrag.A_K_Nr
WHERE (((Index.Bezirk)=17) AND ((Index.Branche) Like "*204*"));


Do While Not Rs2.EOF

Response.Write ">" & Rs2("Bezirk") & "<br>"

MyString = Rs2("Index.Branch")
MyArray = Split(MyString,",")

For i=0 to UBound(MyArray)
Set conn = Server.CreateObject("ADODB.Connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("database/main.mdb")
conn.Open connstr

SQL1 = "Select branchen.* from branchen where Branch= '" &
myArray(i) & "'"
set Rs3=conn.execute(SQL1)

response.write "&nbsp;&nbsp;&nbsp;" & Rs3("B_nr") & " - " &
Rs3("B_Branche") & "<br>"
Next
Rs2.MoveNext

Response.Write("<br><br><br>")
Loop


any help is really appreciated.

thanks in advance

nudge

[quoted text, click to view]
:
: Bad, bad bad bad bad.
:

I have to refrain from reading your posts out loud. I couldn't understand
why the dog came in my room moping when I was reading this one and then I
realized, "bad", is her middle name. No-no "Bad" Dog.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

[quoted text, click to view]

:-)
That was the image I was trying to evoke.

Bob
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"