Is there a better way?

[Larry Rekow]

(using Access, IIS4, FP, ASP)

page 1 is a drop down list of countries, and choosing a country, users
get a results table of companies in that country. clicking on the ID
of a company they go to page 2, which has a header of the company
name, address, phone, fax, etc., and it's followed by a table of
contacts in that company with their respective e-mail addresses.

since each company can have from 1 to a dozen or more contacts, i put
the contacts in a separate Access table, and linked them by ID number
to the company table.

when i first created this page a few years ago, the only way i could
think of doing this was to have a DRW table of the contacts on the
second page, and the company info above them was passed on by
parameters in the hyperlink.

but now that i have to revise this page, i'm thinking: couldn't there
be some sort of query that would give me both the country name and
info, followed by all possible contacts, without repeating the company
info? I've created a query in Access linking the two tables, but the
info about the company appears as many times as there are contacts.
should i make two results tables? one for the company above, and a
second query/table for the contacts below?

thanks for any direction.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Thomas A. Rowe]

If you are hand coding you could use the Company ID, to look up the company
info in the first company table and also look up the associated info in the
contact table. This requires the use of two queries/recordset or you could
use the Join method.

Personally I like to use two separate queries/recordset so that I can then
easy use the individual query/recordset on other pages, etc. if needed.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Kevin Spencer]

Assuming that he has multiple companies, running multiple repeated queries
in a nested loop through the results of a first query is VERY expensive in
terms of performance. You will get much faster/better results with a JOIN
query.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Thomas A. Rowe]

Kevin, based on what was written, there would be no nested loop on the page,
only the display of the basic company data from the Company table, and a
second query to the Contact table using the same Company ID to retrieve the
contact related info to the specific company.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[MD Websunlimited]

Hi Larry,

Yes you can accomplish that by using a join provided there is a common field between the two databases. Which implies that they have
been normalized.

This is an example of joining three tables together
SELECT orders.*
FROM (orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number]) INNER JOIN products ON
product_orders.SKU = products.SKU;

 

[Kevin Spencer]

You may be right, Tom. I can't say I'm sure after re-reading his message
whether he's talking about displaying ONE company's information or a number
of them. If it is indeed one company, 2 queries wouldn't hurt much. Still, a
single JOIN is faster.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Larry Rekow]

Hi Larry,

Yes you can accomplish that by using a join provided there is a common field between the two databases. Which implies that they have
been normalized.

This is an example of joining three tables together
SELECT orders.*
FROM (orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number]) INNER JOIN products ON
product_orders.SKU = products.SKU;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks to all who have responded; it appears there is indeed a better
way, either using two record sets or using a join query. the results
i'm looking for will produce a page where i can a single company's
name and address data at the top, then a table of possible contacts at
the company (yes, each contact has the same ID as the one company ID),
and lastly, some more info about the company at the bottom from a
couple of memo fields containing some special instructions, etc.

Using the two query method, should I/can I have two live recordsets
open at once? or should i get the first recordset, populate some
variables for placing around the page, close the record set, then open
the second one to loop out the contacts? I'm getting more comfortable
with ASP now, but not sure how to run two queries in sequence; I'm
guessing I can do it from the same open connection?

And regarding the JOIN method, I'm a little intrigued about it. It
looks like a way to deal with tables that could prove more flexible
than dealing with the way Access lets you join tables in queries (i'm
guessing Access is just using a sort of pre-query behind the scenes;
what little experience i have with SQL showed me that it doesn't do
that, does it all with actual query statements, IIRC). In any case, If
I created a recordset with a JOIN query, I would still need a routine
to list the MANY contacts from the ONE company, hopefully in a small
table.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Thomas A. Rowe]

With the two recordset, just close both at the bottom of the page. Be sure
to give each recordset a unique name.

You can open as many recordset as needed (within reason).

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

Hi Larry,

Yes you can accomplish that by using a join provided there is a common field between the two databases. Which implies that they have
been normalized.

