Update query headache

[dvault101]

I've been assigned the task of tracking vacation hours for a small group of employees out in California. California has some interesting rules about employee vacation, among which is that once time has been earned, it stays available to that employee until they use it or are no longer employed (where it would be paid out).

The attached tables.txt is a csv with my tables and structure. I'm using access 2007.

Now, here's where my headache comes in. Once an employee has reached a set cap, they no longer accrue hours until they use their time and drop below the cap; at which point they start accruing again (from the time they went below the cap, not where they first hit the cap).

I know it isn't considered best practice to store a calculated value in a table, but I don't see much choice. What I'm trying to make happen is to set the vac_earned field to equal [agents].[vac_accrual] x sum( [accrual].[earned] ). I'll be using that value to decide whether [agents].[accrue_stop] is on or off.

I'm not concerned about out-of-sync data because I intend to run the calculation as part of the autoexec macro and I'll be the only one using the database.

When I try to run the update query, it either tells me the operation must use an updatable query or that
You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.
:banghead:

Any thoughts?

 

[plog]

I have no idea what I am looking at with your attachment. Honestly, the forum allows you to paste in text, that would have saved me a download if you just pasted it into your post. And it would have allowed you to verify it appears as you wanted it to.

I don't know what you are trying to do with all those commas. I don't know if I am looking at 3 tables, or 1 table, or if I'm looking at the fields of a query (I don't see any SELECT clause so I know its not SQL).

I don't think this is something you need to store. When you start talking about autoexec macros to update data, it really sounds like you will be better served by a SELECT query to do the calculation whenever you need it.

 

[DavidAtWork]

From what I can see in your attachment, there are 3 tables: Accrual, Agents, SickVacUse
Am I correct in assuming SickVacUse.Event_ID links to Accrual.Cycle_No, if not I don't see how you can calculate
vac_earned field to equal [agents].[vac_accrual] x sum( [accrual].[earned] ) because there's no way to join [agents].[vac_accrual] [accrual].[earned]

David

 

[dvault101]

Hi David,

Unfortunately no, the sickvacuse.event_ID is not related to the cycle numbers. Employees take time as they need it, so if they wanted to, they could take 1 vacation day in 10 different payroll cycles so there'd be 10 records in the sickvacuse table. But I can create a cycle field in the table that would assign the cycle number for each event based on its start date....