If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Access Relationship help.

Hello everyone, I've just been hired as an engineering intern at a manufacturing company and my manager has askedme to set up a database in Access 2007 to keep track of my companies assembly instructions. i'm new to access, but I started working on this a couple weeks ago and I've learned a lot so far(I only work about 10 hours a week because of school so I haven't got too far, but anyways...). Below is some information about the database and what I'm trying to accomplish.

tblOperations = contains a list of different operations which may be performed in the assembly (Cut cable, cut tubing, print label, stamp tubing, etc)

tblProductOperations = contains the part#'s as well as a Step# for the assembly process and the operation which is performed for that step. There is also a unique ID for each step (For example StepID#: 15, Part#: 60701D1040, Step#: 1, Operation: Print Label)

Then I have a seperate table for each operation because they each require between 3 and 8 columns to convey all the information. For example, the field(column) titles for the "Blocking" Operation are StepID#(number), Part Number(text), To End(Yes/No), From End(Yes/No), Both Ends(Yes/No), Details(text). But for the "Cut Tubing" Operation the fields are StepID#(number), PartNumber(text), Tubing P/N(text), Quantity(number), Length(number).

Currently i have the relationships set up so that if you open "tblOperations" and click on the + next to a certain operation it will open a subform of "tblProductOperations" showing every part number which uses that operation.

Also, when I open 'tblAPI' I can click on the + icon and open a subform of tbleProductOperations which shows the step and operations.

The next thing i'd like to be able to do is to open another subform within tblAPI>tblProductOperations which shows me the actual information for that operation(that operation table). Can anyone help me out here? I believe it's a many to many relationship but I'm not positive. (remember I'm new to this)

i'd attach the database file but it's extremely large, so i'm going to attach some screenshots so you have a better understanding of what i'm trying to accomplish.

Any help at all would be greatly appreciated. thank you very much in advance.

In your situation, I would recommend a consultant. There's just too much to advise and I don't feel like writing a book about it for nothing. A couple of hours with a good consultant and you should be able to shortcut a lot of this.

Couple of tips:

Don't let users enter data via tables.
Forget about the + signs.
Your table design is a mess. All those tables fanning out at the right is a recipe for failure and would be a nightmare to work with.
I think you're confusing subforms with those + signs.

Thanks Startrekker, I think your right...I've been trying to figure this out all day and I know where the database is at right now isn't the right direction. It's just frustrating because I've been entering data from different word documents and I feel like all that information is going to be lost...because my company is too cheap to hire someone who knows what they're doing. They'd rather just pass the project off to an intern who's never touched access before.
Anyways...thanks for the reply.

Yeah, there are a lot of what I call "cheap-ass" companies that do all kinds of stupid things just to avoid a couple of hundred dollars. The funny thing is that they all end up paying more for their desired results in the end.