complex query/report

[Ruth]

Hi there

I don't know if this is possible or not... I want to be able to create a
query or a report that will give an average for the unload rates at each port
for each vessel.

I have a table that has 3 fields for Unload ports for a 3 letter port code.
I have a query that has a formula to figure out the unload rates (net tonnes
per hour)

each port could be in either of the 3 unload port fields. There are over 60
ports. In the end I would like something that looked similar to

Vessel: AAAA
Port Average Unload
ABA 1200
ACC 600
AFF 1600
..
..
..
Vessel: BBB
Port Average Unload
ABA 1600
ACC 500
AFF 2600
..
..
..
All Vessels
Port Average Unload
ABA 1700
ACC 800
AFF 2700

Please let me know if this is possible and if so, how to do it.

 

[KARL DEWEY]

each port could be in either of the 3 unload port fields.
Change your query so that the output looks like this --
Vessel Port Average Unload
Then create a report grouping on vessel and put vessel in group header.

 

[Ruth]

Hi Karl

How do I set it up in the query so that it gives the average unload for each
port- rather than each of the 3 fields that contain the port?

When I set up a report with grouping the vessel it lists each unload rate
seperately, so by the end of the season I would have 100s of them, but only
want one for each port under each vessel.

Hope the explanation made sense.
Sorry, but I am lost here and need further details of how it is done.

 

[KARL DEWEY]

What is your table structure? Table name?
Field name Datatype
Field name Datatype
Field name Datatype
Field name Datatype

 

[Ruth]

Hi Karl

The data comes from a table called "data"

Most of the fields are text or number
Field Name Datatype
Year Number
VesselName Text
SCACCode Numer
..
..
..
-- I have formulas in the queries to figure out the unload time and the
unload rates -- the formulas are:

U1Unloadhours: DateDiff("n",[Data]![U2CommenceLoad],[Data]![U2FinishLoad])/60

U1UnloadRate: (Data![1stUnload Tonnage]/[U1Unloadhours])


Hope this all

 

[KARL DEWEY]

You gave me a few of the pieces and I made assumptions on the rest.
Try this ---
SELECT Data.VesselName, Data.Port, Avg(([Data]![1stUnload
Tonnage]/(DateDiff("n",[Data]![U2CommenceLoad],[Data]![U2FinishLoad])/60)))
AS AVGUnloadRate
FROM Data
GROUP BY Data.VesselName, Data.Port;

--
KARL DEWEY
Build a little - Test a little

Hi Karl

The data comes from a table called "data"

Most of the fields are text or number
Field Name Datatype
Year Number
VesselName Text
SCACCode Numer
.
.
.
-- I have formulas in the queries to figure out the unload time and the
unload rates -- the formulas are:

U1Unloadhours: DateDiff("n",[Data]![U2CommenceLoad],[Data]![U2FinishLoad])/60

U1UnloadRate: (Data![1stUnload Tonnage]/[U1Unloadhours])


Hope this all
--
Thank-you!
Ruth

What is your table structure? Table name?
Field name Datatype
Field name Datatype
Field name Datatype
Field name Datatype