Create a Starting Number based on the Ending Number of Another Record

[watdahek]

Hi,

I am looking to create a headcount query that has 5 fields: Week #, Start Headcount, New Hire, Attrition, End Headcount

I would like Week #2 Starting Headcount to = Week #1 Ending Headcount

I keep running into a circular reference issue and I completely get why. If I create a Week #1 Starting Headcount Table with static numbers can I avoid the circular reference? Any assistance in solving this issue is greatly appreciated.

Thank You,
Steve

 

[UncleCatwoman]

I've done something similar. I added a record with a starting headcount and then wrote a query summing the starting headcount plus all new hires minus terminations for the current headcount.

 

[watdahek]

I've done something similar. I added a record with a starting headcount and then wrote a query summing the starting headcount plus all new hires minus terminations for the current headcount.

Can you explain more what you mean by "added a record". I tried creating a starting headcount query but it is still circular.

Thanks

 

[UncleCatwoman]

How are you storing your data? Is it in a table? And does the table go back to the beginning? I'm assuming you're talking about employees here.

If the table has every employee in it, then COUNT([NewHires]) - COUNT([Attrition]) should give you your starting number.

If the table doesn't have every employee, or it stores different data, you'll need to find a way to include your starting headcount. You can add a record into the table (if that's appropriate) or you could just hardcode the number into the query [15+COUNT([NewHires])], since the number you started with won't be changing.

 

[xenou]

I am looking to create a headcount query that has 5 fields: Week #, Start Headcount, New Hire, Attrition, End Headcount

I would like Week #2 Starting Headcount to = Week #1 Ending Headcount

I keep running into a circular reference issue and I completely get why. If I create a Week #1 Starting Headcount Table with static numbers can I avoid the circular reference? Any assistance in solving this issue is greatly appreciated.

Thank You,
Steve

What is your query you have tried that isn't working?