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:
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):
And then a new query to join the two:
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:
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...
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:
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):
And then a new query to join the two:
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:
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
Last edited: