Query multiples values

G

Guest

Hi,

I have tried explaining this problem before without success, so I'll try again.

TblTransactions
From Suburb
To Suburb
Service
Items
Kilos

TblPostcode
Suburb
Direct Port

QryTo
generates the direct port.....To

QryFro
generates the direct port....from

QryRFP
takes the "To" & "From" direct ports and sums the items and kios

This works fine until I have:

A multiple suburb to suburb.....Mel to Syd ( if there are 2 entries, the result for items & kilos are doubled)

or I have same suburb to suburb Mel to Mel this seems to multiply by 4.

What am I doing wrong...this is the sql view:

SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To], Sum(TblTransactions!Items) AS Items, Sum(TblTransactions!Kilograms) AS Kilograms
FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] = TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON QryTo.[To Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] = [To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

Help!

harry
 
D

Dale Fye

Harry,

My guess is that your Postal Code table contains multiple values for several
of the suburbs (more than one direct port per Suburb). Since I'm not sure
what a Direct Port is, I'm not certain. If you could post the SQL for the
other queries, it might make this easier.

Dale

Harry Bo said:
Hi,

I have tried explaining this problem before without success, so I'll try again.

TblTransactions
From Suburb
To Suburb
Service
Items
Kilos

TblPostcode
Suburb
Direct Port

QryTo
generates the direct port.....To

QryFro
generates the direct port....from

QryRFP
takes the "To" & "From" direct ports and sums the items and kios

This works fine until I have:

A multiple suburb to suburb.....Mel to Syd ( if there are 2 entries, the
Click to expand...
result for items & kilos are doubled)
or I have same suburb to suburb Mel to Mel this seems to multiply by 4.

What am I doing wrong...this is the sql view:

SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To],
Click to expand...
Sum(TblTransactions!Items) AS Items, Sum(TblTransactions!Kilograms) AS
Kilograms
FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] =
Click to expand...
TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON
QryTo.[To Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] =
[To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

Help!

harry
Click to expand...
 
G

Gary Walter

Hi Harry,

Originally you said:

****quote*****
I have a table with fields thus:

From To Items Kilos Charge

Mel Syd 5 25 27.50
Mel Bne 3 11 12.5
Mel Syd 3 5 22.50

When I run the query it gives me Mel - Syd 16 (items) 60 (Kilos) 100 (Charge)
***unquote****

That's quite a bit different than:

SELECT
QryFro.[Direct Port] AS [From],
QryTo.[Direct Port] AS [To],
Sum(TblTransactions.Items) AS Items,
Sum(TblTransactions.Kilograms) AS Kilograms
FROM
(QryFro
INNER JOIN TblTransactions
ON QryFro.[From Suburb] = TblTransactions.[From Suburb])
INNER JOIN
(QryTo
INNER JOIN [To Suburb]
ON QryTo.[To Suburb] = [To Suburb].SubPost)
ON TblTransactions.[To Suburb] = [To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

all the INNER JOINS are causing the multiplicative effect...
where does [To Suburb].SubPost come from and why
do you need it as part of the joins?
what are the SQL views of QryTo and QryFro (and
[ToSuburb] if it is a query)?

What would be wrong with summing only with
TblTransaction in a query, then joining TblPostcode
to the results of that query to get your [DirectPost]
(using judicious JOIN(s))?

qrySum:

SELECT
[From Suburb] As TransFrom,
[To Suburb] As TransTo,
Sum([Items]) As SumItems,
Sum([Kilos]) As SumKilos
FROM
TblTransactions
Group By
[From Suburb],
[To Suburb];

query to get [DirectPort]'s:

SELECT
SumItems,
SumKilos,
(Select t1.[DirectPort]
FROM
TblPostCode As t1
WHERE
t1.Suburb = qrySum.TransFrom) As [From],
(Select t2.[DirectPort]
FROM
TblPostCode As t2
WHERE
t2.Suburb = qrySum.TransTo) As [To]
FROM
qrySum;

Otherwise, I don't see any way to help
without seeing all your views, and sample data
for all your tables and their structure (like is
Suburb "unique-no duplicates" in TblPostcode).
I know that sucks, but


I have tried explaining this problem before without success, so I'll try again.

TblTransactions
From Suburb
To Suburb
Service
Items
Kilos

TblPostcode
Suburb
Direct Port

QryTo
generates the direct port.....To

QryFro
generates the direct port....from

QryRFP
takes the "To" & "From" direct ports and sums the items and kios

This works fine until I have:

A multiple suburb to suburb.....Mel to Syd ( if there are 2 entries, the result for items & kilos are doubled)

or I have same suburb to suburb Mel to Mel this seems to multiply by 4.

What am I doing wrong...this is the sql view:

SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To],
Click to expand...
Sum(TblTransactions!Items) AS Items, Sum(TblTransactions!Kilograms) AS Kilograms
FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] =
Click to expand...
TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON QryTo.[To
Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] = [To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

Help!

harry
Click to expand...
 
G

Guest

Gary,
Thanks for taking the time to reply.
all the INNER JOINS are causing the multiplicative effect...
where does [To Suburb].SubPost come from and why
do you need it as part of the joins?
what are the SQL views of QryTo and QryFro (and
[ToSuburb] if it is a query)?
Click to expand...
[To Suburb].SubPost comes from QryTo (Postcode Qry -To Suburb, Direct Post) It needs to be part of the joins as I can only have one PK and since I need to join 2 fields with one postcode file. (TblTransactions, From Suburb, To Suburb)

Iactually set up to postcode files to accomodate the relationships.(QryTo & QryFrom)

SQL view QryFro:
SELECT TblTransactions.[From Suburb], [From Direct].[Direct Port]
FROM [From Direct] INNER JOIN (TblTransactions INNER JOIN FromSuburb ON TblTransactions.[From Suburb]=FromSuburb.SubPost) ON [From Direct].ID=FromSuburb.Direct_ID;

SQL view QryTo:
SELECT TblTransactions.[To Suburb], [To Direct].[Direct Port]
FROM ([From Direct] INNER JOIN [To Direct] ON [From Direct].ID=[To Direct].ID) INNER JOIN (TblTransactions INNER JOIN [To Suburb] ON TblTransactions.[To Suburb]=[To Suburb].SubPost) ON [To Direct].ID=[To Suburb].Direct_ID;


Thanks again

Harry
Gary Walter said:
Hi Harry,

Originally you said:

****quote*****
I have a table with fields thus:

From To Items Kilos Charge

Mel Syd 5 25 27.50
Mel Bne 3 11 12.5
Mel Syd 3 5 22.50

When I run the query it gives me Mel - Syd 16 (items) 60 (Kilos) 100 (Charge)
***unquote****

That's quite a bit different than:

SELECT
QryFro.[Direct Port] AS [From],
QryTo.[Direct Port] AS [To],
Sum(TblTransactions.Items) AS Items,
Sum(TblTransactions.Kilograms) AS Kilograms
FROM
(QryFro
INNER JOIN TblTransactions
ON QryFro.[From Suburb] = TblTransactions.[From Suburb])
INNER JOIN
(QryTo
INNER JOIN [To Suburb]
ON QryTo.[To Suburb] = [To Suburb].SubPost)
ON TblTransactions.[To Suburb] = [To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

all the INNER JOINS are causing the multiplicative effect...
where does [To Suburb].SubPost come from and why
do you need it as part of the joins?
what are the SQL views of QryTo and QryFro (and
[ToSuburb] if it is a query)?

What would be wrong with summing only with
TblTransaction in a query, then joining TblPostcode
to the results of that query to get your [DirectPost]
(using judicious JOIN(s))?

qrySum:

SELECT
[From Suburb] As TransFrom,
[To Suburb] As TransTo,
Sum([Items]) As SumItems,
Sum([Kilos]) As SumKilos
FROM
TblTransactions
Group By
[From Suburb],
[To Suburb];

query to get [DirectPort]'s:

SELECT
SumItems,
SumKilos,
(Select t1.[DirectPort]
FROM
TblPostCode As t1
WHERE
t1.Suburb = qrySum.TransFrom) As [From],
(Select t2.[DirectPort]
FROM
TblPostCode As t2
WHERE
t2.Suburb = qrySum.TransTo) As [To]
FROM
qrySum;

Otherwise, I don't see any way to help
without seeing all your views, and sample data
for all your tables and their structure (like is
Suburb "unique-no duplicates" in TblPostcode).
I know that sucks, but


I have tried explaining this problem before without success, so I'll try again.

TblTransactions
From Suburb
To Suburb
Service
Items
Kilos

TblPostcode
Suburb
Direct Port

QryTo
generates the direct port.....To

QryFro
generates the direct port....from

QryRFP
takes the "To" & "From" direct ports and sums the items and kios

This works fine until I have:

A multiple suburb to suburb.....Mel to Syd ( if there are 2 entries, the result for items & kilos are doubled)

or I have same suburb to suburb Mel to Mel this seems to multiply by 4.

What am I doing wrong...this is the sql view:

SELECT QryFro.[Direct Port] AS [From], QryTo.[Direct Port] AS [To],
Click to expand...
Sum(TblTransactions!Items) AS Items, Sum(TblTransactions!Kilograms) AS Kilograms
FROM (QryFro INNER JOIN TblTransactions ON QryFro.[From Suburb] =
Click to expand...
TblTransactions.[From Suburb]) INNER JOIN (QryTo INNER JOIN [To Suburb] ON QryTo.[To
Suburb] = [To Suburb].SubPost) ON TblTransactions.[To Suburb] = [To Suburb].SubPost
GROUP BY QryFro.[Direct Port], QryTo.[Direct Port];

Help!

harry
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Harry,

It just looks to me like you are making it
harder than it needs to be (I could be wrong).

Please list the tables (no queries), their structure
(table names, field names, field types), and
some sample data. I'm sure we can do this
simpler plus clear up the multiplicative effect.

Was there any reason the previous suggestion
would not work?
qrySum:

SELECT
[From Suburb] As TransFrom,
[To Suburb] As TransTo,
Sum([Items]) As SumItems,
Sum([Kilos]) As SumKilos
FROM
TblTransactions
Group By
[From Suburb],
[To Suburb];

query to get [DirectPort]'s:

SELECT
SumItems,
SumKilos,
(Select t1.[DirectPort]
FROM
TblPostCode As t1
WHERE
t1.Suburb = qrySum.TransFrom) As [From],
(Select t2.[DirectPort]
FROM
TblPostCode As t2
WHERE
t2.Suburb = qrySum.TransTo) As [To]
FROM
qrySum;
Click to expand...

I'm sure we can sort this out.....
I just cannot see your data
and each reply confuses me more.

Thanks,

Gary Walter
 
G

Guest

Thanks again Gary, really appreciate your help.
Not sure why your queries didn't work. I've yet to master SQL.

Ok, my tables:

tbltransactions
Transaction ID - Autonumber
From Suburb - Text ( uses cbo from tblPostcode)
To Suburb - Text (uses cbo from tblPostcode)
Service - Text (uses cbo from tblservice)
Items - Number
Kilograms - Number
Charge - Currency

tblPostcode
SubPost - Text - (eg: Kew 3101)
Diect Port - Text (eg: Melbourne )
ID - Autonumber

tblService
Service - Text (eg: Express)

Thats basically it.

Thanks

Harry

Gary Walter said:
Hi Harry,

It just looks to me like you are making it
harder than it needs to be (I could be wrong).

Please list the tables (no queries), their structure
(table names, field names, field types), and
some sample data. I'm sure we can do this
simpler plus clear up the multiplicative effect.

Was there any reason the previous suggestion
would not work?
qrySum:

SELECT
[From Suburb] As TransFrom,
[To Suburb] As TransTo,
Sum([Items]) As SumItems,
Sum([Kilos]) As SumKilos
FROM
TblTransactions
Group By
[From Suburb],
[To Suburb];

query to get [DirectPort]'s:

SELECT
SumItems,
SumKilos,
(Select t1.[DirectPort]
FROM
TblPostCode As t1
WHERE
t1.Suburb = qrySum.TransFrom) As [From],
(Select t2.[DirectPort]
FROM
TblPostCode As t2
WHERE
t2.Suburb = qrySum.TransTo) As [To]
FROM
qrySum;
Click to expand...

I'm sure we can sort this out.....
I just cannot see your data
and each reply confuses me more.

Thanks,

Gary Walter
Click to expand...
 
G

Gary Walter

Harry Bo said:
Ok, my tables:

tbltransactions
Transaction ID - Autonumber
From Suburb - Text ( uses cbo from tblPostcode)
To Suburb - Text (uses cbo from tblPostcode)
Service - Text (uses cbo from tblservice)
Items - Number
Kilograms - Number
Charge - Currency

tblPostcode
SubPost - Text - (eg: Kew 3101)
Diect Port - Text (eg: Melbourne )
ID - Autonumber

tblService
Service - Text (eg: Express)

Thats basically it.
Click to expand...

Thanks Harry,

Please post some sample data from tbltransactions
for the following 3 fields (5 lines or more):

[From Suburb] [To Suburb] [Service]
??? ??? ???

When you say

From Suburb - Text ( uses cbo from tblPostcode)

that can mean one of 2 things to me?

1) you are using "Lookup fields" where the ID
from tblPostcode is actually stored in tbltransactions

2) you have a combobox on your entry form
bound to tbltransactions that shows distinct
[SubPost]? from tblPostcode and when a user
selects one, the [SubPost]? *text* is stored in
tbltransactions.[From Suburb].

Hopefully it is number 2?

Humor me and run the following queries
and please post back with your results/success.

1) check for "tandem repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
SubPost,
[Direct Port]
FROM
tblPostcode
GROUP BY
SubPost, [Direct Port]
HAVING Count(*)>1;

2) check for "SubPost repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
SubPost
FROM
tblPostcode
GROUP BY
SubPost
HAVING Count(*)>1;

3) check for "Direct Port repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
[Direct Port]
FROM
tblPostcode
GROUP BY
[Direct Port]
HAVING Count(*)>1;

4) check for repeats in tblService

SELECT
Service
FROM
tblService
GROUP BY
Service
HAVING Count(*)>1;

3) I don't know how we can go any
further if we do not understand why
the following didn't work (do you get
an error message?)

SELECT
[From Suburb],
[To Suburb],
Service,
Sum(Items) As ItemSum,
Sum(Kilograms) As SumKilograms,
Sum(Charge) As SumCharge
FROM tbltransactions
GROUP BY
[From Suburb],
[To Suburb],
Service
ORDER BY
[From Suburb],
[To Suburb],
Service;

If you need help with this, I can type out
"step-by-step" directions to create this
query from scratch.

If you do get this to work, then post back
the result(s) (just 3 fields) you get from it for
what will be the "Mel to Syd" records.

