Expression query

[Adrian1962]

I am importing an array of information from excel, and I am stuck with
respect to replicating the same result within access on the query grid.

I have four columns representing daily data. And I group data by calendar
periods to obtain a weighted calendar HDD result by calendar period. See
below:

Date Use HDD Wgt avg HDD
Jan 2,07 100 2 (100/600)*2=.3333
Jan 3,07 200 3 (200/600)*3=.9999
Jan 4,07 300 4 (300/600)*4=2.00
Feb 2,07 200 2 (200/1,600)*2=.250
Feb 3,07 600 3 (600/1,600)*3=1.125
Feb 4,07 800 4 (800/1,600)*4=2.00

I'm open to breaking up my table(normalization), just need help.

Thanks.

 

[John Spencer]

The Sql for that might look like the following.

SELECT HDD*Use/(SELECT Sum(Tmp.Use)
FROM YourTable as Tmp
WHERE Tmp.Date between
DateSerial(Year(YT.Date),Month(YT.Date),1)
and DateSerial(Year(YT.Date),Month(YT.Date)+1,0))
, YT.Date, YT.USE, YT.HDD
FROM YourTable As YT

Or do this is two queries - which could be faster
First query sum the total of use by month.

SELECT Format([Date], "YYYYMM") as YearMonth
, SUM(Use) as TotalUse
FROM YourTable
GROUP BY Format([Date], "YYYYMM")

Now use that saved query in a join with you original table

SELECT HDD * Use / TotalUse
FROM YourTable INNER JOIN QSavedQuery
ON Format(YourTable.Date,"YYYYMM") = qSavedQuery.YearMonth


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

Although you have imported data from Excel, you are not "stuck with" the
structure.

From your description, the first three columns are actual data, while the
fourth is a "calculated" value. There's rarely a need to store calculated
values in a relational database.

But something confuses me ... the first three calculated values use "600"
for a denominator, while the next three use "1,600" as a denominator. Where
is this denominator coming from? Why isn't it part of the data being
imported?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Adrian1962]

Although you have imported data from Excel, you are not "stuck with" the
structure.

From your description, the first three columns are actual data, while the
fourth is a "calculated" value. There's rarely a need to store calculated
values in a relational database.

But something confuses me ... the first three calculated values use "600"
for a denominator, while the next three use "1,600" as a denominator. Where
is this denominator coming from? Why isn't it part of the data being
imported?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/



Hello Jeff,

The unique denominator is due to the calendar month grouping. Jan sums to
600 and Feb sums to 1,600. Hope this answers the question.

 

[Jeff Boyce]

All the more reason, then, not to store the calculated value(s)! If one of
the entries during a month is changed, that row's (calculated) value would
have to be changed, and so would ALL the calculated values for any other
rows involving that month.

Again, don't store the calculated value, use a query to determine it.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/