CFLoop value within query name

[semi_star_gazer]

OK, so I don't even know if what I am trying to do is possible, but here goes...

I have one table, practiceArea, that is basically a lookup table, with the fields id, areaName, and anchor.

That table is relational to a larger table, mentors, which pulls the id number from practiceArea that is applicable to each record.

I am trying to run a query that loops through each id value dynamically, to avoid having to write 57 queries, one for each record in the practice Area table, and then display navigation generated from the query, where if the recordcount neq 0, the areaName shows up. That was probably really confusing.

When I look at the page live, I am getting the following error:

nvalid CFML construct found on line 202 at column 90.
ColdFusion was looking at the following text:

#

The CFML compiler was processing:

* a cfif tag beginning on line 202, column 74.

200 : <p>
201 : <cfoutput>
202 : <cfif getMentors_#id#.recordCount neq 0>
203 : <cfloop query="getMentors_#id#">
204 : <a href="anchor">areaName</a> |


So, I'm not sure if the query is running correctly, and I just need to figure out the correct way to nest the # marks, or if there's something weird going on with the query itself. Here's my code... Please help! Thanks.

[Dinghus]

Talk about doing things the hard way .... It seems you could much easier do a JOIN to get the same info easier.

Where to start on this one?

- First query, since you are only using the ID, select it only.
- 2nd Query you need to prefix all your selects etc with the table name or mnemonic. IE mentors.prefix Leaving it off could be confusing the database since you are telling it to pull from 2 tables. Same for where clauses and orderby.
Now in the DISPLAY CODE you have a serious problem. You are looking at the LAST ID pulled and then looping over it if there is any data in the query. But only that 1 ID number.

Walk thru it step by step keeping track of what your ID number is and you will see what I mean.

Solution : Sit down far away from your computer and draw a flow chart of how you want this to work. Then figure out the code to make it work. THEN go back to your puter and put the code in. Right now it is not doing anything useful it would appear. There are also a lot of basic errors in your code with missing "#"s and such.

Even better would be to look at doing a JOIN. I'm betting you can do all this in 1 query.

[semi_star_gazer]

Yeah, I kind of thought it might be a mess... sigh. Any help on the query would be appreciated.

Here are my three tables and the relevant fields:

MENTORS
id
practiceTypeID
practiceArea1ID,
practiceArea2ID,
practiceArea3ID,
practiceArea4ID
**I am sure there is a better way to arrange this than having 4 fields that are essentially the same thing, and I am open to suggestions!

PRACTICETYPE
id,
typeName

PRACTICEAREA
id,
areaName

In my Access database, I set up relationships (in the Relationships window) like this:
practiceType.id = mentors.practiceTypeID - One-To-Many
practiceArea.id = mentors.practiceArea1ID - One-To-Many (I can only figure out how to set up the relationship on the first "practiceArea()ID" field, because if I add the other 3 fields, it doesn't work)

I know this is a mess, and there is probably a really simple way to combine it all into one query, but I'm just not sure where to start! Any advice would be welcome, thanks!

So, here is what I am trying to do:

[Dan_Bracuk]

Fieldnames like PracticeArea1, PracticeArea2, etc tell me your db design could be improved upon. If you don't know what normalized to the 3rd form means, I have heard good things about the book, Database Design for Mere Mortals.