Counting Locations

  • Thread starter Thread starter andy_42 via AccessMonster.com
  • Start date Start date
A

andy_42 via AccessMonster.com

I am having a problem with creating employee totals.

I have two tables as follows:

employeesTable:
emp name, locationID, location Name

locationsTable:
LocationID, Location Name

I am trying to create employee totals for the various locations with the
locationID as the relationship.

Essentially,
Select the first locationID from the locationsTable, count the total
employees (in the employee table) based on the selected locationId...and do
the same for all location ID's.

Is there an easy query that can produce what I am attempting here?

Thanks!
 
andy_42 via AccessMonster.com said:
I am having a problem with creating employee totals.

I have two tables as follows:

employeesTable:
emp name, locationID, location Name

locationsTable:
LocationID, Location Name

I am trying to create employee totals for the various locations with the
locationID as the relationship.

Essentially,
Select the first locationID from the locationsTable, count the total
employees (in the employee table) based on the selected locationId...and do
the same for all location ID's.
Click to expand...

According to what you said your table fields are, this
should work:

SELECT [location Name], Count(*) As NumAtLoc
FROM employees
GROUP BY [location Name]

BUT you should NOT have the location Name field in the
employees table. In this case, the query would be:

SELECT L.[location Name], Count(*) As NumAtLoc
FROM employees As E INNER JOIN locations As L
ON L.LocationID = E.LocationID
GROUP BY L.[location Name]
 
SELECT [location Name], Count(*) As NumAtLoc
FROM employees
GROUP BY [location Name]
Click to expand...

This works great, Thank you!

Instead of name I will be counting by ID. Within this count I have numbers
ranging from 100-900. Can I have this query also group and sum all numbers
within the 500-599 range? My result would look something like this:

ID Count
100 56
120 112
201 323
500's 619
697 12
etc.

All other location id's are counted normal, but I need any in the 500 range
to be counted together. Can this be done in a simple query?


Thanks!
 
SELECT IIF([ID] Between 500 and 599,500,[ID]) as LocID
, Count(ID) As NumAtLoc
FROM employees
GROUP BY IIF([ID] Between 500 and 599,500,[ID])


andy_42 via AccessMonster.com said:
SELECT [location Name], Count(*) As NumAtLoc
FROM employees
GROUP BY [location Name]
Click to expand...

This works great, Thank you!

Instead of name I will be counting by ID. Within this count I have numbers
ranging from 100-900. Can I have this query also group and sum all numbers
within the 500-599 range? My result would look something like this:

ID Count
100 56
120 112
201 323
500's 619
697 12
etc.

All other location id's are counted normal, but I need any in the 500
range
to be counted together. Can this be done in a simple query?


Thanks!
Click to expand...
 
Back
Top