Starting a new project

Jason1

Registered User.
Local time
Today, 16:28
Joined
Mar 12, 2010
Messages
63
To start out, I would like to say hello to everyone. I just joined the forum. I have built a small database in access 2007 (my first time). It works, but not as well as I would like, so I'm trying to refine it. I am trying to do it the correct way this time as opposed to making it work which is what happened with the original.

The current database is used to collect batch data on products we are producing.

I have a table for products (tblProducts). This contains all of the raw materials need to produce that product. I have another table for empoyess (tblEmployees). This contains all the information related to the empolyees. Then a third table to collect the data (tblMainProduction). This table uses the drop down menus in some of the fields which are populated by tblProducts and tblEmployees.

I also have a form (frmPlant) which allows someone to enter information into the MainProduction table.

Currently there is a batch number field in the Main Production table that is used as the primary key. In our process each batch goes through 3-4 different processes. There is data that needs to be entered during each process. Currently all of the data for all the processes is entered on one form. For example, when the product is made and a record is entered using the plant form, during this step a batch number is given, and about half of the fields in the form are completed. Then down the line, the product is packaged. Another person then has to use the record arrows to look up the same record on the form, and complete the form. All the information is then stored in one row on the Main Production table, tied to one batch number.


What I would like to acheive is to have the two seperate forms. For example one form for the production division that would generate the batch number and it's relative information. And then a seperate form for the packing department, but still have all of the information stored on one line in the main Production table, tied to one batch number.

Basically I am asking is there a way to have two forms coorespond to the same table, and edit the same record?

I hope this isn't too confusing. I read the thread about posting questions, and I think I still screwed it up.

Thanks for the help,
Jason
 
Basically I am asking is there a way to have two forms coorespond to the same table, and edit the same record?

Absolutely Jason you can have limitless number of forms looking at the same data.

As for how this is achieved will depend on finer detail of your processes and what each department needs to know from the information.

by the way welcome to the forum
 
lightwave,

Thanks for the response. Each pallet of product (i.e. batch) has a paper attached to it showing the batch number, and product code. I would like the employees in the other departments (i.e. packing) to be able to call fill in a form with packing information related to the batch number.

I have made another attempt at this. I have made a different forms and table for each department. The production form generates the batch number, and it is shown in the other forms via a drop down menu.

Then I query all of the forms to consolidate the batch infomation. Does that sound like the best way to do this?
 
Can I just ask you some questions to get a feel for the structure I am still struggling to understand what the structure needs to be like.

Have you used the relationship screen to relate the tables?
I suspect you shouldn't need to have a drop down field for the batch for instance because it sounds to me as if that is the parent record.

Would it be possible for you to list the fields in the tables or just post a simple jpeg of the structure of the relationships?

Could you list the departments?

How many products do you have?

Do all products go through the same departments?

Does each department have exactly the same information requirement albeit the values are different?

Slightly differnt question to above.
Does each department perform the same process?

Might help if I knew what the products actually are?

With the database what is the main objective?

Are you trying to identify amount of material used?
Alternatively are you trying to track what departments the product has been through?
Is it supposed to be an all singing all dancing stock control system?

ie what is the main problem that you experience at the moment with the former system?

To date I've never designed a database with different tables specifically related to differing departments as while I have had different forms I've had the tables designed around the focus of the data. I can see that in a manufacturing environment with several departments performing completly different processes this might be relevant.
Although there are several things that may allow you to simplify the process.

Stick with me and I'll get it sorted just need to understand your requirements better!
 
lightwave,

I appreciate the effort. I don't have acces to the database file from my home. I will be able to get you some pictures of it tomorrow. I would like to try to answer your questions now.

All of the tables are related through the batch number. In the production form (each form has a cooresponding table) the batch number is the primary key. This form is filled out by the machine operator when our raw product is produced. When the record entered into the production table it is given a primary key.

The cutting process is the second step in our production process. Once a pallet of product has been cut (one batch) the cutting form is filled out. This form has a batch number field on it, which is set as a lookup to the production table batch numbers. There is no primary key on the cutting form.

There is then a packaging form. Same situation as the cutting form. When the batch has been packed the packaging form is completed. there is a field which is set as a lookup to the production table batch numbers.

I am then able to query the all of the forms and group the information for all the processes by batch.

When I set up these lookups relationships were automatically formed between the batch number field on all three forms.

-Second question-
Departments: Production, cutting, packing

-Third question-
Around 30 products, but all relatively the same, processed the same, cut the same way, packaged different ways.

-Fourth questions-
All the products go through the same departments, but each department have different requirements.

-Fifth question-
I can't tell you what the product is. I know that sounds crazy, but I could get in trouble. Its not illegal.

-Six question-
The database has a couple of objectives
1-keep track of the raw materials used in each batch
2-keep track of the yield of our raw materials
3-keep track of the productivity of the employees and our processes

I am attempting to build a very watered down ERP.

The problems with the existing database is that everything from the three forms above is entered into one form. So the same record on the same form gets edited over and over, and it makes it impossible to use date and time stamps, and with the records being edited over and over, it makes it easy for people to screw it up.

I really do appreciate you help, and as long as you keep answering me I'll keep giving you information. This is really what I need someone to hold my hand and help me through this thing.

thanks again,
Jason
 
Thanks chief that is all very useful - I guess I'll have to take your illegal aspect on trust!!!!!!

I've just spent the day out at the pub watching Scotland vs England (draw)

So I think you roughly have the right tables but maybe the link between them ain't correct and some of the subtleties between tables may not be correct.

I'll have another look tomorrow but if you can post your tables would be a help.

Also what version of access are you I am 2003?

Are you in America or the Uk?

Curious
 
Last edited:
Lightwave,

I'm running 2007. I am going to try to attach the file. I don't know if it will work. I think I have the tables worked out, but have a look and let me know what you think.

the next problem I have to solve involves printing 2 sheets of avery labels when a record is submitted on the form. The trick isn't printing the labels I have that covered, the trick is that the labels all have to be the same as the record that was entered. Not one label for each record, 40 labels of the same record.

Alright, it won't attach. pm me your email address and I'll send you my database so you can check it out. ahh damn, you have 2003. give me some suggestions?
 
...I am asking is there a way to have two forms coorespond to the same table, and edit the same record?

The easiest way to accomplish this is to not base the Forms on any Tables, and instead base them on updatable Queries to the Database that return the information that is required for the Form. You can have as many variations on Forms as you need, by creating Queries that meet each group's needs.
 

Users who are viewing this thread

Back
Top Bottom