ASP & database Relationships...

[PogoWolf]

Hey all,
I'm useing an Access database coupled with an ASP front end. I have created a 3 table, and linked them via the 'look up wizard' and create relationships between the tables.

The issue I'm having is that even with the relationships, the query only seems to return the numbers and not the information.

Such as I have an 'owner' table, and a 'lit' table.

the 'Lit' table has a field called owner that referances a record in the 'owner' table.

a query like this:
SELECT data FROM Lit WHERE Owner = 'PogoWolf';

wont work.. I would need to change 'pogowolf' into '1'
(the ID of Pogowolf in the 'owner' table)

am I missing something, that is not allowing my querys to use the realtionships set?

---===///The PogoWolf\\\===---
Darkness..Bleakness..Plastic forks..?

http://www.pogowolf.com

http://www.xboxnewsnet.com

 

[link9]

You have to use a JOIN statement --

SELECT something FROM lit INNER JOIN pogo ON lit.ownerID = pogo.ownerID WHERE pogo.ownerID = 'PogoWolf'

Use your QBE utility in Access (the query builder) to easily form your queries until you have a good handle on SQL syntax. Setting up the relationships in the database is for enforcing referential integrity and other business rules on INSERTing data, but to extract the data, you still must use JOIN statements if you want to reference data in many tables.

good luck!
Paul Prewett

 

[Gamera99]

If you want to get away from doing the inner joins, you can go into Access and create special query for each query that you plan to use (let's say that there will be no ad-hoc queries during app execution). Save each query in Access with a unique name.
Now in Interdev you will see each query listed under "Views" in the Data View. You can query from this new view with simplistic SQL "select * from myView".
If you are doing ad-hoc queries in your ASP then you have to do the inner joins like Paul said. In the Access Query Designer, Access is doing the inner joins for you.

 

[PogoWolf]

Thank you Paul and Gamera99. Gamera, I wonder what is 'Ad-Hoc' ? and Paul Thank you got clearing up the Reason for the relationships. I thought (well, and hoped) that it worked both ways.. =)
---===///The PogoWolf\\\===---
Darkness..Bleakness..Plastic forks..?

http://www.pogowolf.com

http://www.xboxnewsnet.com

 

[link9]

ad-hoc : on-the-fly

 

[PogoWolf]

Why ad-hoc and now OTF? LOL =) but yes, I will be doing the on the fly querys on my database.

If case you which to know what I'm trying to pull off, just check out

http://www.pogowolf.com

in the friends area. All if it database driven.
---===///The PogoWolf\\\===---
Darkness..Bleakness..Plastic forks..?

http://www.pogowolf.com

http://www.xboxnewsnet.com