Create a New Table From Calculated Data

  • Thread starter Thread starter KostasP via AccessMonster.com
  • Start date Start date
K

KostasP via AccessMonster.com

Hi everyone,
I've been experiencing the following problem,
i want to create a table which will be populated by values from calculated
data of a module.

But first let me explain the situation:
I have a table with vessel characteristics from a certain range of years and
through a module i calculate certain values (Average Length, Power e.c.t.)
for each year from '91 to '07. These data which now i display them to a text
box of a form, i want to get them to a new table from where i can copy them
to excel spreadsheets and create charts (either on Access or Excel).
Do you have any ideas on how to do that?
Please
Any help would be welcomed

KostasP
 
Kostas

You may not need to bother with the step of creating a new table.

If you can create (i.e., calculate) the values you need via a query, you can
export that query (actually, the query's results) to a spreadsheet without
ever creating a new table. You described using a module to do the
calculation. If you'll post the code in that function, the newsgroup
readers may be able to offer a way to use a query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
You might also want to consider, once you have your query written, using an
Access chart report to graph your data. No need to export to Excel, unless
you have a need to send the data and charts to someone.

Dale
 
Well thanks for the advice but the problem is that i can't get with a query
the amount of values that i want (1991 to 2007) but only a year's values. So
i had to use 17 queries (each per year). That led me to make the following
code:

For nb = 1991 To 2007


sq = "SELECT Count (CountryCode) AS [VNB], "
sq = sq + "Avg(KAM.[Loa]) AS [ML], "
sq = sq + "StDev(KAM.[Loa]) AS [SDL], "
sq = sq + "Sum(KAM.[Ton Ref]) AS [SumGT],"
sq = sq + "Avg(KAM.[Power Main]) AS [MKW], "
sq = sq + "StDev(KAM.[Power Main]) AS [SDKW], "
sq = sq + "Avg(KAM.[Construction Year]) AS [MCY], "
sq = sq + "StDev(KAM.[Construction Year]) AS [SDCY], "
sq = sq + "" + Str(nb) + " As [Year]"
sq = sq + "FROM [KAM Prefecture] INNER JOIN ([KAM Port] INNER JOIN
KAM ON ([KAM Port].[Port Name] = KAM.[Port Name]) "
sq = sq + "AND ([KAM Port].[Port Code] = KAM.[Port Code])) ON [KAM
Prefecture].Prefecture = [KAM Port].Prefecture"
sq = sq + " WHERE "
sq = sq + sport
sq = sq + "AND KAM.ComYear<=" + Str(nb) + " AND KAM.EventEndDate>=" +
Str(nb) + "9999"
sq = sq + sgear
sq = sq + " AND KAM.Loa>=" + Str(slow) + "AND KAM.Loa<=" + Str(shi)

Set qst = dbs.OpenRecordset(sq)


sloaAvg = sloaAvg + ", " + Str(qst!ML)
sloaSD = sloaSD + "," + Str(qst!SDL)
sPowerAvg = sPowerAvg + "," + Str(qst!MKW)
sPowerSD = sPowerSD + "," + Str(qst!SDKW)
sConYear = sConYearSD + "," + Str(qst!MCY)
sConYearSD = sConYearSD + "," + Str(qst!SDCY)

Next nb

Right now i export these values ( sloaAvg , sloaSD e.c.t) on a form's a
textbox but it it hard for me to manipulate the values. I have to copy them
to an excel and then make charts and everything.
Any suggestions?
 
Kostas,

It looks like what you need to do is an aggregate query where you Group by
the ComYear field. By the looks of your WHERE clause, and your looping
through the various years, it looks like you are attempting to generate
statics that relate to each of the years, but only include the records for
each year (ComYear) where the EventEndDate (is this field a string in the
format 'YYYYMMDD'?) is in the year following the ComYear. Is that
assessment correct?

The following query might meet your needs, and prevent you from having to
run this 17 times and concatenate the results. Try a SQL statement that
looks like:

SELECT Kam.ComYear,
Count (CountryCode) AS [VNB],
Avg(KAM.[Loa]) AS [ML],
StDev(KAM.[Loa]) AS [SDL],
Sum(KAM.[Ton Ref]) AS [SumGT],
Avg(KAM.[Power Main]) AS [MKW],
StDev(KAM.[Power Main]) AS [SDKW],
Avg(KAM.[Construction Year]) AS [MCY],
StDev(KAM.[Construction Year]) AS [SDCY]
FROM [KAM Prefecture] INNER JOIN
([KAM Port] INNER JOIN KAM
ON ([KAM Port].[Port Name] = KAM.[Port Name])
AND ([KAM Port].[Port Code] = KAM.[Port Code]))
ON [KAM Prefecture].Prefecture = [KAM Port].Prefecture
WHERE KAM.ComYear BETWEEN 1991 AND 2007
AND VAL(LEFT(KAM.EventEndDate, 4)) > [KAM].ComYear
GROUP BY KAM.ComYear

Now here is where you lose me. What are 'sport', 'sgear', 'slow', and 'shi'
values? If you are confident that these values contribute to the WHERE
clause, then by all means, insert them into the above query.

HTH
Dale

KostasP via AccessMonster.com said:
Well thanks for the advice but the problem is that i can't get with a
query
the amount of values that i want (1991 to 2007) but only a year's values.
So
i had to use 17 queries (each per year). That led me to make the following
code:

For nb = 1991 To 2007


sq = "SELECT Count (CountryCode) AS [VNB], "
sq = sq + "Avg(KAM.[Loa]) AS [ML], "
sq = sq + "StDev(KAM.[Loa]) AS [SDL], "
sq = sq + "Sum(KAM.[Ton Ref]) AS [SumGT],"
sq = sq + "Avg(KAM.[Power Main]) AS [MKW], "
sq = sq + "StDev(KAM.[Power Main]) AS [SDKW], "
sq = sq + "Avg(KAM.[Construction Year]) AS [MCY], "
sq = sq + "StDev(KAM.[Construction Year]) AS [SDCY], "
sq = sq + "" + Str(nb) + " As [Year]"
sq = sq + "FROM [KAM Prefecture] INNER JOIN ([KAM Port] INNER JOIN
KAM ON ([KAM Port].[Port Name] = KAM.[Port Name]) "
sq = sq + "AND ([KAM Port].[Port Code] = KAM.[Port Code])) ON [KAM
Prefecture].Prefecture = [KAM Port].Prefecture"
sq = sq + " WHERE "
sq = sq + sport
sq = sq + "AND KAM.ComYear<=" + Str(nb) + " AND KAM.EventEndDate>="
+
Str(nb) + "9999"
sq = sq + sgear
sq = sq + " AND KAM.Loa>=" + Str(slow) + "AND KAM.Loa<=" + Str(shi)

Set qst = dbs.OpenRecordset(sq)


sloaAvg = sloaAvg + ", " + Str(qst!ML)
sloaSD = sloaSD + "," + Str(qst!SDL)
sPowerAvg = sPowerAvg + "," + Str(qst!MKW)
sPowerSD = sPowerSD + "," + Str(qst!SDKW)
sConYear = sConYearSD + "," + Str(qst!MCY)
sConYearSD = sConYearSD + "," + Str(qst!SDCY)

Next nb

Right now i export these values ( sloaAvg , sloaSD e.c.t) on a form's a
textbox but it it hard for me to manipulate the values. I have to copy
them
to an excel and then make charts and everything.
Any suggestions?
Click to expand...
 
Dear Dale,
First of all thanks for the try that was great news for me.
I think you've got it all through except the fact of the (Com Year).
Com Year (Commission Year) is the year that the boat entered the fleet, so i
want each year the sum of the records (boats) that entered until [Com Year]
(<=) and exit after the year i want [EventEndDate].
I don't think that BETWEEN does that and correct me if i'm wrong.
Any other suggestions?
As for the rest of it (sloaAvg e.c.t forget about it)

Thanks in advance!
Kostas
 
Kostas,

We are getting close.

For future reference, it would have helped if you had given this "problem
description" right up front. I helps us understand what you are trying to
accomplish. That, along with your data structure helps us to help you write
the appropriate query.

On closer examination, of your query, I see no reason to include the [KAM
Prefecture] and [KAM Port] tables in this query, unless they were referenced
in the 'sport', 'sgear', 'slow', and 'shi' text that you originally had in
your WHERE clause, or one of them has the [CountryCode] field in it, and the
only way to get at that is through this join.

I think this is the way I would handle this.

Create a new query (qry_ComYear) that looks like:

SELECT DISTINCT ComYear
FROM KAM
WHERE ComYear BETWEEN 1991 and 2007

You could just create a table for this purpose if you wanted to.

Now, add that query to the query grid for the query I wrote last night, but
don't join it to anything.

Change the first entry of the select statement to read:

SELECT qry_ComYear.ComYear

Then change the WHERE and GROUP BY clauses to read:

WHERE KAM.ComYear < qry_ComYear.ComYear
AND VAL(LEFT(KAM.EventEndDate, 4)) > qry_ComYear.ComYear
GROUP BY qry_ComYear.ComYear

HTH
Dale
 
Back
Top