SQL help?

[Gil Lopes via AccessMonster.com]

Hi all!

I' m trying to use SQL to obtain 4 series of percentages from a table.

My SQL looks like this:

SELECT UPsAbertos.Data, ((select UPsAbertos.Cotação from UpsAbertos where
UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido')/5-1) AS Gar, (
(select UPsAbertos.Cotação from UpsAbertos where UpsAbertos.[nome do fundo]
='SGF Património Reforma Prudente')/1.09640-1) AS Pru, ((select UPsAbertos.
Cotação from UpsAbertos where UpsAbertos.[nome do fundo]='SGF Património
Reforma Equilibrado')/5-1) AS Equ,((select UPsAbertos.Cotação from UpsAbertos
where UpsAbertos.[nome do fundo]='SGF Património Reforma Acções')/5-1) AS Acc
FROM UPsAbertos
WHERE (((UPsAbertos.Data)>=#12/31/2002# And (UPsAbertos.Data)<=[forms]!
[cartaz]![cartazdata]))
ORDER BY UPsAbertos.Data DESC;

UpsAbertos is a table where I have the prices for these 4 products SGF Pat...
It has 3 fields: Data (date), Nome do Fundo (product ID) and Cotação (price).
So, this query should return the percentage evolution of the 4 products
ordered by date.
This SQL is giving me the error "This Subquery can only return one result",
and doesn' t run.
Can anyone see where I' m going wrong?

Many thanks,

Gil

 

[John Spencer]

Each of the Sub-Queries in the main queries SELECT clause must return a
single value in a single field. Access determines that you are returning a
single value when you use an aggregate or totals query. So perhaps you can
change your subQuery to use The MAX function

(SELECT Max(UPsAbertos.Cotação) from UpsAbertos
WHERE UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido')

You may have to expand the WHERE criteria depending on which price you are
looking to return. For example, if the price is data sensitive, you may
need that criteria in the subqueries.

 

[Gil Lopes via AccessMonster.com]

Hummm...
Got my mistake! My subquery must return only one value.

Anyway, max function will not work, I guess.
I want to divide a price (relative to a precise date) for another given and
static price.
But I want this to be a time series.
So, my first query field will select the dates I want.
The others fields should relate to that date, dividing the price of that date
by the static price (wich is 31-12-2002).
So, as you said, I will have to expand the criteria of my WHERE clause, but I
want it to refer to my first field on the query. But when I do so, it keeps
asking me for that date.

How should I expand the WHERE clause to do that. Should I start by SELECT
date AS XXX and then expand the WHERE clause like "where (...) and upsabertos.
data=XXXX ?

Did I made that clear? Sorry! Guess it is a bit confusing...

Each of the Sub-Queries in the main queries SELECT clause must return a
single value in a single field. Access determines that you are returning a
single value when you use an aggregate or totals query. So perhaps you can
change your subQuery to use The MAX function

(SELECT Max(UPsAbertos.Cotação) from UpsAbertos
WHERE UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido')

You may have to expand the WHERE criteria depending on which price you are
looking to return. For example, if the price is data sensitive, you may
need that criteria in the subqueries.

Hi all!

[quoted text clipped - 33 lines]

Gil

 

[John Spencer]

I'm sorry, but I don't understand the question/explanation.

Hummm...
Got my mistake! My subquery must return only one value.

Anyway, max function will not work, I guess.
I want to divide a price (relative to a precise date) for another given
and
static price.
But I want this to be a time series.
So, my first query field will select the dates I want.
The others fields should relate to that date, dividing the price of that
date
by the static price (wich is 31-12-2002).
So, as you said, I will have to expand the criteria of my WHERE clause,
but I
want it to refer to my first field on the query. But when I do so, it
keeps
asking me for that date.

How should I expand the WHERE clause to do that. Should I start by SELECT
date AS XXX and then expand the WHERE clause like "where (...) and
upsabertos.
data=XXXX ?

Did I made that clear? Sorry! Guess it is a bit confusing...

Each of the Sub-Queries in the main queries SELECT clause must return a
single value in a single field. Access determines that you are returning
a
single value when you use an aggregate or totals query. So perhaps you
can
change your subQuery to use The MAX function

(SELECT Max(UPsAbertos.Cotação) from UpsAbertos
WHERE UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido')

You may have to expand the WHERE criteria depending on which price you are
looking to return. For example, if the price is data sensitive, you may
need that criteria in the subqueries.

Hi all!

[quoted text clipped - 33 lines]

Gil

 

[Gil Lopes via AccessMonster.com]

Ok...

What I' m trying to do:

Product1 product2
date 1 % %
date 2 % %

So, for every %, I have to calculate a price which is static (31-12-2002),
and a variable price wich is defined by date 1, 2, etc.

How do I refer to the date field in by where clause?

((SELECT UPsAbertos.Cotação FROM UpsAbertos WHERE
UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido' AND UpsAbertos.
Data=[Date 1, 2...])/5-1)

How can I do so that my WHERE clause picks up the value on my "field 1/date
1" field?

I tryed it, but it doesn't seem to work.
That would make that the result of my subquery would have one, and only one,
result possible.

Thanks for your help!!!

I'm sorry, but I don't understand the question/explanation.

Hummm...
Got my mistake! My subquery must return only one value.

[quoted text clipped - 40 lines]

 

[John Spencer]

Try Aliasing the tablename and using a coordinated subquery.

((SELECT A.Cotação
FROM UpsAbertos as A
WHERE
A.[nome do fundo]='SGF Património Reforma Garantido'
AND A.Data=UpsAbertos.[Data])/5-1)

Unfortuneately, although this may return one record in the sub-query Access
will still complain. It doesn't check to see if multiple records are
returned, it simply assumes that multiple records can be returned and
generates an error.

The solution is to use one of the aggregate functions.
((SELECT First(A.Cotação) as MyCotação
FROM UpsAbertos as A
WHERE
A.[nome do fundo]='SGF Património Reforma Garantido'
AND A.Data=UpsAbertos.[Data])/5-1)

If that doesn't solve it, this may be a case where you will have to use
multiple queries and a master query to combine them. Or you may even have
to result to a temporary table.

Ok...

What I' m trying to do:

Product1 product2
date 1 % %
date 2 % %

So, for every %, I have to calculate a price which is static (31-12-2002),
and a variable price wich is defined by date 1, 2, etc.

How do I refer to the date field in by where clause?

((SELECT UPsAbertos.Cotação FROM UpsAbertos WHERE
UpsAbertos.[nome do fundo]='SGF Património Reforma Garantido' AND
UpsAbertos.
Data=[Date 1, 2...])/5-1)

How can I do so that my WHERE clause picks up the value on my "field
1/date
1" field?

I tryed it, but it doesn't seem to work.
That would make that the result of my subquery would have one, and only
one,
result possible.

Thanks for your help!!!

I'm sorry, but I don't understand the question/explanation.

Hummm...
Got my mistake! My subquery must return only one value.

[quoted text clipped - 40 lines]

Gil