origin-destination matrix calculations

[baggieboy]

I have some data in which each row is an individual who travels from his/her home area to a work place, which can be simplified as follows:
person_ID Area_ID Workplace_ID
Person1 Area1 WorkplaceA
Person2 Area1 WorkplaceB
Person3 Area1 WorkplaceA
Person4 Area2 WorkplaceB
Person5 Area2 WorkplaceC
Person6 Area2 WorkplaceC
Person7 Area3 WorkplaceA
Person8 Area3 WorkplaceB
Person9 Area3 WorkplaceB
Person10 Area3 WorkplaceC

For every home area I would like to establish the most common work area traveled to (and, ideally, a count of the people travelling there from that area). So in the example my query would output:
Area Most_common_workplace Count
Area1 WorkplaceA 2


Area2 WorkplaceC 2
Area3 WorkplaceB 2

I think I could achieve this in Excel, but my database is in practice very large, and I think this is a database task. I'm sure it should be relatively easy in Access, but can't see how to achieve it - any advice most welcome!

Thanks

[jzwp11]

Welcome to the forum!

You will need an aggregate query. If you are doing this from the design grid, add the three fields with area first, the workplace and then person. Highlight the person column, right click and select Totals. A total row will be added to the grid. For the person field change the Group By to count via the dropdown. Run the query.


If you do this via the SQL window, the query will look like this

SELECT Area_ID, Workplace_ID, Count(person_ID) As CountOfPerson_ID
FROM tablename
GROUP BY Area_ID, Workplace_ID

[baggieboy]

Thanks jzwp11 for the welcome and the quick reply.

Your solution gets me a count of each area/workplace combination - which is half-way to what I'd like - but I want to get a list which only contains each area once, along with the workplace that is the most common destination from each area, and the count.

I suspect it's just an extra step from the table created from your query, but I can't figure that bit.

Thanks

John

[jzwp11]

My apologies, I did not read your post thoroughly. Using the query I provided earlier, you now need to create another query that identifies the max count (i.e. the most popular workplace) for each area

If I call the first query: qryCountByWorkPlaceWithinArea

This is the second query:

query name: qryMaxByArea

SELECT qryCountByWorkPlaceWithinArea.Area_ID, Max(qryCountByWorkPlaceWithinArea.CountOfPerson_ID ) AS MaxOfCountOfPerson_ID
FROM qryCountByWorkPlaceWithinArea
GROUP BY qryCountByWorkPlaceWithinArea.Area_ID;


The above query just gives the max count for any one area but it does not show which workplace produced that count. So now we have to join the above query back to the first query. We have to join by both the count and the area. This is what the final query will look like

SELECT qryCountByWorkPlaceWithinArea.Area_ID, qryCountByWorkPlaceWithinArea.Workplace_ID as MostCommonDestination, qryCountByWorkPlaceWithinArea.CountOfPerson_ID as NumberGoingToThisDestination
FROM qryCountByWorkPlaceWithinArea INNER JOIN qryMaxByArea ON (qryCountByWorkPlaceWithinArea.CountOfPerson_ID = qryMaxByArea.MaxOfCountOfPerson_ID) AND (qryCountByWorkPlaceWithinArea.Area_ID = qryMaxByArea.Area_ID);

[baggieboy]

Many thanks again for the fast reply - your logic looks right to me, I will try it out and let you know.

How do I get you a vitual beer?

Thanks again

[jzwp11]

Let me know if it works for you.

[baggieboy]

jzwp11 - it works a treat - many, many thanks, i was getting nowhere fast with that earlier.
John

[jzwp11]

Glad it worked out. And thanks for the virtual beer!