Displaying data from 2 Access tables in detail page

[Ito2233]

I am constructing a master/detail page set. The master page lists information about companies (From a COMPANY table). When you click on the company's name, you are taken to the detail page, which displays both the company information (from the COMPANY table) and the contact people for that company (from the CONTACTS table). I am passing along the Company ID key to the detail page (since it's the foreign key that links the COMPANY and CONTACTS TABLE). However, I do not know how to display both information from the COMPANY table and the CONTACTS table in the detail page, based on the key I pass. I'm guessing I need two recordsets, but I'm not sure how to let Dreamweaver know which records from the CONTACTS table match the company whose information is being displayed on screen.
Thanks

 

[YNOTU]

[tt]One option I've used was by creating either a query in access or a view in sql pulling from there to my recordset[/tt]

​

 

[Cheech]

I would just add the company ID to a variable:

<%
strCompany = request.querystring("ID") ' or form
%>

Now just set the filter for both recordsets to only pull details where they match strCompany

Cheech

[Peace][Pipe]

 

[Ito2233]

Hmmm...I'm sure that would work, but I'm not sure how to do it. I'm unfamiliar with the scripting...I just click on the buttons in Dreamweaver. How do I transfer that companyID to the detail page?
Thanks

 

[josel]

guisilva,

I am not sure which serverside technology you're using so it is tough to suggest syntax. However, following is a standard SQL query to join two tables by a key field

1 select * from
2 modules join faq on faqModID = modID
3 order by modID, faqquestion ASC

Line 1 tells SQL to select all records.

Line 2 identifies tables "modules" and "faq". In this case, there is 1 module and many faq per module. The fields (aka: columns) faqModID and modID are the key fields common to both tables. The join command tells SQL to return only records that match one another's key fields. So, here you would get a group of faq (rows) per modID.

Line 3 tells the order in which data should be returned.


Now, as per passing the ID from page to page, there are various ways. You can use <form .... method="get/post">.
When form is submitted, you can then make reference of form fields passed to action page.

Identically, you can use a straight <a href="..."> command to do this. For example:

<a href="[URL unfurl="true"]www.mysite.com/myaction.html?CompanyID=2">[/URL]
Click here to pass company ID 2 to action page
myaction.html
</a>

Again, how you read the value of CompanyID depends on the server side technology you use. I normally use CFML so I would simply look at it as #URL.CompanyID#.

Perhaps, if you tell us what you use and send a sample of code we can provide you a more straight forward answer.

Hope this helps!


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours