Conditional data display

[Dennis]

I'm using ASP to display data from my Access DB and I'm stumped about how to
do the following:

I have two tables. One lists nursery stock and the other lists locations
where each stock item can be found. I think I understand how to use the
Count operator to present a column which shows how many locations exist for
each stock item but what I really want to do is to present a column that
says "Out of stock" if there are no locations and shows how many locations
there are if there are one or more.

Can anyone give me a push in the right direction?

 

[Bill Schroyer]

Create a query (view) in access to count your locations.
Use the FrontPage Database Results Wizard to display the
query. You can position the new results relative to your
main DRW using layers, or an iframe. Change the "No
Records Returned" to "Out of Stock".

Bill Schroyer
(e-mail address removed)
http://www.frontpagewiz.com

 

[Jim Buyens]

Have the DRW use a custom query, and code that query
along the lines of:

SELECT stock.stockid,
stock.stock_description,
IIf(Count(locations.locid) > 0,
Count(locations.locid),
"Out of stock") AS CountOflocid
FROM stock LEFT JOIN locations
ON stock.stockid = locations.stockid
GROUP BY stock.stockid, stock.stock_description
ORDER BY stock.stock_description;

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Dennis]

Yeh, Jim. That sounds like what I'm looking for!

Thanks!