This is an example of joining three tables together
SELECT orders.*
FROM (orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number]) INNER JOIN products ON
product_orders.SKU = products.SKU;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks to all who have responded; it appears there is indeed a better
way, either using two record sets or using a join query. the results
i'm looking for will produce a page where i can a single company's
name and address data at the top, then a table of possible contacts at
the company (yes, each contact has the same ID as the one company ID),
and lastly, some more info about the company at the bottom from a
couple of memo fields containing some special instructions, etc.

Using the two query method, should I/can I have two live recordsets
open at once? or should i get the first recordset, populate some
variables for placing around the page, close the record set, then open
the second one to loop out the contacts? I'm getting more comfortable
with ASP now, but not sure how to run two queries in sequence; I'm
guessing I can do it from the same open connection?

And regarding the JOIN method, I'm a little intrigued about it. It
looks like a way to deal with tables that could prove more flexible
than dealing with the way Access lets you join tables in queries (i'm
guessing Access is just using a sort of pre-query behind the scenes;
what little experience i have with SQL showed me that it doesn't do
that, does it all with actual query statements, IIRC). In any case, If
I created a recordset with a JOIN query, I would still need a routine
to list the MANY contacts from the ONE company, hopefully in a small
table.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Kevin Spencer]

Hi Larry,

With a JOIN query, the single parent record data will be duplicated in each
row of the result set, along with the data from the second table. If you
only want to display the company info one time, you just omit that data when
looping to display the related data.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Hi Larry,

Yes you can accomplish that by using a join provided there is a common field between the two databases. Which implies that they have
been normalized.

This is an example of joining three tables together
SELECT orders.*
FROM (orders INNER JOIN product_orders ON orders.[Order Number] = product_orders.[Order Number]) INNER JOIN products ON
product_orders.SKU = products.SKU;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks to all who have responded; it appears there is indeed a better
way, either using two record sets or using a join query. the results
i'm looking for will produce a page where i can a single company's
name and address data at the top, then a table of possible contacts at
the company (yes, each contact has the same ID as the one company ID),
and lastly, some more info about the company at the bottom from a
couple of memo fields containing some special instructions, etc.

Using the two query method, should I/can I have two live recordsets
open at once? or should i get the first recordset, populate some
variables for placing around the page, close the record set, then open
the second one to loop out the contacts? I'm getting more comfortable
with ASP now, but not sure how to run two queries in sequence; I'm
guessing I can do it from the same open connection?

And regarding the JOIN method, I'm a little intrigued about it. It
looks like a way to deal with tables that could prove more flexible
than dealing with the way Access lets you join tables in queries (i'm
guessing Access is just using a sort of pre-query behind the scenes;
what little experience i have with SQL showed me that it doesn't do
that, does it all with actual query statements, IIRC). In any case, If
I created a recordset with a JOIN query, I would still need a routine
to list the MANY contacts from the ONE company, hopefully in a small
table.

Thanks,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Larry Rekow]

Hi Larry,

With a JOIN query, the single parent record data will be duplicated in each
row of the result set, along with the data from the second table. If you
only want to display the company info one time, you just omit that data when
looping to display the related data.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I actually accomplished this using both methods (two recordsets, OR
one JOIN query)

Results?

using the JOIN method (which I like and plan to keep in mind for a lot
of other projects), i got the recordset i wanted, placed the company
table info at the top where it needed to be, then where i wanted my
contacts to appear, i ran the loop that spit out the contacts i wanted
(tho not in a table...just one per line,which i think i like better)

only problem was that below the list of contacts, where i wanted to
put some additional company info, i got an error...something about the
recordset needed to be BOF or EOF or something, so I think that once i
did the loop, I couldn't reference any more of the company table.

Trying again now using the 2 recordsets method and will adivse.

thanks for your input.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Kevin Spencer]

Hi Larry,

Yes, once you've looped through the RecordSet, you can't get any data from
it (by default - let's keep this simple). However, you CAN grab that
information and put it into variables while the RecordSet is open, and then
put it anywhere in the page. Just for future reference.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Larry Rekow]

