Solved Maintain ability to update data across nested query recordset (1 Viewer)

ringah

New member
Local time
Today, 12:03
Joined
Jan 27, 2022
Messages
2
Using O365 (Access 2016) I have a table with time data:

Using a query based on it and CBO to select StaffID and WorkDate (this works fine), I can pull up the selected days data no problem and edit it:

1.png


End goal is to have a unique list of ProjectID on left column, and Time and Overtime as rows (each DateWorked with its own nested query) which is editable:

Mon Tue Wed etc
Time Overtime Time Overtime Time Overtime
ProjectID x y x y x y
1
2
3
4
etc.

So I've created a new query which uses DateAdd to constrain the next work days data (works fine on its own):

2.png


And then a new query to join the two:

3.png


At whhich point I get the data but lose the ability to edit, it doesnt seem to be the query criteria, UniqueValues or any edit properties, and a single base table:

4.png


At this point it hits my blind spot... its a standalone table so only self joins might apply ? create new SQL to insert a unique list of Projects from another table and then join the nested queries to that ? (can't edit, and UniqueValues or Union a no go for updateable recordset). Fallback to editable temp tables and update queries ?

Very much appreciate any pointers from experienced members of the group...
 

Attachments

  • 1643284470230.png
    1643284470230.png
    54.5 KB · Views: 288
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:03
Joined
May 7, 2009
Messages
19,169
you can achieve what you want by using a table (w/structure)

staffID (the staff id)
projID (the projec id)
mo_yr (month and year, yyyymm)
d1_norm (day1 normal hours)
d1_ot (day1 ot hours)
d2_norm (day2 normal hours)
d2_ot (day2 ot hours)
...
...
d31_norm
d31_ot

put this in a form.
on load event of the form, you will fill those fields (using VBA) from timesheetproj table.
on beforeUpdate of this form you add/update your TimesheetProject table.
 

ringah

New member
Local time
Today, 12:03
Joined
Jan 27, 2022
Messages
2
@arnelgp thanks ... get it with the table structure, will do it by week so can fit those fields on a form static, will figure out the VBA to handle the data..
 

Users who are viewing this thread

Top Bottom