1 to many relationships-entering data in 'many' side

[sidewayzalex]

Hey,
I have several questions related to a database I am working on for my employer, but I figured taking each issue on one at a time is best.



I have 2 main forms in which I can enter information. One form has 2 subforms, where the other has 3. I was wondering if there is a way for me to enter information in the forms and appear in every table related to it?

What I mean is that I can enter the information directly related to the table the form is based around, but cant enter information that is related to other tables unless the other tables already have that information stored. Is there a way I can enter this information and it gets stored in every table related to it?

I am happy to supply screenshots and information if necessary.

Thanks!

[NoellaG]

Hi,

if you have troubles entering 1 to many info this could be an indication that the database structure isn't normalized. Perhaps you couls include an example of your database, or a print screen of the relationships?

gr
NG

[sidewayzalex]

Hey,
I believe I have just attached the image. I don't know if there is an easy way for me to print screen and paste, so I just threw it in paint, and saved as jpeg.

The table 'BOM1' is the main table for the form, 'BOM1', which has 2 subforms.

'Shaft Information' is the main table for 'Worksheet for dyno shafts' form, which has 3 subforms.

Let me know what other information I can supply.

Thanks

I do belive that the customer Orders form is pretty much not necessary, but I do not know for sure

[rpeare]

If you're using a form/subform design you shouldn't need to copy ANY information from one table to another, the parent unique identifier should be your connection to the secondary table and any time you enter something on your subform, if properly designed, should inherit the parent primary key.