[From Suburb] [To Suburb] [Service]
???? ??? ???

I'm sorry it is so complicated, but please
post back with your results for the above.

Thank you,

Gary Walter
 
G

Guest

Gary,

From Suburb - Text ( uses cbo from tblPostcode)
that can mean one of 2 things to me?

1) you are using "Lookup fields" where the ID
from tblPostcode is actually stored in tbltransactions

2) you have a combobox on your entry form
bound to tbltransactions that shows distinct
[SubPost]? from tblPostcode and when a user
selects one, the [SubPost]? *text* is stored in
tbltransactions.[From Suburb].

Hopefully it is number 2?
Click to expand...
Yes number 2

SELECT
SubPost,
[Direct Port]
FROM
tblPostcode
GROUP BY
SubPost, [Direct Port]
HAVING Count(*)>1;
Click to expand...

Result:
SubPost Direct Port
ARUNDEL 4214 GOLD COAST 4210
KALGOORLIE 6433 KALGOORLIE 6430
KELVIN GROVE 4059 BRISBANE 4000
PORT MACQUARIE 2444 PORT MACQUARIE 2444
TINGALPA 4173 BRISBANE 4000
URIARRA 2611 CANBERRA 2600

SELECT
SubPost
FROM
tblPostcode
GROUP BY
SubPost
HAVING Count(*)>1;

