This content has been marked as final. Show 3 replies
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.
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.
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:
**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!
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: