Relating tables to each other using checkboxes

[johannaellamay]

I'm not sure if the title is very appropriate or direct to the point. But here's what I'm working on.

In our organization, when employees need to ask for office supplies, they have to fill out a Supplies Request Form (SR). Then Procurement team makes a Purchase Order Form (PO) based on the SR. One PO could have one or more SRs. Then once the items have been delivered by the supplier, the Receiving team makes a Receiving Report Form (RR) based on what was delivered. One PO could have many RRs.

What I want to create is like the one in the Northwind Traders template in Access 2010. Where there is a Purchase Order and a checkbox to "Post in Inventory." When clicking the checkbox, the ordered items will automatically reflect in the inventory. However, mine is a little bit more complicated. When there's an SR, I want to have a checkbox that will automatically reflect on a new PO. I also want a checkbox for PO that should automatically reflect on the RR. And the RR should have another checkbox linked to the inventory which should be able to update the total available stocks.

I'm not sure if I'm making sense. But, yeah. I'm new. I'm stuck, so please help. :banghead:

 

[johannaellamay]

Already have. It's not exactly what I'm looking for.

 

[jdraw]

Do you have a data model? Where exactly in the design/development process are you now?

 

[johannaellamay]

Do you have a data model? Where exactly in the design/development process are you now?

Yes.



View attachment Model 1.accdb

But those are the old ones. I'm still trying to re-do everything and I'm not quite finished yet. I'm still trying to make the model. I know that it's a bit messy. But just to let you have an idea of where I am currently, here's my progress.

View attachment X.accdb

It's still not complete as I have to still add a couple more tables. I'm just stuck because I don't know how to relate the checkboxes.

 

[jdraw]

Do you have a list of rules for the business, or a narrative that describes all of the business you are trying to support with this database?
In t_Employee I'm not following the need for Lastname, FirstName, MiddleName, Suffix, NickName and then EmployeeName ---seems excessive, but I don't know your business.
Also, why is Department in t_Employee? Can an Employee change Departments over the course of Employment? Similarly couldn't the Supervisor change with time?

What would you do if an Employee changes Position or Department?

I'm not trying to be negative, I'm just questioning some fields/attributes in the table. Your choices may be correct, but it is by comparing your model to your "agreed upon" business rules and needs that will determine the proper location of and/or need for fields.

What exactly do you mean by relating the checkboxes?
I would not store Totals in Tables. You calculate them when needed using queries, usually.

Tony's suggested link to Allen Browne's AppInventory is good for concepts and technique even if it isn't specific to your "checkbox issue".
In general Inventory management/Stock Control tends to be more complex than general envisioned.

Are you developing a new application or modifying an existing set up?
Good luck with your project.

 

[johannaellamay]

Do you have a list of rules for the business, or a narrative that describes all of the business you are trying to support with this database?
In t_Employee I'm not following the need for Lastname, FirstName, MiddleName, Suffix, NickName and then EmployeeName ---seems excessive, but I don't know your business.
Also, why is Department in t_Employee? Can an Employee change Departments over the course of Employment? Similarly couldn't the Supervisor change with time?

What would you do if an Employee changes Position or Department?

I'm not trying to be negative, I'm just questioning some fields/attributes in the table. Your choices may be correct, but it is by comparing your model to your "agreed upon" business rules and needs that will determine the proper location of and/or need for fields.

What exactly do you mean by relating the checkboxes?
I would not store Totals in Tables. You calculate them when needed using queries, usually.

Tony's suggested link to Allen Browne's AppInventory is good for concepts and technique even if it isn't specific to your "checkbox issue".
In general Inventory management/Stock Control tends to be more complex than general envisioned.

Are you developing a new application or modifying an existing set up?
Good luck with your project.

Wew this is gonna be a long one to process. Okay, I will explain it, and will answer your questions in my next posts.

 

[johannaellamay]

General Conditions

1. I am trying to track inventory levels and the flow of procurement and receiving of items.
2. The organization has several sources of funds called "DSPN". Each source of fund could be given to more than one department.
3. There are also different departments in the organization. A department could have one or more DSPNs.
4. There's a table for inventory details. Each item has a code.

PROCESS

1. An employee needs certain items, for example, office supplies.
2. He fills up a Supplies Request Form (SR), puts in his office, department, date, item description, quantity, unit of measure, his name, name of the approver of the budget, and the sources of funds.
3. The SR is forwarded to Procurement. They go out to canvass for the items then fill up a Purchase Order Form (PO). One PO could contain many SRs. It contains supplier info, approver, etc.
4. Once the supplier delivers items, the Receiving Team, fills up a Receiving Report Form (RR) according to what has been received, also in relation to the PO. One RR could have many POs.
5. The property custodian (me), could also request for bulk order of office supplies so when employees request for something, they don't have to make an SR, they can come to me and ask for the supplies. Whatever will be released should be replenished using the sources of funds being used by the requester.
6. When employees ask me for items, I let them sign an Acknowledgement Form which should contain information such as the item description, quantity, signature, etc. This should then be recorded as "out" or should be subtracted from the total items available.

