Field Automation Coding

[akrylik]

I have a database in which we are trying to have a field "Headcount" automatically fill with the correct value based on information in multiple fields. The "Headcount" field will be based on about 6 or 7 rules. Most of the rules i can figure out, but im having trouble figuring out what the best way would be to search for a single individual's projects when they are on more than one project at a time. i thought about using a counter of sorts to count how many projects someone was one, but how to i pick them out of the entire table once i have found out the number of projects? Would a for loop work best or just many if statements? I guess my biggest problem is finding out how to determine if someone is on one or many projects.

[John_G]

Hi -

It sounds very much as if your data is not properly structured - i.e. is not properly normalized. If the projects an individual is on are all listed in one record, then it is not normalized. Can you show us your table structure?

John

[orange]

Part of the answer probably depends on the structure of your database. Do you have a table in which Projects and People are related.
For example,

Project X involves Jim, Tom, Jason, Sarah and Fred.
Project Y involves Tom, Sam
Can you post a jpg of your Tables and relationships?


OOOps. Looks like John was posting while I was typing.

[akrylik]

I did a bad job explaining it. I will get a sample database together here shortly. The idea is to track employee's jobs and if they are working on more than one job, then only one would be counted in a headcount calculation. for now, i think i will focus on getting a counter working. im new to VBA coding, so how would i set up a for loop to search through a table and count how many records have the same last name?

[John_G]

Hi -

You can do that without VBA - all you need is a totals query with Group By on the last name. But that could be an issue - what do you do if two or more employees have the same last name? You should use group by on a field that uniquely identifies the employees.

John

[akrylik]

Ight ill give it a try. i will get a sample db up tomorrow or friday. Sorry its taken so long to get it up here.