Subquery? for total within same table

[mcalex]

Hi, I have a table with Company, SectionName, NumberOfStaff, and I want to run a query that shows this data, plus a column for the total of staff in each Company, obtained by adding the staff in the different sections.

I'm obtaining this number via its own query using Sum(NumberOfStaff) and Group By Company, which is then joined to the original query in the designer window. Is it possible to do it all in one query (maybe with a subquery, I'm not sure) - given that all the data comes from one table?

The reason for doing this is that the query is part of a larger macro that runs for about 5 minutes and (once I split the database) was returning 'Could not open any more databases' error. My job is to go through the macro, cleaning up all the extra bits and multi-nested queries that are causing this.

I have a feeling this is pretty basic, but my searching hasn't found anything. All info on subqueries seem put the subselect within the Where clause or Having bit, but I want it in the Select area.

thanks for any assistance

 

[mcalex]

Ha! sorted.

I had to alias the tables in the select statements so that the table could be joined to itself.

So,

select t1.company, t1.sectionname, t1.numberofstaff,
(select sum(t2.numberofstaff) from staffcount t2
where t2.company = t1.company)
from staffcount t1

 

[neileg]

You could use a calculated field using DSum instead of the sub query. Might hurt performance, though, if you have a lot of data.

 

[mcalex]

Hi neileg,
how do I do this when the calculation depends on adding field values from this table. i use the field I want to sum as the <<expr>>, the table as <<domain>>, but what is the criteria? It would be something like all the company fields that match the company field for this record.

My first attempt resulted in access prompting with a query parameter dialog for the table name (it didn't recognise that the table is a database object). When I tried again, it told me the fieldname (that I'm comparing to - itself) could come from more than one table. When I try to include the same table in the query again with an alias, so DSum can compare the field value against something, I get a syntax error in the join operation.

aaaaaggghhhhhh!