Hi Larry,

Yes, once you've looped through the RecordSet, you can't get any data from
it (by default - let's keep this simple). However, you CAN grab that
information and put it into variables while the RecordSet is open, and then
put it anywhere in the page. Just for future reference.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
yes, that thought occurred to me later. now if i can just find out how
to make one of the fields retrieved in the loop as a mailto:
hyperlink, i'll have this thing put to bed.

thanks to you and the others for all your insights.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Kevin Spencer]

<a
href="mailto:<%=RecordSetName("ColumnName").Value%>"><%=RecordSetName("Colum
nName").Value%></a>

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Larry Rekow]

<a
href="mailto:<%=RecordSetName("ColumnName").Value%>"><%=RecordSetName("Colum
nName").Value%></a>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks Kevin. But I can't seem to get it to work. Here is the
statement as is...can you show me how to integrate the href part?
thanks.

While Not crs.EOF
Response.Write crs("firstname")& " " & crs("lastname")& ", "&
crs("title") & " " & crs("email")& "<br>"
crs.MoveNext

Larry


- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Thomas A. Rowe]

Larry,

Instead of using Response.Write, try:

While Not crs.EOF
<%=crs("firstname")%>&nbsp;<%=crs("lastname")%>,&nbsp;<%=crs("title")%>&nbsp
;<a href="mailto:<%=crs("email")%>"><%=crs("email")%></a><br>
crs.MoveNext

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Larry Rekow]

Larry,

Instead of using Response.Write, try:

While Not crs.EOF
<%=crs("firstname")%>&nbsp;<%=crs("lastname")%>,&nbsp;<%=crs("title")%>&nbsp
;<a href="mailto:<%=crs("email")%>"><%=crs("email")%></a><br>
crs.MoveNext

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
looks right....but it doesn't run.

if you put a <% in front of your statements, and a %> afterwards, it
doesn't seem to look right in my frontpage page. the last part is
black instead of brown, if you know what i mean. is something left
out?

thanks,

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Thomas A. Rowe]

Sorry.

Do/Try..


<% While Not crs.EOF %>
<%=crs("firstname")%>&nbsp;<%=crs("lastname")%>,&nbsp;<%=crs("title")%>&nbsp
;<a href="mailto:<%=crs("email")%>"><%=crs("email")%></a><br>
<% crs.MoveNext %>

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Larry Rekow]

Sorry.

Do/Try..


<% While Not crs.EOF %>
<%=crs("firstname")%>&nbsp;<%=crs("lastname")%>,&nbsp;<%=crs("title")%>&nbsp
;<a href="mailto:<%=crs("email")%>"><%=crs("email")%></a><br>
<% crs.MoveNext %>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
works like a dream! (just had to add the "Wend" at the end. Your idea
about using the two recordsets worked well for what I was doing, too.

This is the way I did it....tho i'm not too sure if it's the most
efficient way...and of course i'll kill the recordsets at the end and
set them to nothing.

strID = Request.QueryString("ID")
Set ars = Server.CreateObject("ADODB.RecordSet")
Set crs = Server.CreateObject("ADODB.RecordSet")
aq = "SELECT * FROM agents1 WHERE ID = "&strID&""
cq = "SELECT firstname,lastname,title,email FROM contacts WHERE ID =
"&strID&""
ars.Open aq, agents
crs.Open cq, agents

Thanks again Thomas!

Larry

- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

 

[Thomas A. Rowe]

Larry,

Great!

I basically set the recordset up completely separate, so that I can easily
re-used/copy them to other pages, if needed.

But your way works the same.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[MD Websunlimited]

Ouch. The single response.write is much better than the implied multiple statements being used here.


--
Mike -- FrontPage MVP '97 - '02
http://www.websunlimited.com
Our latest products "At Your Command" and IncludeASP
http://www.websunlimited.com/order/Product/AYC/ayc.htm
http://www.websunlimited.com/order/product/includeASP/includeASP.htm