Just a note here, some of your tables do not have primary keys. *Every* table should have a primary key (autonumber is the easiest to manage). Every record should be able to be identified by a single field. You should make that change. Secondly, access really does NOT like special characters (#, spaces, &, *) marks in object (field names, table names, query names, etc) names. And it should be avoided.

can you say specifically on your form with three subform which four tables are involved?

[sidewayzalex]

I will work on the primary keys on this ASAP. I figured it was something to do with relationship problems. microsoft access tips on blogspot was very helpful with helping me learn what the relationships are and how they can be used, which i didnt find until 10 minutes ago.
As for the tables that are involved...
In the BOM1 form there are 'parts per order', 'BOM1', and 'labor per order': BOM1 is the main table.
In the 'Worksheet for dyno shafts' form, there are 'final driveshaft balance', 'components replaced', 'shaft information', and 'driveshaft runouts': Shaft information is the main table.

Thanks for the help so far.

[sidewayzalex]

Should I make the 'Parts per order' table have JCS# and PartID as primary keys, even though there can be any number of JCS#'s per part and any number of parts per JCS#?

I guess I would have to ask the same question for 'Customer orders' table.
I am simply trying to use these two tables as Junction tables

[rpeare]

Ok, there are a couple of things I see right off

First let's focus on the form you originally mentioned (the one with three subforms).

Your main table for this is SHAFT INFORMATION which has a 1 to 1 relationship with both FINAL DRIVESHAFT BALANCE and DRIVESHAFT RUNOUTS. Is it possible for you to have a record in SHAFT INFORMATION and no information in the other two tables and have it be a valid record? I'm asking because typically with a 1 to 1 relationship if ALL of the information is going to be entered at some point there's no real reason to split the table into multiple pieces. That aside, if your main form is based on SHAFT INFORMATION and you have a subform for the other two tables if you establish the link as SHAFT # then any time you enter new information on one of the subforms the SHAFT # field will automatically be populated in your two subtables.

The potential problem I see is with your relationship between SHAFT INFORMATION and COMPONENTS REPLACED. You have COMPONENTS REPLACED on the ONE side of the relationship and SHAFT INFORMATION on the MANY side of the relationship which strikes me as odd typically your forms/subforms, but from what you're saying SHAFT INFORMATION is the 'main' table for this data entry which means your SHAFT INFORMATION should be on the ONE side of the relationship and COMPONENTS REPLACED the MANY side. Additionally you are not relating any of the COMPONENTS REPLACED To the SHAFT they were replaced on, you will need to carry the SHAFT # on the COMPONENTS REPLACED table to correctly relate it to the item in your SHAFT INFORMATION table. It's ok to have both the SHAFT # *and* the field you currently have as your link field you just have to define both in your form/subform and make sure both tables have both pieces of information.

[sidewayzalex]

Hmmmm. I will take a look at that. I tried entering information, just a quick test, into the form (specifically in shaft#) to see if it would automatically update all 3 sheets, but it gave me an error message saying that Final Driveshaft Balance doesnt have th information and needs it first.

My plan is to make a new database, based on what I got from making this one. If you could give me any tips for making the new and improved database, that would be great!

If you couldnt tell, this is my first time working with access. As I am off of work in 12 minutes, I will continue reading up on Access so as to have a better idea of how to work with it.

[rpeare]

No, you're missing my point. when you enter a piece of data in your SUBFORM it will inherit the parent item's primary key (or the fields that you've defined) If you are requiring one record on each of the first three tables (they have one to one relationships) there is no reason to separate the items into different tables. You can have them all in one table. Adding a blank record to a normalized database sort of violates the rules of normalizing. Especially if there is the possibility that the child records will never have any data. You do *not* want to add a record to your database if it's going to remain blank, and in your case if they aren't going to remain blank you don't need to separate them.

[sidewayzalex]

Ok then. I may not be understanding you correctly, and I apologize if that is the case. Let me know if I am still misunderstanding. If I should not have any tables blank, which I know I didnt want in the first place, I should remove the Customer Orders table, seeing as I can simply get the same result (for reports) with a query.

Or should I remove the relationship between the contacts and customers table and the BOM1 table, and leave the junction table? If I do that, will the Junction table automatically fill up with information as I enter it into BOM1?

I should (or could) remove RO# and JCS# from 'components replaced' and replace them with a primary key, being 'Shaft#' so that I can have a one-to-one relationship there.

The employee table will be fine as is, because I will be adding new employee information there first, before they even work on a shaft.

Lastly, I need to make the relationship from Parts per order and Labor per order be a one-to-one relationship. Would this be acceptable?

Thanks for the insight!

[sidewayzalex]

Correction: I should simply connect Labor per order to BOM1 with a one-to-one relationship through JCS#, and delete the relationship it has with parts per order?

[sidewayzalex]

And as you said, if I dont need to separate them, I shouldnt. Seeing as the 3 tables have almost no reason to be separated, I will try making a form with just one table that has all of that information in it. one. BIG. table.

[rpeare]

Ok then. I may not be understanding you correctly, and I apologize if that is the case. Let me know if I am still misunderstanding. If I should not have any tables blank, which I know I didnt want in the first place, I should remove the Customer Orders table, seeing as I can simply get the same result (for reports) with a query.

Exactly right, your customer orders table is carrying information you can rebuild from a query, so there's no need to have it.

I should (or could) remove RO# and JCS# from 'components replaced' and replace them with a primary key, being 'Shaft#' so that I can have a one-to-one relationship there.

Is there only going to be one component replaced for any given shaft? Or is it going to be possible to have multiple replaced components on any given shaft. If it's the former then yes, you want a one to one relationship. If it's the latter then no, you want a one to many relationship where the components replaced would be on the many side of the relationship. In either case you want to retain the primary key from the shaft information so you can identify the parts that were applied to a specific shaft.

Lastly, I need to make the relationship from Parts per order and Labor per order be a one-to-one relationship. Would this be acceptable?

This really depends on how your process works. Right now it looks like you are trying to say all labor costs are associated with a part. So if a part is being replaced there are x many hours of work associated with it. But that's not what your relationships are saying. Your JCS# field is the primary key of BOM and you are attempting to use the same JCS# for both your labor and parts table. So your LABOR table may as well be connected directly to your BOM table.

If you are trying to say 'I have part x, I want to enter all the labor related to installing part x in my labor table' then you have got to change your setup. You'd have to put a primary key in your PARTS table and use that same PK in your LABOR table.

In terms of FORMS you'd have to have a main form based on the BOM, a subform based on the PARTS table (with a link of the JCS# enforced) then a subfrom on the PARTS form that connects to the LABOR table (with a link of the PARTS PK enforced)

If you are trying to say 'my parts and labor are independent, I charge x amount for the parts, I don't care how the labor is divided I just need to know the total amount of time spent on the repair' then your current setup will work but you have to change your link from PARTS/LABOR on the JCS# to BOM/LABOR on the JCS#

[sidewayzalex]

Ok, cool. Now, I just realized that the components replaced is not based on Shaft#. It IS in fact based on RO#. I say this, because the RO# is basically the order#. Basically, I need to leave this relationship as is. Woudl it still work with a one-to-many relationship?

I am currently working on combining the rest of the tables from that form.

If you are trying to say 'my parts and labor are independent, I charge x amount for the parts, I don't care how the labor is divided I just need to know the total amount of time spent on the repair' then your current setup will work but you have to change your link from PARTS/LABOR on the JCS# to BOM/LABOR on the JCS#

The Labor is independent. We dont really care how long it takes to weld on a single yoke, just how long it takes to put the whole shaft together. so I have already changed that relationship to BOM/LABOR

[rpeare]

if RO# is your purchase order number why are you not using that as the primary key in your relationships instead of the JCS#

It will still work as you have it I'm just curious why you seem to maintain two different ID's.