how to construct this query? software count by room

[Song Su]

I have two tables: computer, software with 1 to many relation on 'serial'
'computer' table fields: serial, name (first 6 character identify room)
'software' table fields: serial, description (software name)

how to construct a query that give me software (description) count by room?
I want:

description room count
Adobe GoLive E7-321 20
MS Office E7-321 35
MS Office E5-411 21

thanks.

 

[John Spencer]

Does the Serial field in Software identify the computer in the computer table?
If so, you can use the left function to identify the room.

SELECT Software.Description
, Left(Computer.Name,6) as Room
, Count(Software.Description) as Units
FROM Computer INNER JOIN Software

 

[Song Su]

perfect! Thanks a lot!

Does the Serial field in Software identify the computer in the computer
table?
If so, you can use the left function to identify the room.

SELECT Software.Description
, Left(Computer.Name,6) as Room
, Count(Software.Description) as Units
FROM Computer INNER JOIN Software