Creating a Recordset from a many-to-many access db relationship

[DotComDV]

Hi there,

i have an access database, with a many-to-many relationship, consisting of 3 tables:

tblStock -
About 8 columns, eg. CODE, TITLE, AUTHOR (product details)

tblCategories -
Number
Name

tblStockCat -
StockRef (linked to CODE in tblStock)
CatRef (linked to NUMBER in tblCategories)

What i would like to do, is have a categories.asp page with links (eg. books.asp?cat=1) to the books.asp page. And by defining the cat=1, i want to be able to display all of the books on books.asp, that are in category 1.

What would be an appropriate SQL statement here? i have tried several but keep getting errors. Also, i am using dreamweaver mx, if that makes any difference. Please help!

 

[Herminio]

use the Acess query wizard and you'll get the SQL for doing that, when you finish to add all the fields you want goto view --> SQL view(or something like that), copy the SQL statement and paste it on your asp page, i use that method when i have doubts on how to build the SQL statement.

 

[DotComDV]

thanx, i will try that - sounds good, i will let u know how it goes when i try it tomorrow (im an aussie and it is 10:30pm) and i am sure as hell not at work at that hour!

Cheers,
Shane.

 

[DotComDV]

I am confused, how would u so something like that - even using the wizard, it still doesnt make sense!

Could u please elaborate? Cheers.

 

[Tarwn]

Ok, basically what you need to do first is cross all of the tables. Let me first begin by restating (some of) what is there:

tblStock -
Code
Title
Author

tblCategories -
Number
Name

tblStockCat -
StockRef (linked to CODE in tblStock)
CatRef (linked to NUMBER in tblCategories)


So first we know we are going to need to get information from all three tables, so lets do that:
Select * FROM tblStock, tblStockCat, tblCatagories WHERE tblStock.Code = tblStockCat.StockRef AND tblStockCat.CatRef = tblCatagories.Number

Now this will give us all the information from all three tables in related rows. Now we want to restrict this by saying only the books in catagory 1(assuming this is not the number field):

Select * FROM tblStock, tblStockCat, tblCatagories WHERE tblStock.Code = tblStockCat.StockRef AND tblStockCat.CatRef = tblCatagories.Number AND tblCatagories.WhateverField = 1

If in fact that catagory we are selecting for is the number field, our job becomes much easier because we only need to select from the tblStock and the tblStockCat tables, because we already know the Catagories.number:

SELECT * FROM tblStock, tblStockCat WHERE tblStock.Code = tblStockCat.StockRef AND tblStockCat.CatRef = 1

Now before anyone else gets on me about it, yes it is a bad idea to select * from a database. For one, you may not need all of the fields, so this is wasteful of communications time and temporary data storage, secondly in order for the database to know what you mean by * it first has to do an internal query to determine exactly what fields it will be returning and then it executes your query, meaning if you list your fields instead than the db will process your query more efficiently and quickly.


Hope this is what you were aiming for,

-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924

 

[DotComDV]

damn it!! all that makes sense, but it still comes up with the error i always get:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

wat this means, i am not sure - wat u have said makes perfect sense and i cannot understand why this comes up!

 

[Tarwn]

ok, the problem is likely with the fields "code" and "cumber" it is my bet that is anything these are reserved words, try plugging it directly into the query analyzer in access. If it runs without a problem than you will need to figure out which of your fields are reserved words, or simply put square brackets around the names.

Let me know what happens, it's possible I have overlooked something else,
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924