Result:
SubPost
ARUNDEL 4214
KALGOORLIE 6433
KELVIN GROVE 4059
PORT MACQUARIE 2444
TINGALPA 4173
URIARRA 2611

SELECT
[Direct Port]
FROM
tblPostcode
GROUP BY
[Direct Port]
HAVING Count(*)>1;

Result.....there's 394 of these which is to be expected as these are the direct ports associated with the 10000 individual suburbs

SELECT
Service
FROM
tblService
GROUP BY
Service
HAVING Count(*)>1;

Result nil

The qry:
SELECT
[From Suburb],
[To Suburb],
Service,
Sum(Items) As ItemSum,
Sum(Kilograms) As SumKilograms,
Sum(Charge) As SumCharge
FROM tbltransactions
GROUP BY
[From Suburb],
Click to expand...

works fine,but groups by from suburb ( there are 10000 of these but only 390 direct Ports) but I need the result for the direct ports:

From Suburb Direct To Suburb Direct Port items kilos
Como Perth Kew Melbourne 2 6
Belmont Perth Glenroy Melbourne 3 8
Kew Melbourne Milton Brisbane 5 12
Glenroy Melbourne Mansfield Brisbane 8 22
Manly Sydney Craigie Perth 3 17

The result should be:
From To Items Kilos
Perth Melbourne 5 14
Melbourne Brisbane 13 34
Sydney Perth 3 17

