Subquery not working

G

Guest

I am working in Access 97. I have the following query:

SELECT tblPromo.UPCFip13, tblProd.ProdDesc, tblPromo.PromoNo, tblPromo.UOM,
tblPromo.StartDate, UCase(Mid([StartWkdy],1,2)) AS [START DAY],
tblPromo.EndDate, UCase(Mid([EndWkdy],1,2)) AS [END DAY], tblPromo.Price,
tblPromo.Factor, tblPromo.Type
FROM tblPromo INNER JOIN tblProd ON tblPromo.UPCFip13 = tblProd.UPCFip13
WHERE (((tblPromo.PromoNo)=(SELECT DISTINCT tblPromo.PromoNo FROM tblPromo))
AND ((tblPromo.StartDate)>=Date()))
ORDER BY tblPromo.StartDate;

I am having problems with

SELECT DISTINCT tblPromo.PromoNo FROM tblPromo

It says it will only return one record when I know there are 428 records
that should be returned. The bottom line is that I only want one Promo No
returned per line. Note: If I remove this criteria the query runs just fine
but returns me multiple Promo nos.

The unique value property doesn't work because there might be a variety of
UPC's (Item nos.) Any help would be appreciated.
 
G

Guest

Hi,
Try this for your subquery:

SELECT tblPromo.PromoNo FROM tblPromo group by tblPromo.PromoNo;
 
G

Guest

The same error occurs when I use your query as my sub query. Any other help
would be appreciated.
--
M. Shipp


Chris said:
Hi,
Try this for your subquery:

SELECT tblPromo.PromoNo FROM tblPromo group by tblPromo.PromoNo;

SHIPP said:
I am working in Access 97. I have the following query:

SELECT tblPromo.UPCFip13, tblProd.ProdDesc, tblPromo.PromoNo, tblPromo.UOM,
tblPromo.StartDate, UCase(Mid([StartWkdy],1,2)) AS [START DAY],
tblPromo.EndDate, UCase(Mid([EndWkdy],1,2)) AS [END DAY], tblPromo.Price,
tblPromo.Factor, tblPromo.Type
FROM tblPromo INNER JOIN tblProd ON tblPromo.UPCFip13 = tblProd.UPCFip13
WHERE (((tblPromo.PromoNo)=(SELECT DISTINCT tblPromo.PromoNo FROM tblPromo))
AND ((tblPromo.StartDate)>=Date()))
ORDER BY tblPromo.StartDate;

I am having problems with

SELECT DISTINCT tblPromo.PromoNo FROM tblPromo

It says it will only return one record when I know there are 428 records
that should be returned. The bottom line is that I only want one Promo No
returned per line. Note: If I remove this criteria the query runs just fine
but returns me multiple Promo nos.

The unique value property doesn't work because there might be a variety of
UPC's (Item nos.) Any help would be appreciated.
Click to expand...
Click to expand...
 
M

[MVP] S.Clark

The sub query is only allowed to return one value, or else it can not be
used a subquery. Thus you will need to restrict the result of the subquery.
Check out the term Correlated Subquery for more information.

The A97 help file is actually pretty useful for this, so check there first.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
V

Van T. Dinh

Both SubQueries returns a Recordset with possibly multiple rows which is not
suitable for your criteria.

AFAIK, it doesn't do anything (if it works). Assume that you actually meant
IN or EXISTS instead of =, every [tblPromo].[PromoNo] is in the returned
rows of the SubQuery and therefore all rows in tblPromo will be selected
subjected to the restriction of the INNER JOIN & the date criterion.

Which Table do [StartWkdy] and [EndWkdy] come from?
 
Top