sql query string loop rype thing.

Hi - see if you can get your head around this one.
In the db (access), got many tables interlinked.

There are about a dozen departments, which they select on the previous page,
where most of the data is entered into a table. Each department has a code -
just a number from 1- 30 or something. these codes are stored in a table,
along with their corrosponding 'error codes' - the codes for what errors can
be wrong in that deprtment.

So when I run that sql query, I get a list of the possable error codes for
that department.

now, the actual errors are stored in a different table

Table tbleDECodes

DeptID ErrorCode
2 1
2 3
3 1



Table ErrorCodes

Error Code Name
1 Missing
2 Extra
3 Damage

so what I'm trying to do, is get a list of the possable error codes,
determined by the deptID - which I've done. I then need to some how (I
guessing here) loop this through a second sql query to get the correspong
error, so I can have a form, with the error code as the value, and the
corresponding error name as the name as I said, I expect it's some sort of
loop - only i've never done loops before. Got any pointers or good websites
I can have a ganders at?

This is what I have so far

which works, but when I reponse.write strQb, I just get


code:
SELECT * FROM tblErrorCodes WHERE ErrorCode


code:
Dim strQb
StrQb = strQb & "SELECT * FROM tblErrorCodes WHERE ErrorCode ="

If objRS.BOF And objRS.EOF Then
Response.Write "No Entries Found."
Else
While Not objRS.EOF
strQb = strQb & objRS("ErrorCode") &" OR "
objRS.MoveNext
Wend
End If

strQb = Left(strQb, Len(strQb) - 2)many thanks for your help!

Re: sql query string loop rype thing.

David Aldred wrote:
> Hi - see if you can get your head around this one.
> In the db (access), got many tables interlinked.
>
> There are about a dozen departments, which they select on the
> previous page, where most of the data is entered into a table. Each
> department has a code - just a number from 1- 30 or something. these
> codes are stored in a table, along with their corrosponding 'error
> codes' - the codes for what errors can be wrong in that deprtment.
>
> So when I run that sql query, I get a list of the possable error
> codes for that department.
>
> now, the actual errors are stored in a different table
>
> Table tbleDECodes
>
> DeptID ErrorCode
> 2 1
> 2 3
> 3 1
>
>
>
> Table ErrorCodes
>
> Error Code Name
> 1 Missing
> 2 Extra
> 3 Damage
>
> so what I'm trying to do, is get a list of the possable error codes,
> determined by the deptID - which I've done. I then need to some how (I
> guessing here) loop this through a second sql query to get the
> correspong error, so I can have a form, with the error code as the
> value, and the corresponding error name as the name

No, no no no no.
Have you never heard of a join? Run this query in Access using the query
builder.

Select DeptID,d.ErrorCode,[Name]
FROM FROM tbleDECodes d inner join ErrorCodes e
ON d.ErrorCode = e.ErrorCode
ORDER BY DeptID, ErrorCode

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.