Thanks again Gary

Harry




Gary Walter said:
Harry Bo said:
Ok, my tables:

tbltransactions
Transaction ID - Autonumber
From Suburb - Text ( uses cbo from tblPostcode)
To Suburb - Text (uses cbo from tblPostcode)
Service - Text (uses cbo from tblservice)
Items - Number
Kilograms - Number
Charge - Currency

tblPostcode
SubPost - Text - (eg: Kew 3101)
Diect Port - Text (eg: Melbourne )
ID - Autonumber

tblService
Service - Text (eg: Express)

Thats basically it.
Click to expand...

Thanks Harry,

Please post some sample data from tbltransactions
for the following 3 fields (5 lines or more):

[From Suburb] [To Suburb] [Service]
??? ??? ???

When you say

From Suburb - Text ( uses cbo from tblPostcode)

that can mean one of 2 things to me?

1) you are using "Lookup fields" where the ID
from tblPostcode is actually stored in tbltransactions

2) you have a combobox on your entry form
bound to tbltransactions that shows distinct
[SubPost]? from tblPostcode and when a user
selects one, the [SubPost]? *text* is stored in
tbltransactions.[From Suburb].

Hopefully it is number 2?

Humor me and run the following queries
and please post back with your results/success.

1) check for "tandem repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
SubPost,
[Direct Port]
FROM
tblPostcode
GROUP BY
SubPost, [Direct Port]
HAVING Count(*)>1;

2) check for "SubPost repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
SubPost
FROM
tblPostcode
GROUP BY
SubPost
HAVING Count(*)>1;

3) check for "Direct Port repeats" in tblPostcode
(start a new query, go into SQL View,
and paste the following into the window)

SELECT
[Direct Port]
FROM
tblPostcode
GROUP BY
[Direct Port]
HAVING Count(*)>1;

4) check for repeats in tblService

SELECT
Service
FROM
tblService
GROUP BY
Service
HAVING Count(*)>1;

3) I don't know how we can go any
further if we do not understand why
the following didn't work (do you get
an error message?)

SELECT
[From Suburb],
[To Suburb],
Service,
Sum(Items) As ItemSum,
Sum(Kilograms) As SumKilograms,
Sum(Charge) As SumCharge
FROM tbltransactions
GROUP BY
[From Suburb],
[To Suburb],
Service
ORDER BY
[From Suburb],
[To Suburb],
Service;

If you need help with this, I can type out
"step-by-step" directions to create this
query from scratch.

