lookup tables

[connerlowen]

Hi,

I am new to this website as well as Access. I am doing an internship for a company that wants me to create a database for them. One of the things they want the database to be able to do is hold all of the orders. The business makes parts with master part numbers that sometimes have more than one step and each step has an individual sub-part number. They are currently opening a new job for every step as opposed for every completed part and the database needs to remedy this problem. How can I create a new record within a table with master part numbers by looking up other sub-part numbers and information within other tables? also I need to sort the sub-part numbers in a step by step process.

thanks.

 

[Ranman256]

You have the table master part numbers. You will need a child table say: tBuild
This will have the MASTERPART and the sub parts.
tBuild
-----------
BuildID (auto)
MasterPart (text)
Step (long)
SubPart (text)


Add all masterparts, subparts, and steps in the child table.

 

[spikepl]

1. Since the company exists how do they do it now? Show current data as examples.
2. Can you imagine/show what the desired end output should look like
3. You mention a "job" - what is that and how does it relate to the parts?
4. Is there a difference in masterpart numbering and subpart numbering? Or just some parts get grabbed as masterparts and then subparts follow?

 

[connerlowen]

1. I do not think I can post actual information from the business here.
2. I know what I want the database to do, but I am not sure how to make it do that, or the best way to approach it. Within each department one specific step is performed. Not all parts must go through every department, and some can go through a department more than once. The employee in a department must log in, and see what he/she has to do and check a box indicating they have started the job, then log out. When the job is finished, he/she must log in and check a box indicating the job is finished, as well as answer some questions about the process. I want the database to know when the box for starting and finishing are checked. I also want the database to know that when the finished box is checked the part is ready to go to the next step and transfer over to the department that is next. There needs to be a main page where the management can go on and see where any given part is at any given time.
3. The job is basically either an internal process that happens at the company, or an external process for which we have to send the part out to get something done to it.
4. There is a difference between the master part and the subpart numbering. the master part is the finished product. the subpart number refers to each individual job that must be performed to get from raw material to finished product. The company wants to issue one work order according to the master part numbers that includes all of the steps and subpart numbers. This would save time, because as of right now, each time a step is performed the part must be checked back into stock until a new work order is done for the next step in the process then checked out of stock and so on until the finished product is finally produced.

I hope this explains it better.

thanks.

 

[jdraw]

If you are new to database and Access, here is a tutorial that will improve your database and design concepts. You have to work through it -30-60 minutes - but you will learn concepts that are critical to database design, table design, keys and relationships.

Good luck.