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

[sidewayzalex]

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.

Well... I am still new to this company, seeing as I have been here for almost 2 months. I wont be able to describe it as well as some of the other employees, but I will do my best.

The RO# is basically the request order number. This can only appear once, which makes it a good canditate for a PK.

The JCS# is basically the number for the shaft that is being made. It symbolizes the size, the parts used and everything else. This one should be the PK for the BOM (bill of materials) because it makes referencing it much easier. Each JCS# has a specific set of parts, construction notes, and labor hours that go with it.

The shaft# is the number of the shaft being made (or inspected). Any BOM can have any number of shafts that go with it. as an example, Ford could order 7 JCS# 000.0000.00.0000.000.00's, and each shaft will have a different number based on a few things, which I still dont understand fully.

When we go into the files to find a part, we always look for JCS#, its basically like OUR part numbers for shafts (if that makes sense). We dont look for RO, or shaft numbers.

When we do inspections, such as with the WORKSHEET FOR DYNO SHAFTS, we often inspect several shafts, so we use the RO# as main reference, and then the shaft# for each set of readings of each shaft in the RO.

I hope this helps and its easily understandable. Let me know if I should clarify more.

[sidewayzalex]

Now... If we look at BOM1 right now. I have a junction table connecting it into a many-to-many relationship with parts. (there can be any number of parts per JCS# and visa-versa).
If I try entering part information of a part that doesnt exist into a subtable of BOM1, it tells me I need to enter it into parts first. It says the same thing if I try to make a query to use as a junction, or if I try entering the data into the junction table. Is there a VBA code that I can use that will do something like this...:

Check if this part is in tblParts
if yes, allow entry
if no, enter data into tblParts as well

Also, can I make a code or do a query or something for when I make a form, I would like to simply enter the partID into the form, and make it so that the VenderID and price show up automatically. Is this possible?

[rpeare]

can you provide an updated relationship diagram

[sidewayzalex]

can you provide an updated relationship diagram

Haha, I was just about to before I saw your post

This is the updated version so far, I made a new database just in case the old one was better. I filled up tblShaftInformation with the fields from tblFinalDriveshaftBalance and tblDriveshaftRunouts. I also put the fields from tblLaborPerOrder into tblBOM1

[rpeare]

I would just limit your data entry to items that are in your items table, don't allow any user typed values. If the user can't find the item they want give them an 'edit' button that will take them to a data entry screen for the items then after they add their item refresh your database (me.refresh on closing the item data entry screen) and requery the combo box on the first form a during the closing event of your item data entry screen as well.

[sidewayzalex]

Ok. I thought this might be the case. I wanted to avoid this because it would be incredibly time consuming for the first little while to enter the part information several times at a time.
So lets say I make a frmParts form, which can be opened up from the frmBOM form. How and where would I code the link/button/frmParts form, so that it automatically refreshes the database and requeries the combo box on the first form when closing the frmParts form?

[sidewayzalex]

Also, if I did this, I would like to be able to enter in just the part# and VID into the form. Is there any way I can have the part cost automatically pop up?

[rpeare]

Ok. I thought this might be the case. I wanted to avoid this because it would be incredibly time consuming for the first little while to enter the part information several times at a time.
So lets say I make a frmParts form, which can be opened up from the frmBOM form. How and where would I code the link/button/frmParts form, so that it automatically refreshes the database and requeries the combo box on the first form when closing the frmParts form?

When you close your frmParts open the ON CLOSE event and simply have

me.refresh
forms!firstformname!partslistboxname.requery
me.close

[rpeare]

Also, if I did this, I would like to be able to enter in just the part# and VID into the form. Is there any way I can have the part cost automatically pop up?

If you base your data entry form on a query and not a table yes, you'd just have to set up the query to include the parts table (linked appropriately) and include the cost field. When you do your data entry and you enter a part number that field should automatically populate, just make sure you lock it on your data entry field because if someone changes the cost it gets changed on your table which you do not want.

Just an aside, when you are considering a database where you are calculating costs over a long term does it matter to you to be able to reproduce an invoice? For instance let's say a year from now all the parts you use have gone up by 1 dollar per item. If you are not storing any sort of history, or not storing the original cost of the item when you generate the invoice you will not have the ability to reproduce an old invoice. Just something else to think about.

[sidewayzalex]

So lets say that I might need an invoice a year from now. Given, prices dont really change much, and regardless, we usually tend to sell our parts for 40% more than we paid for them. But keeping to the idea of an invoice of the past year's/month's/week's sales...

If I decided not to keep price based on part, and based on JCS#, that would mean the user would have to enter price every time. Could I end up doing a query a month from now (even if prices change) of all the totals of the JCS#'s we sold, and place them in a report to give us totals and subtotals?

Also, I believe we mark up our prices by a certain percent depending on the customer relationship. Would it be difficult to go in later (when I find out that whole system) and change the percent per order?


IF I do end up basing the form on a query instead of a table, and the prices automatically update (based on the table the query is based off of) I would make it possible to change the part cost in the table only by use of the part information form that will be linked to the mainform via button.

Does this seem correct?

[sidewayzalex]

This is what I was looking at using for the frmBOM. Let me know what you think.

mainform-tblBOM1
subform1-qryCustomerOrders
subform2-qryLaborCost
subform3-qryPartsPerOrder

there will be a button that opens up frmParts: for when a part is not already in the system.

frmParts is a split form so it will allow for more simple navigation if one needs to edit the price of a part.

[rpeare]

There's a lot going on in this question if you want to be able to keep an accurate history of what was charged to a specific customer you could a few things two come to mind.

The easiest way I can see to handle this is in your items table keep track of the 'current' cost. Then when you add a new part to a customer's order you could copy the current cost to the 'invoice' record and apply a markup percentage to the part. How you handle the markup percentage sort of depends on how your business operates. For instance if you have a standard mark up percentage at the customer level you can multiple that base cost by the markup percentage. If each part has a different markup percentage for each different customer that's a more extensive problem and you'd likely have to have a table that has the customer number and the markup percentage for each part.

In either case of tracking cost by client and having an accurate history the cost of the part would have to be a calculated field on the invoice itself (i.e. the value would be calculated one time and added to the record automatically based on how your business handles mark up percentages).

This is likely the easiest way. The more robust way would be to keep a base cost for each item through a range of dates, and when you enter a part number for a certain invoice, you could figure the base cost of the item by the invoice date.

[rpeare]

What's the relationship between BOM and customer orders? they both have a JCS# field but are there mutliple records in BOM for each record in Customer orders? and what function is the customer orders table serving?

[sidewayzalex]

As for right now, there is no relationship with BOM and customer orders. I had it there in my second draft of the database as a junction table between customers and BOM. I then realized that it would be easiest to have CustomerID in BOM be a lookup field.

As for the part cost ideas, I think that I will have to talk to my managers and whatnot before everything is set in stone. I also think that before I make the system too complex, I need to finish the final rough draft of the database. I will keep these thoughts in mind though. Thank you.