How to select distinct combinations of 2 fields that can be the sa

A

agarten

Hi everyone,

I'm working with a table that has origin and destination fields. I'm trying
to figure out the amount of flow between 2 locations. For example: amount
from CA to NY + amount from NY to CA. While I can get the calculation to
work correctly, in my query it returns
Origin: NY Dest: CA 1234
Origin: CA Dest: NY 1234

The 1234 is the correct total of the amount going from NY to CA added to the
amount going from CA to NY. I'm trying to figure out how to only show one of
the two rows.

Thanks for any help you can provide.
Amy
 
A

agarten

SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin =
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING (((Domestic_06.Destination)<>[Domestic_06].[Origin]));
 
K

KARL DEWEY

The first query builds an alphabetical listed origin-destination list without
regard as to point of origin.
agarten_2 ----
SELECT
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]) AS CK
FROM Domestic_06
GROUP BY
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]);

The second query list the sum of flow between them without regard as to
point of origin.
agarten_2 ---
SELECT agarten_1.CK, Sum(Domestic_06.Mdol) AS SumOfMdol,
Sum(Domestic_06.Kton) AS SumOfKton
FROM agarten_1, Domestic_06
WHERE (((agarten_1.CK) Like "*" & [Origin] & "*" And (agarten_1.CK) Like "*"
& [Destination] & "*"))
GROUP BY agarten_1.CK;

--
KARL DEWEY
Build a little - Test a little


agarten said:
SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin =
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING (((Domestic_06.Destination)<>[Domestic_06].[Origin]));

KARL DEWEY said:
Post the SQL of your query.
Click to expand...
Click to expand...
 
J

John Spencer

Just a thought, I wonder if the following might work, Note the change in
the Having clause

SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin =
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING Domestic_06.Destination>[Domestic_06].[Origin]

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

KARL DEWEY said:
The first query builds an alphabetical listed origin-destination list
without
regard as to point of origin.
agarten_2 ----
SELECT
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]) AS CK
FROM Domestic_06
GROUP BY
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]);

The second query list the sum of flow between them without regard as to
point of origin.
agarten_2 ---
SELECT agarten_1.CK, Sum(Domestic_06.Mdol) AS SumOfMdol,
Sum(Domestic_06.Kton) AS SumOfKton
FROM agarten_1, Domestic_06
WHERE (((agarten_1.CK) Like "*" & [Origin] & "*" And (agarten_1.CK) Like
"*"
& [Destination] & "*"))
GROUP BY agarten_1.CK;

--
KARL DEWEY
Build a little - Test a little


agarten said:
SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin
=
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING (((Domestic_06.Destination)<>[Domestic_06].[Origin]));

KARL DEWEY said:
Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

Hi everyone,

I'm working with a table that has origin and destination fields. I'm
trying
to figure out the amount of flow between 2 locations. For example:
amount
from CA to NY + amount from NY to CA. While I can get the
calculation to
work correctly, in my query it returns
Origin: NY Dest: CA 1234
Origin: CA Dest: NY 1234

The 1234 is the correct total of the amount going from NY to CA added
to the
amount going from CA to NY. I'm trying to figure out how to only
show one of
the two rows.

Thanks for any help you can provide.
Amy
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Using this data ---
Origin Destination Mdol Kton
CA NY 5 7
CA NY 4 3
NY CA 8 6
AT SF 3 4
SF AT 3 3
and your SQL i got ---
Origin Destination Mdol Kton
AT SF 6 7
CA NY 25 22
My SQL got these results ---
CK SumOfMdol SumOfKton
AT-SF 6 7
CA-NY 17 16

--
KARL DEWEY
Build a little - Test a little


John Spencer said:
Just a thought, I wonder if the following might work, Note the change in
the Having clause

SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin =
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING Domestic_06.Destination>[Domestic_06].[Origin]

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

KARL DEWEY said:
The first query builds an alphabetical listed origin-destination list
without
regard as to point of origin.
agarten_2 ----
SELECT
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]) AS CK
FROM Domestic_06
GROUP BY
IIf([Domestic_06].[Destination]>[Domestic_06].[Origin],[Domestic_06].[Origin]
& "-" & [Domestic_06].[Destination],[Domestic_06].[Destination] & "-" &
[Domestic_06].[Origin]);

The second query list the sum of flow between them without regard as to
point of origin.
agarten_2 ---
SELECT agarten_1.CK, Sum(Domestic_06.Mdol) AS SumOfMdol,
Sum(Domestic_06.Kton) AS SumOfKton
FROM agarten_1, Domestic_06
WHERE (((agarten_1.CK) Like "*" & [Origin] & "*" And (agarten_1.CK) Like
"*"
& [Destination] & "*"))
GROUP BY agarten_1.CK;

--
KARL DEWEY
Build a little - Test a little


agarten said:
SELECT Domestic_06.Origin, Domestic_06.Destination,
Sum(([Domestic_06].[Mdol]+[Domestic_06_1].[Mdol])) AS Mdol,
Sum(([Domestic_06].[Kton]+[Domestic_06_1].[Kton])) AS Kton
FROM Domestic_06 INNER JOIN Domestic_06 AS Domestic_06_1 ON
(Domestic_06.Destination = Domestic_06_1.Origin) AND (Domestic_06.Origin
=
Domestic_06_1.Destination)
GROUP BY Domestic_06.Origin, Domestic_06.Destination
HAVING (((Domestic_06.Destination)<>[Domestic_06].[Origin]));

:

Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little


:

Hi everyone,

I'm working with a table that has origin and destination fields. I'm
trying
to figure out the amount of flow between 2 locations. For example:
amount
from CA to NY + amount from NY to CA. While I can get the
calculation to
work correctly, in my query it returns
Origin: NY Dest: CA 1234
Origin: CA Dest: NY 1234

The 1234 is the correct total of the amount going from NY to CA added
to the
amount going from CA to NY. I'm trying to figure out how to only
show one of
the two rows.

Thanks for any help you can provide.
Amy
Click to expand...
Click to expand...
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

Count where two fields are the same 2
Multple Sites 5
Formula to count based on multiple criteria fields w/duplicates 5
Oracle criteria problem 1
how do I surpress the printout of an empty field and its label? 2
Excel 2010/2007 - How much can be done with Custom Validation? 6
How to apply a conditional Select distinct (or conditional Where onduplicated cell values in a colum 3
Query involving Strings : How To Return Matching Data From Both Ta 4

Top