Counting Expression

[PowellGirlTN]

Can somebody please help me with this expression. The table is
tblClassification, and the field name is "Class". The records in the table
in the Class field are, "Students", "Teachers" and "Bus Drivers". I want to
write an expression called Students and have it count all the Students. The
query itself has 4 tables total so I'm not sure if having additonal tables in
the query is causing my problem with counting the student, teachers and bus
drivers

 

[Michel Walsh]

Can you rephrase the problem. As example, you mention a field name Class and
then continue your description with the table in the Class field ??? To my
understanding, a table contains fields and a field contains values, not
tables. Furthermore, if you can supply a SMALL example of what the data, the
fields AND THE VALUES, are, in a TABULAR format, that can help.



Vanderghast, Access MVP

 

[KARL DEWEY]

If you only want to count "Students", "Teachers" and "Bus Drivers" why do you
have three more table in the query?

 

[PowellGirlTN]

because I'm using this inforation on a report. I'm capturing other data from
the other tables on the report. I hope that helps. Thanks

 

[PowellGirlTN]

I guess I didn't phrase my question very clearly...the class field is in the
table tblClassification. The class values are Students, Teachers and Bus
Drivers. I want to be able to count just students, just teachers and just
bus drivers in my report so I was thinking I needed an expression for each in
my query. Thank you

 

[KARL DEWEY]

Post the SQL of the query with the 4 tables - open query in design view,
click on menu VIEW - View SQL. Highlight all, copy, and paste in a post.

 

[PowellGirlTN]

Here you go Karl

SELECT tblTravelers.TravelerID, tblTravelers.LastName,
tblTravelers.FirstName, tblTravelers.SexID, tblTravelers.GroupID,
tblTravelers.SchoolID, tblTravelers.VehID, tblTravelers.HotelID,
tblTravelers.AssignedChap, tblTravelers.[Medical Needs], tblTravelers.CityID,
tblTravelers.AreaCode, tblTravelers.Phone, tblSex.Sex,
tblClassification.CLASSCode, tblHotels.HOTELName, tblHotels.HOTELPhone,
tblTravelers.RoomType
FROM tblClassification INNER JOIN ((tblSex INNER JOIN tblTravelers ON
tblSex.SexID = tblTravelers.SexID) INNER JOIN tblHotels ON
tblTravelers.HotelID = tblHotels.HOTELID) ON
tblClassification.ClassificationID = tblTravelers.ClassificationID;

 

[PowellGirlTN]

Here you go:

SELECT tblTravelers.TravelerID, tblTravelers.LastName,
tblTravelers.FirstName, tblTravelers.SexID, tblTravelers.GroupID,
tblTravelers.SchoolID, tblTravelers.VehID, tblTravelers.HotelID,
tblTravelers.AssignedChap, tblTravelers.[Medical Needs], tblTravelers.CityID,
tblTravelers.AreaCode, tblTravelers.Phone, tblSex.Sex,
tblClassification.CLASSCode, tblHotels.HOTELName, tblHotels.HOTELPhone,
tblTravelers.RoomType
FROM tblClassification INNER JOIN ((tblSex INNER JOIN tblTravelers ON
tblSex.SexID = tblTravelers.SexID) INNER JOIN tblHotels ON
tblTravelers.HotelID = tblHotels.HOTELID) ON
tblClassification.ClassificationID = tblTravelers.ClassificationID;

 

[Michel Walsh]

Can it be:


SELECT LAST(tblClassification.CLASSCode), COUNT(*)
FROM tblClassification INNER JOIN tblTravelers
ON tblClassification.ClassificationID =
tblTravelers.ClassificationID
GROUP BY tblClassification.ClassificationID





Vanderghast, Access MVP

 

[KARL DEWEY]

Create another query tblTravelers_Class ---
SELECT tblClassification.CLASSCode, Count(tblTravelers.ClassificationID) AS
CountOfClassificationID
FROM tblClassification INNER JOIN tblTravelers ON
tblClassification.ClassificationID = tblTravelers.ClassificationID
GROUP BY tblClassification.CLASSCode;

Join in your query ---
SELECT tblTravelers.TravelerID, tblTravelers.LastName,
tblTravelers.FirstName, tblTravelers.SexID, tblTravelers.GroupID,
tblTravelers.SchoolID, tblTravelers.VehID, tblTravelers.HotelID,
tblTravelers.AssignedChap, tblTravelers.[Medical Needs], tblTravelers.CityID,
tblTravelers.AreaCode, tblTravelers.Phone, tblSex.Sex,
tblClassification.CLASSCode, tblHotels.HOTELName, tblHotels.HOTELPhone,
tblTravelers.RoomType, tblTravelers_Class.CountOfClassificationID AS
[Traveler Class Total]
FROM ((tblClassification INNER JOIN (tblTravelers LEFT JOIN tblHotels ON
tblTravelers.HotelID = tblHotels.HOTELID) ON
tblClassification.ClassificationID = tblTravelers.ClassificationID) LEFT JOIN
tblSex ON tblTravelers.SexID = tblSex.SexID) INNER JOIN tblTravelers_Class ON
tblClassification.CLASSCode = tblTravelers_Class.CLASSCode;

--
KARL DEWEY
Build a little - Test a little

Here you go Karl

SELECT tblTravelers.TravelerID, tblTravelers.LastName,
tblTravelers.FirstName, tblTravelers.SexID, tblTravelers.GroupID,
tblTravelers.SchoolID, tblTravelers.VehID, tblTravelers.HotelID,
tblTravelers.AssignedChap, tblTravelers.[Medical Needs], tblTravelers.CityID,
tblTravelers.AreaCode, tblTravelers.Phone, tblSex.Sex,
tblClassification.CLASSCode, tblHotels.HOTELName, tblHotels.HOTELPhone,
tblTravelers.RoomType
FROM tblClassification INNER JOIN ((tblSex INNER JOIN tblTravelers ON
tblSex.SexID = tblTravelers.SexID) INNER JOIN tblHotels ON
tblTravelers.HotelID = tblHotels.HOTELID) ON
tblClassification.ClassificationID = tblTravelers.ClassificationID;

Post the SQL of the query with the 4 tables - open query in design view,
click on menu VIEW - View SQL. Highlight all, copy, and paste in a post.