To Join Tables or Not To Join Tables

[kurtis51]

That is the question:
Whether 'tis nobler in the mind to suffer
with VBA Programming and the outrageous errors,
Or to take up arms against a sea of Access troubles.

Sorry I was in the mood for Shakespeare.

A quick summary first:
In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.

What I'm trying to do is this:
Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.

Then on the form itself I should just be able to enter in the data that is required for the fields.

So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.

SELECT Model.ComputerID, Make.ComputerID
FROM Computer
INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;

 

[RuralGuy]

Are you aware that SubForms were designed to display the many side of a 1:M relationship with the table bound to the MainForm.

 

[kurtis51]

I'll have to look into that for displaying.

Here are my following issues:
1) I have no front end main Form. I need a front end in order to have my colleagues at work be able to enter Data on one screen.

- Reports and Sub-forms can come after that, and I will probably go with Sub-Forms.

2) Every Form I've tried to create with the particular fields I need (all of them except the Primary Key's) are not saving the data I enter.

This is probably due to the fact that I have no recordsets created for the tables...

3) Let's take the Employee Table for example. I have a Foreign key called wrkEmployeeID in the Workorder Table that is linked to the Employee Table. I need all of those fields from Employee (minus the Primary key of course) into the Workorder Table so that I can enter in information for each field...

So I'm really reading up and racking my brain and I think I'm asking the right questions to ask. I know alot of the above seems redundant and I apologize for that. It's hard to explain the process for a project that could potentially be used across NA...possibly the world...

 

[Pat Hartman]

Each table in the schema should have its own form or subform for data maintenance. Don't try to update multiple tables with the same form.

 

[kurtis51]

Each table in the schema should have its own form or subform for data maintenance. Don't try to update multiple tables with the same form.

Each Table does have it's own form now, I finally realized that a week or so ago. So that's all set.

Then how am I able to populate the data for this DB? I can't use Queries because there is no data to speak of.

 

[kurtis51]

A better question would be, how should I go about entering data?

 

[Pat Hartman]

It sounds like you are expecting Access to propagate data from one table to another. That's not the way it works. you have to input all the records. If you use subforms for child data, Access will correctly populate the foreign keys for you as long as the master/child links are properly set.