So there's a total of four major forms: SR, PO, RR, and Acknowledgement.

 

[johannaellamay]

To answer your questions/concerns, one by one...

1. Do you have a list of rules for the business, or a narrative that describes all of the business you are trying to support with this database?
   • Please see previous post.
2. In t_Employee I'm not following the need for Lastname, FirstName, MiddleName, Suffix, NickName and then EmployeeName ---seems excessive, but I don't know your business.
   • They're all required. Not only do I have to store information for inventory purposes, I also want to integrate HR information.
3. Also, why is Department in t_Employee? Can an Employee change Departments over the course of Employment? Similarly couldn't the Supervisor change with time?
   • An employee should belong to a department. Yes, an employee CAN change departments, and yes, the supervisor can change over time. Why do you ask? Am I gonna have problems about this in the future? I mean, I would be able to change the departments and immediate supervisor of each employee right?
4. What would you do if an Employee changes Position or Department?
   • Just change the job title and department in the table, I think.
5. What exactly do you mean by relating the checkboxes?
   • I'm making a table for Supplies Request, and I'm making a table for Supplies Request Details. In the SRD, I want to have checkboxes. When one is clicked, it should automatically reflect in a PO. When clicked, it means that a certain item has alread been purchase ordered. There's another checkbox for the RR. When clicked, it should mean that the item has already been received. How then should I be able to do link the checkboxes? Should I be concerned about them now?
6. Are you developing a new application or modifying an existing set up?
   • 
     Developing.

 

[jdraw]

A few more comments for consideration based on your responses:
I also want to integrate HR information.
I don't know how big your organization is, but if you're recording Country with Employee, it seems large.
I recommend you set up to copy/link to what you need from HR. The last thing you want or need is two copies of HR data. Your table for Employee should be a ReadOnly access to HR (in my view). If you have new employees they should get registered in HR, not procurement. Your organization should have 1 EmployeeID per employee regardless of which system/database is involved. (My background is corporate so your needs may be different but you want to avoid duplication of systems, databases, sources etc where practical).

Yes, an employee CAN change departments, and yes, the supervisor can change over time. Why do you ask?
Do you need to know the history of such moves? If so you may want a table - tblEmployeeDepartment. It would be a junction between Employee and Department. You might want to know when the Employee was in which Department/Position. In fact this may by more HR than Inventory management.

Am I gonna have problems about this in the future? I mean, I would be able to change the departments and immediate supervisor of each employee right?
If you need to keep history, you're going to have t allow for it. Can a supervisor change independent of an Employee changing departments -seems so, so you may need a separate EmployeeReportsTo table (and you may need history so you might record the date involved.)

I'm making a table for Supplies Request, and I'm making a table for Supplies Request Details. In the SRD, I want to have checkboxes. When one is clicked, it should automatically reflect in a PO.
I think this would be more appropriate on a Form - part of HOW the WHAT is i mplemented. During processing, if a checkbox is "checked" your behind the scenes code would do whatever process(validate/update/insert etc) you want. Strongly advise not to interact directly with tables.

I know when developing there is a strong urge, and often a demand from management, to see some code etc. But from my experience I recommend you spend the time on design and validation of rules. I like to work from models, and some test data scenarios.
As I have said elsewhere, a good practice in my view is the "stump the model" game. You have the latest model for display to participants. Participants can be users, developers and management. Participants run test scenarios against the model. Anything that doesn't work (an anomaly) is documented and must be reconciled. Is it the data; the model; the rule; a new requirement...etc? The purpose of stump the model is for all to be on board. You are vetting the model - not harassing the modeler. You adjust the model/test data or scenarios as necessary and have another "stump the model" session. It does not have to be same participants since part of this exercise is to communicate the "new/revised processes and structures" to as many as necessary. Better to get people involved as participants than to drop something on them that doesn't match their needs. This is a team building effort since it is focused on the business and the participants within. It's surprising how many people can be involved with a database application and not know what/how others use the system.

Another thing to keep in mind, especially because of the potential link with HR, is that you can design the larger database/model and implement based on your organizations's priorities. With the bigger design (much like the artist's concept of the shopping mall) you can build various pieces as priorities dictate but knowing where the pieces fit within the big picture.

Enough for now. Good luck.

 

[johannaellamay]

Hi jdraw, you've talked previously about saving history? Do you maybe have a sample model for that? I really could not imagine how I would be able to store the field's history.