Count Records with Multiple Criteria

[khart12]

I need to determine how many buses complete multiple runs (trips) from the same school.



I have 3 fields in the DB: Bus No, Run ID and Schools.


To say it in a sentence, I want to know how many Buses have the Same School Code Listed, but also has a Different Run ID.

In the posted Example - Bus 144 is one such Bus, it visits School 348 three times but has two Run IDs listed (348.105 and 348.106). This would count as "1" instance that I am trying to count. I have hundreds of rows of data for hundreds of buses. I would like to know how many times a Unique Bus Number shows the same school but on different Run IDs. Assuming there were two other such buses in my DB, I would like an Output of "3", indicating 3 buses in the DB make multiple runs from the same school.

Any tips, tricks or suggestions are greatly appreciated.

[June7]

You mean the two 348.106 RunIDs would be 1 instance and the 348.105 would be another?

Does this get you somewhere:

SELECT BusNo, RunID, Count(Schools) AS CountSchools FROM tablename GROUP BY BusNo, RunID;


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[khart12]

Thanks for the quick response.

No, I do not need the data tallied in this fashion - "You mean the two 348.106 RunIDs would be 1 instance and the 348.105 would be another?" I only need know that Bus 144 is one bus that meets my criteria.

Does this get you somewhere: SELECT BusNo, RunID, Count(Schools) AS CountSchools FROM tablename GROUP BY BusNo, RunID; ...
YES, these commands did work (Thanks) but it didnt tally the data as needed, let me try to better explain what I seek.

The BusNo in Column 1 has multiple entries (usually 2-6 rows of data). The Schools in Column 3 reflect the schools each Bus Services on a particular Run. The RunID in Column 2 shows which leg of their journey they are on (1st trip, 2nd trip, etc). I need to know how many BusNo in Column 1 list the same Schools in Column 3 but show those Schools on a different RunID in Column 2. So in the generic data I posted originally, Bus 144 is the only such bus in this example. The other Buses should not appear in this Query.

[June7]

Maybe:

Query1
SELECT BusNo, RunID, Schools FROM tablename GROUP BY BusNo, RunID, Schools;

or
SELECT DISTINCT BusNo, RunID, Schools FROM tablename;

Query2
SELECT BusNo, Schools FROM Query1 GROUP BY BusNo, Schools HAVING Count(RunID) > 1;

[khart12]

Still not quite there but getting closer... maybe the image below can help to further clarify...

[June7]

Yes, I expected the suggested query sequence to produce that result. However, I do not have data to test. So what was the output of Query2?

[khart12]

I did not get to Query2 as Query 1 would not work for me, it would ask me Which School I wanted to search/filter by...which would be all schools.

I've attached an example DB.

I appreciate the assistance.

Bus_DB.zip

[June7]

So what is the correct field name: School or Schools - you used both in your posts.

Just downloaded the db. Apparently the correct name is School. The queries work.

[khart12]

Sorry it took me so long to respond, I wanted to test a few files before I was satisfied. For typical files, this worked great. I really appreciate it.

However, I did discover one hiccup with this process. See Attached Image and Zipped DB for troubleshooting.

Bus_DB2.zip

[rpeare]

hope you don't mind a late comer...

Try this:
Save this query as QryPre:

SELECT QMF_PM.BusNo, Left([School],3) AS SchoolNo, Count(QMF_PM.RunID) AS CountOfRunID
FROM QMF_PM
GROUP BY QMF_PM.BusNo, Left([School],3);

Save this query as QryPre2:

SELECT QMF_PM.BusNo, QMF_PM.RunID, QMF_PM.School, Left([school],3) AS SchoolNo
FROM QMF_PM;

Save this query as QryFinalPre:

SELECT QryPre.BusNo, QryPre.SchoolNo, QryPre2.RunID, Count(QryPre2.BusNo) AS CountOfBusNo
FROM QryPre LEFT JOIN QryPre2 ON (QryPre.SchoolNo = QryPre2.SchoolNo) AND (QryPre.BusNo = QryPre2.BusNo)
GROUP BY QryPre.BusNo, QryPre.SchoolNo, QryPre2.RunID;

This is your final query:

SELECT QryFinalPre.BusNo, QryFinalPre.SchoolNo, Sum(QryFinalPre.CountOfBusNo) AS SumOfCountOfBusNo
FROM QryFinalPre
GROUP BY QryFinalPre.BusNo, QryFinalPre.SchoolNo
HAVING (((Sum(QryFinalPre.CountOfBusNo))>1));

[June7]

The query meets the requirement of "identify bus numbers in column1 show the same school in column3 but not the same runID in column2".

So if you want only the bus numbers, query3:

SELECT DISTINCT BusNo FROM Query2;

[rpeare]

hah I completely misread this.. I suck

[khart12]

Works like a charm. Thanks for the assistance.

Please Mark as SOLVED!

[June7]

You can always mark your threads as solved - Thread Tools dropdown above first post.

Done.