How do I query a temp table in Access

[Guest]

From the following query

SELECT
Sum(IIf([daysvolume]<3,Abs([amktval]),0)/sum(abs(amktval))) AS ['1-3 Days'],
Sum(IIf([daysvolume] Between 3 And 5,[amktval],0)/sum(abs(amktval))) AS
['3-5 Days'],
Sum(IIf([daysvolume]>5,[amktval],0)/sum(abs(amktval))) AS ['>5 Days']

FROM
(SELECT Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) AS DaysVolume,
DeltaAdjMktVal as aMktVal
FROM (MainDownload INNER JOIN AverageDailyVolume ON
MainDownload.UndBloomberg = AverageDailyVolume.BBG_Ticker) INNER JOIN FxRates
ON MainDownload.Ccy = FxRates.CCY2
WHERE ((MainDownload.DeltaAdjMktVal)<>0)
ORDER BY Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) DESC) AS Liquidation;


I get the error that I cannot have an agregate function in my expression..
I have also tried dividing the sum of the days volume by another select
statement on the table [Liquidation] but I get another error message stating
the jet database does not recognise the table or query 'Liquidation' Is there
anyway I can accomplish what I want here - which in essence is finding the %
of abs(deltadjmktval) for each bucket

 

[Tom Ellison]

Dear Kidd:

I think you should consider using subqueries for the three columns [1-3
Days], [3-5 Days], and [>5 Days]. With more details, I may be able to show
you how to do this.

Now, daysvolume < 3 a range from 0 - 2 days, not 1-3? If the buckets are
really 1-3 and 3-5, wouldn't anything that is 3 days be in both buckets?
The descriptions look as though they overlap according to the descriptions.

Are you looking for a query that returns just one row?

Please show which columns come from which tables.

Tom Ellison

 

[Guest]

tom, thanks for you post

I thought as I was using < and not =< I would not be overlapping. Also days
volume are not integer's . Is there any benifit by me naming the temp table
as Liquidation - can I use it in any other way other than a simple select

Dear Kidd:

I think you should consider using subqueries for the three columns [1-3
Days], [3-5 Days], and [>5 Days]. With more details, I may be able to show
you how to do this.

Now, daysvolume < 3 a range from 0 - 2 days, not 1-3? If the buckets are
really 1-3 and 3-5, wouldn't anything that is 3 days be in both buckets?
The descriptions look as though they overlap according to the descriptions.

Are you looking for a query that returns just one row?

Please show which columns come from which tables.

Tom Ellison

From the following query

SELECT
Sum(IIf([daysvolume]<3,Abs([amktval]),0)/sum(abs(amktval))) AS ['1-3
Days'],
Sum(IIf([daysvolume] Between 3 And 5,[amktval],0)/sum(abs(amktval))) AS
['3-5 Days'],
Sum(IIf([daysvolume]>5,[amktval],0)/sum(abs(amktval))) AS ['>5 Days']

FROM
(SELECT Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) AS DaysVolume,
DeltaAdjMktVal as aMktVal
FROM (MainDownload INNER JOIN AverageDailyVolume ON
MainDownload.UndBloomberg = AverageDailyVolume.BBG_Ticker) INNER JOIN
FxRates
ON MainDownload.Ccy = FxRates.CCY2
WHERE ((MainDownload.DeltaAdjMktVal)<>0)
ORDER BY Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) DESC) AS Liquidation;


I get the error that I cannot have an agregate function in my
expression..
I have also tried dividing the sum of the days volume by another select
statement on the table [Liquidation] but I get another error message
stating
the jet database does not recognise the table or query 'Liquidation' Is
there
anyway I can accomplish what I want here - which in essence is finding the
%
of abs(deltadjmktval) for each bucket

 

[Tom Ellison]

Dear Kidd:

I was not taking any exception to what you put into your columns, but how
you labelled them. < 3 does not include 3. Why then call that column 1-3?

I asked you for details, and you gave none.

I have no information about Liquidation. You said Access complains there is
no such table or query. I don't know what you're asking about in that
respect.

Tom Ellison

tom, thanks for you post

I thought as I was using < and not =< I would not be overlapping. Also
days
volume are not integer's . Is there any benifit by me naming the temp
table
as Liquidation - can I use it in any other way other than a simple select

Dear Kidd:

I think you should consider using subqueries for the three columns [1-3
Days], [3-5 Days], and [>5 Days]. With more details, I may be able to
show
you how to do this.

Now, daysvolume < 3 a range from 0 - 2 days, not 1-3? If the buckets are
really 1-3 and 3-5, wouldn't anything that is 3 days be in both buckets?
The descriptions look as though they overlap according to the
descriptions.

Are you looking for a query that returns just one row?

Please show which columns come from which tables.

Tom Ellison

From the following query

SELECT
Sum(IIf([daysvolume]<3,Abs([amktval]),0)/sum(abs(amktval))) AS ['1-3
Days'],
Sum(IIf([daysvolume] Between 3 And 5,[amktval],0)/sum(abs(amktval))) AS
['3-5 Days'],
Sum(IIf([daysvolume]>5,[amktval],0)/sum(abs(amktval))) AS ['>5 Days']

FROM
(SELECT Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) AS DaysVolume,
DeltaAdjMktVal as aMktVal
FROM (MainDownload INNER JOIN AverageDailyVolume ON
MainDownload.UndBloomberg = AverageDailyVolume.BBG_Ticker) INNER JOIN
FxRates
ON MainDownload.Ccy = FxRates.CCY2
WHERE ((MainDownload.DeltaAdjMktVal)<>0)
ORDER BY Abs([deltaadjmktval])/([20dayavvol]*[price]/[rate]*[Enter % of
Volume]/100) DESC) AS Liquidation;


I get the error that I cannot have an agregate function in my
expression..
I have also tried dividing the sum of the days volume by another select
statement on the table [Liquidation] but I get another error message
stating
the jet database does not recognise the table or query 'Liquidation' Is
there
anyway I can accomplish what I want here - which in essence is finding
the
%
of abs(deltadjmktval) for each bucket