If you do get this to work, then post back
the result(s) (just 3 fields) you get from it for
what will be the "Mel to Syd" records.

[From Suburb] [To Suburb] [Service]
???? ??? ???

I'm sorry it is so complicated, but please
post back with your results for the above.

Thank you,

Gary Walter
Click to expand...
 
G

Gary Walter

Harry said:
The qry:
SELECT
[From Suburb],
[To Suburb],
Service,
Sum(Items) As ItemSum,
Sum(Kilograms) As SumKilograms,
Sum(Charge) As SumCharge
FROM tbltransactions
GROUP BY
[From Suburb],
Click to expand...

works fine,but groups by from suburb ( there are 10000 of these but only 390 direct
Click to expand...
Ports) but I need the result for the direct ports:
From Suburb Direct To Suburb Direct Port items kilos
Como Perth Kew Melbourne 2 6
Belmont Perth Glenroy Melbourne 3 8
Kew Melbourne Milton Brisbane 5 12
Glenroy Melbourne Mansfield Brisbane 8 22
Manly Sydney Craigie Perth 3 17

The result should be:
From To Items Kilos
Perth Melbourne 5 14
Melbourne Brisbane 13 34
Sydney Perth 3 17
Click to expand...
Hi Harry,

Ahh..I think I am finally understanding (maybe).

{I assume you deleted the 6 "tandem repeats" from
tblPostcode, so there is only one of them}

Now....click on "Create Query in Design View"

In the Show Table dialog box,
click on tbltransactions,
click Add,
click on tblPostcode,
click Add,
click on Add again,
and then click Close.

You should now show 1 copy of tbltransactions
table and 2 copies of tblPostcode table
in the query designer.

Right-mouse click one of the tblPostcode tables
and choose Properties.
In the Alias row, type in
tTo
then close the Properties dialog box.

Right-mouse click on the other tblPostcode table
and choose Properties.
In the Alias row, type in
tFrom
then close the Properties dialog box.


Click and hold down on left table's
[From Suburb] field
and "drag and drop"
over on tFrom's SubPost field.

Click and hold down on left table's
[To Suburb] field
and "drag and drop"
over on tTo's SubPost field.

You should now just show our 2 join lines.
If there are any others created by Access,
right-mouse click on them and delete.

Drag and Drop [Direct Port] field from
tFrom table down in to field row
of first column of grid. In front of
"[Direct Port]" in your field row, type

FromPort:

{best to not use reserved word "From"}

Drag and Drop [Direct Port] field from
tTo table down in to field row
of first column of grid. In front of
"[Direct Port]" in your field row, type

ToPort:


Holding down CTRL key, in tbltransactions,
select (highlight) fields

Service
Items
Kilograms
Charge

Release CTRL key and drag and drop selected
fields down into field row of the 3rd column of
your query grid.

We can go down several paths from here, but
first I want to make sure this works if you don't
mind.

If it does, one way would be to save this query
as "qryPorts"

Then base your totals query on it.

SELECT
[FromPort],
[ToPort],
Service,
Sum([Items]) As ItemsSum,
Sum([Kilograms]) As KiloSum,
Sum([Charge]) As ChargeSum
FROM qryPorts
GROUP BY
[FromPort],
[ToPort],
Service;

If a specific From/To pair might have
more than one type of Service, then
you will have to make a decision of
which one?

SELECT
[FromPort],
[ToPort],
First([Service]) As FirstService,
Sum([Items]) As ItemsSum,
Sum([Kilograms]) As KiloSum,
Sum([Charge]) As ChargeSum
FROM qryPorts
GROUP BY
[FromPort],
[ToPort];

The other option (if qryPorts worked
correctly), would be to just change
qryPorts into a totals query.

Well...maybe we are "close."

Please respond back with your success. :cool:

Gary Walter
 
G

Guest

Bingo!!

Thanks Gary

