how to convert weekly based worksheet to Access DB

[eugzl]

Hi All.
I have attached the sample of weekly worksheet. Based on this, I would like to create a database, because every new week I have to create a new spreadsheet for keeping statistics. I can't figure out how to create a form that will look like an attached spreadsheet. Where the user will be able to update each entry on the day of the week regardless. JobID and JobName are persistent. If it's possible to do so, I'd appreciate any idea.

Thanks

 

[MajP]

This is somewhat a challenging problem to explain. It requires good database normalization which makes it very different from Excel.
You need a few tables.

tblJobs tblJobs

JobID	JobName
J10	Job Name 1
J11	Job Name 2
J12	Job Name 3
J13	Job Name 4
J14	Job Name 5
J15	Job Name 6
J16	Job Name 7
J17	Job Name 8
J18	Job Name 9
J19	Job Name 10
J20	Job Name 11

tblWorkers

tblWorkers tblWorkers

WorkerID	WorkerFirstName
1​	John
2​	Mary
3​	Joe
4​	Bill
5​	Mark
6​	Ros
7​	Sui
8​	Ben

tblStatus
tblStatus tblStatus

Status	StatusSort
Done	3​
N/A	1​
Pending	2​

And the hard one.

tblDailyJobStatus tblDailyJobStatus

jobStatusID	JobID_FK	ExcWorkerID_FK	TestedWorkerID_FK	JobStatusDate	JobStatus
1​	J10	1​	​	12/5/2022​	Done
2​	J11	2​	3​	12/5/2022​	Pending
3​	J12	3​	​	12/5/2022​	N/A
4​	J13	​	​	12/5/2022​
5​	J14	​	​	12/5/2022​
6​	J15	​	​	12/5/2022​
7​	J16	​	​	12/5/2022​
8​	J17	​	​	12/5/2022​
9​	J18	​	​	12/5/2022​
10​	J19	​	​	12/5/2022​
11​	J20	​	​	12/5/2022​
12​	J10	1​	​	12/6/2022​	Done
13​	J11	2​	​	12/6/2022​	Pending
14​	J12	3​	​	12/6/2022​	N/A

 

[MajP]

The tblDailyJobStatus is pretty complicated if new to Access. It is a Many to Many junction table and it holds 4 foreign keys. This also makes building the subform complicated if you have never seen it done.
This can get a little more use friendly with some code, but it is hard to build the form like you currently have using normalized access forms.

The form will open to the current week (required some code), but you can scroll using the buttons (more code). Each day of the week is a subform with pull downs for the job, status, exc, and tested. There are 4 stacked subforms. You can add more bells and whistles but this demonstrates a "normalized" database design.

 

[eugzl]

The tblDailyJobStatus is pretty complicated if new to Access. It is a Many to Many junction table and it holds 4 foreign keys. This also makes building the subform complicated if you have never seen it done.
This can get a little more use friendly with some code, but it is hard to build the form like you currently have using normalized access forms.

The form will open to the current week (required some code), but you can scroll using the buttons (more code). Each day of the week is a subform with pull downs for the job, status, exc, and tested. There are 4 stacked subforms. You can add more bells and whistles but this demonstrates a "normalized" database design.

View attachment 105109

Hi MajP. Thanks for reply.
This is a very useful idea. And an idea supported by an example is great. I will try to develop it close to my goal and I hope you will reply me on my next post. Thank you.