Conditional data display

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?

--
Dennis Gallagher
Monroe, WA, USA

Bill
Sat Jun 19 08:40:45 CDT 2004

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
bill@frontpagewiz.com
http://www.frontpagewiz.com
>-----Original Message-----
>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?
>
>--
>Dennis Gallagher
>Monroe, WA, USA
>
>
>.
>

Jim
Sat Jun 19 10:35:39 CDT 2004

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)
|/---------------------------------------------------
*----------------------------------------------------


>-----Original Message-----
>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?
>
>--
>Dennis Gallagher
>Monroe, WA, USA
>
>
>.
>

Dennis
Sat Jun 19 22:29:58 CDT 2004

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

Thanks!

--
Dennis Gallagher
Monroe, WA, USA


"Jim Buyens" <news@interlacken.com> wrote in message
news:1eee401c45613$12a950b0$a001280a@phx.gbl...
> 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)
> |/---------------------------------------------------
> *----------------------------------------------------
>
>
> >-----Original Message-----
> >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?
> >
> >--
> >Dennis Gallagher
> >Monroe, WA, USA
> >
> >
> >.
> >