This is the SQL that worked:
SELECT qryPorts.[tFrom] AS Expr1, qryPorts.[tTo] AS Expr2, qryPorts.Service, Sum(qryPorts.Items) AS ItemsSum, Sum(qryPorts.Kilograms) AS KiloSum, Sum([Charge]) AS ChargeSum
FROM qryPorts
GROUP BY qryPorts.[tFrom], qryPorts.[tTo], qryPorts.Service;

All we needed to do was after generating "qryPorts" was to GROUP BY.

Thanks again Gary

Harry


Gary Walter said:
Harry said:
The qry:
SELECT
[From Suburb],
[To Suburb],
Service,
Sum(Items) As ItemSum,
Sum(Kilograms) As SumKilograms,
Sum(Charge) As SumCharge
FROM tbltransactions
GROUP BY
[From Suburb],
Click to expand...

works fine,but groups by from suburb ( there are 10000 of these but only 390 direct
Click to expand...
Ports) but I need the result for the direct ports:
From Suburb Direct To Suburb Direct Port items kilos
Como Perth Kew Melbourne 2 6
Belmont Perth Glenroy Melbourne 3 8
Kew Melbourne Milton Brisbane 5 12
Glenroy Melbourne Mansfield Brisbane 8 22
Manly Sydney Craigie Perth 3 17

The result should be:
From To Items Kilos
Perth Melbourne 5 14
Melbourne Brisbane 13 34
Sydney Perth 3 17
Click to expand...
Hi Harry,

Ahh..I think I am finally understanding (maybe).

{I assume you deleted the 6 "tandem repeats" from
tblPostcode, so there is only one of them}

Now....click on "Create Query in Design View"

In the Show Table dialog box,
click on tbltransactions,
click Add,
click on tblPostcode,
click Add,
click on Add again,
and then click Close.

You should now show 1 copy of tbltransactions
table and 2 copies of tblPostcode table
in the query designer.

Right-mouse click one of the tblPostcode tables
and choose Properties.
In the Alias row, type in
tTo
then close the Properties dialog box.

Right-mouse click on the other tblPostcode table
and choose Properties.
In the Alias row, type in
tFrom
then close the Properties dialog box.


Click and hold down on left table's
[From Suburb] field
and "drag and drop"
over on tFrom's SubPost field.

Click and hold down on left table's
[To Suburb] field
and "drag and drop"
over on tTo's SubPost field.

You should now just show our 2 join lines.
If there are any others created by Access,
right-mouse click on them and delete.

Drag and Drop [Direct Port] field from
tFrom table down in to field row
of first column of grid. In front of
"[Direct Port]" in your field row, type

FromPort:

{best to not use reserved word "From"}

Drag and Drop [Direct Port] field from
tTo table down in to field row
of first column of grid. In front of
"[Direct Port]" in your field row, type

ToPort:


Holding down CTRL key, in tbltransactions,
select (highlight) fields

Service
Items
Kilograms
Charge

Release CTRL key and drag and drop selected
fields down into field row of the 3rd column of
your query grid.

We can go down several paths from here, but
first I want to make sure this works if you don't
mind.

If it does, one way would be to save this query
as "qryPorts"

Then base your totals query on it.

SELECT
[FromPort],
[ToPort],
Service,
Sum([Items]) As ItemsSum,
Sum([Kilograms]) As KiloSum,
Sum([Charge]) As ChargeSum
FROM qryPorts
GROUP BY
[FromPort],
[ToPort],
Service;

If a specific From/To pair might have
more than one type of Service, then
you will have to make a decision of
which one?

SELECT
[FromPort],
[ToPort],
First([Service]) As FirstService,
Sum([Items]) As ItemsSum,
Sum([Kilograms]) As KiloSum,
Sum([Charge]) As ChargeSum
FROM qryPorts
GROUP BY
[FromPort],
[ToPort];

The other option (if qryPorts worked
correctly), would be to just change
qryPorts into a totals query.

Well...maybe we are "close."

Please respond back with your success. :cool:

Gary Walter
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database -relationship set up 3
Null Values 5
Query Not Summing All Records 2
Query doubling values 5
check box query 1
UPDATE QUERY HELP REQUIRED 4
Query Problem 2
Top 3 per group 3

Top