Sum in combobox

[reginaldmerritt]

Hi,

Don't know if this is even possible but someone out there might be able to tell me otherwise.

I have a table with a list of names, lets call it TBEmployees. I use this table to list all employees in a combobox.

The Employees are linked to another table, lets call it something like TBSite. Employees can work in many different sites lots of times, therefore they can appear in TBSite many times. TBSite has a field called AreaID, where a number is used to represent the Area.

Is it possible for the combobox mentioned above to list not only the Employees but also the number of times their name shows up in TBSite with a certain AreaID?

So essentially the combobox will list each employee plus it will sow the number of times that Employee has worked at a particular Area.

Is this even possible?

Any help would be great.

EDIT:
I'm guessing that the comobox would need an expression something like:

DCount(["EmployeeID]","[TBSite]","[AreaID] = 2 And [EmployeeID] = " & ????? )

Where ??? is the EmployeeID in the combobox or rather the record in the recordsource for the combobox

[Mariostg]

You should build a query that would populate the employee along with the count of employeeId from the TBSite table. Then source your combo box with this query. You can have more than one column in the combo box.
I don't know your table structure, but it could the query would look like this

SELECT tblEmployee.EmployeeID, Count(tblSite.tblEmployee_id) AS CountOftblEmployee_id
FROM tblEmployee INNER JOIN tblSite ON tblEmployee.id = tblSite.tblEmployee_id
GROUP BY tblEmployee.EmployeeID;

[reginaldmerritt]

Thanks Mariostg, sorry for the late reply.

I can see from your suggestion how that might count the number of times an employees id would appear in the table site. What I really need it the count to only count if the employee id appears in the table site and uses a certain area id.

I tried to just use the SQL you suggested, but i got the following error.

"You tried to execute a query that does not include the specified expression 'EmployeeID' as part of an aggregate"

I don't even know what an aggregate is, some sort of total figure or something I guess. Any ideas on what I'm doing wrong?

[Mariostg]

Hi reginaldmerritt

That is why I said I don't know your table structure...
I obviously used wrong table/field names. I assumed you are familiar with the query designer and your question was more about whether or not you can have more than one column in the combo box. Sorry.

Here is a second guess

SELECT TBEmployees.EmployeeID, Count(TBSite.EmployeeID) AS CountOfTBEmployees
FROM TBEmployees INNER JOIN TBSite ON TBEmployees.EmployeeID = TBSite.EmployeeID
WHERE TBSite.AreaID=2
GROUP BY TBEmployees.EmployeeID;
 

I added a where clause that should take care of your counting if areaID=2. But again I am guessing your table design but it should give you an idea.

And yes, an aggregate is one of those functions like count, sum, average, etc...

On a side note, you should consider dropping the s at the end of your table name. Unless all tables do have an s, it gets confusing to remember which one is plural and which one is not.

Cheers.

[reginaldmerritt]

Thanks,

I can see how adding the where clause will give the functionality I need.

Excellent tip on the table names, I have found this a problem before.

Using the following I still get the same error as mentioned before.

SELECT TBEmployees.EmployeeID, Count(TBSite.EmployeeID) AS CountOfTBEmployees
FROM TBEmployees INNER JOIN TBSite ON TBEmployees.EmployeeID = TBSite.EmployeeID

[Mariostg]

Obviously there you need the GROUP BY clause. When you use aggregate function, you must include in that GROUP BY clause the fields that are part on the SELECT statement that are not aggregate. Not sure if you get my wording here...

[reginaldmerritt]

Ah i see.

I miss understood the purpose of 'Group By'.

Mariostg, thank you this works perfectly. Sorry for my slow up take, i've not worked with SQL directly much.

[reginaldmerritt]

Sorry another question.

I want to create a display that will show each employee and the areas they have worked in.

I could create a query with a different where statement for each area. Using the query you have helped me with.

But what I would really like to do in addition, is to show each employee with a column for each area. Does that make sense?

[Mariostg]

SQL can be query at times, but it is powerful.

You may want to start a new thread for your next question as it does not seem to be related to combo box. And since this thread is marked solved, there is little chance people will come to try to help.

In any case, not sure I get your question, but if you want say employee names on rows and area of work on columns, you are looking at a crosstab query. You can use the query designer for this.

[reginaldmerritt]

Thanks, I'll have a look into crosstab queries. If I need any help I'll make a new post.