Complex Analytical Query help please

[Bruce]

Hi,

I have a complex analytical query that is WAY beyond my SQL capabilities and
would appreciate any expert help I can get on this. The table of records I
want to process is large (2.6million records) and will require considerable
processing time. Ultimately I want to append the query results to a table
called tbl_temp. If it helps, I can send some sample data (small sample) of
what I want to process (from tbl_ASX_Data). I can demonstrate my desired
results in Excel if this helps.

Not sure if this is a SQL or VBA solution but happy with help on either.


My table information….

Table name:
tbl_ASX_data

Fields:
ASXCode: This contains my item/product code of type text
ImportDate: This is a text field in date yyyymmdd format
Close: Value of type numeric
Volume: Value of type numeric

n.b Primary Key is concatenated with ASXCode and ImportDate fields.




What I want to do….

For each ASXCode I want to calc the following;

Append the results of the 12 month average of the daily Close * Volume for
each element of ASXCode.

1) For each record calculate Close * Volume
2) Then calculate the average of 1) over the last 12 month for each ASXCode.
i.e. Convert the ImportDate field to a DateValue and work out the range of
records within 1 year and for each ASXCode calculate the average of these
results
3) Append results to table tbl_temp.


Greatly appreciate any help in solving this one cause I will never get it
myself.

Bruce

 

[Mark J]

why not use a pivot table in excel, then push the data into access, much
easier, access will choke on processing that many records. If you need to
automate this process...IE... this processing need to be done daily,
weekly...you can pull the data into excel using ODBC, ADO, DAO, or any other
way you want then access can link to that pivot table.

 

[Tom Ellison]

Dear Bruce:

Well, your description is not completely clear to me. I'll put some
questions inside your comments below:

Hi,

I have a complex analytical query that is WAY beyond my SQL capabilities
and
would appreciate any expert help I can get on this. The table of records I
want to process is large (2.6million records) and will require
considerable
processing time. Ultimately I want to append the query results to a table
called tbl_temp. If it helps, I can send some sample data (small sample)
of
what I want to process (from tbl_ASX_Data). I can demonstrate my desired
results in Excel if this helps.

Not sure if this is a SQL or VBA solution but happy with help on either.


My table information..

Table name:
tbl_ASX_data

Fields:
ASXCode: This contains my item/product code of type text
ImportDate: This is a text field in date yyyymmdd format
Close: Value of type numeric
Volume: Value of type numeric

n.b Primary Key is concatenated with ASXCode and ImportDate fields.

You're saying that the PK is on two columns. ASXCode and ImportDate taken
together are unique. Fair enough.

What I want to do..

For each ASXCode I want to calc the following;

Append the results of the 12 month average of the daily Close * Volume for
each element of ASXCode.

OK, now define 12 months. Do you want a calendar year, or some other annual
basis. How will you designate each year? Like this? 2006, 2007, 2008

1) For each record calculate Close * Volume
2) Then calculate the average of 1) over the last 12 month for each
ASXCode.
i.e. Convert the ImportDate field to a DateValue and work out the range of
records within 1 year and for each ASXCode calculate the average of these
results

So "Close" is perhaps a closing quotation for the day. Multipy by the
Volume on that day to get an extended "Dollar Volume" figure for the day.

3) Append results to table tbl_temp.

Well, I'll just try to get the results first. Transforming it into an
INSERT query will be a last step.

Greatly appreciate any help in solving this one cause I will never get it
myself.

Bruce

Well, I'll take an initial whack at it now, assuming a calendar year basis.

OK, a first hurdle. Do we include all days posted only? What I mean is, if
the "market" is closed Saturday and Sunday, do we use the Close * Volume
from Friday for Saturday and Sunday as well, thus counting it 3 times in the
average, giving the closing values before an "off" day extra weight?
Possibly not, but it is a consideration. I'll assume you do not wish to do
this. It would be a considerable bit of work, and requires a calendar
table.

So, proceeding on that new assumption:

SELECT ASXCode, Year(ImportDate) AS Year, SUM(Close * Volume) AS
AverageDollarVolume
FROM tbl_ASX_data
GROUP BY ASXCode, Year(ImportDate)

I have chosen the simplest replies to your questions. We can add nasty
complexity later if needed. Please let me know what happens, and if you
need more.

Tom Ellison
Microsoft Access MVP