Count where two fields are the same

[Carpie]

I'm using Access 2003 and have a database that looks at the origin and
destination zip codes of all shipments of a company. I want to identify they
heaviest "lanes" (same origin/destinations) so am trying to create a query
that shows me a descending count where the origin and destination fields have
the same value (not the same value of each other). Basically it would have
to create "pairs" to create the lanes and then show me count. Also helpful
would be if it could neglect direction so that an origin of NYC, NY to
destination of L.A., CA counted the same as an origin of L.A., CA going to
NYC, NY (as they'd be the same "lane").

Thanks in advance.

 

[Jerry Whittle]

Table and field names would really help us to help you.

Something like below with the correct field and table names should work for
ONE direction. If I get some time, I'll try to figure out both directions or
maybe someone else will figure it out.

SELECT tblLanes.OriginZipCode,
tblLanes.DestinationZipCode,
Count(tblLanes.OriginZipCode) AS [Lane Count]
FROM tblLanes
GROUP BY tblLanes.OriginZipCode,
tblLanes.DestinationZipCode
ORDER BY Count(tblLanes.OriginZipCode) DESC;

 

[Len]

Carpie,

Try this:
1) Create a new query
2) Make the first field of the query a concatenation of the origin and
destination zip codes Lane: [OriginZip]&"-"& [DestinationZip]
3) Make the second field the shipment number or any other regular data field
to be counted.
4) Click the 'Totals' icon (Menu item View/Totals)
5) Change 'Group By' to 'Count' in the shipment number column
6) Change Sort to 'Descending' in the 'Lane' field.

To ignore the origin/destination difference try this:
In step 2 above, create an 'IFF' statement like this:
Lane: iff([OriginZip]>[DestinationZip],[OriginZip]&"-"&
[DestinationZip],[DestinationZip]&"-"& [OriginZip])


I hope this helps.