Making certain values equal to calculated value in another field

[element32d]

I think I'm searching for the wrong issue in google but it's hard to word in one sentence. I have these calculated average volumes for a certain part number and they populate the average volume field. There are only certain part numbers that NEED to be equal to the calculated volume for another part number. But it's only a unique few that are like this within the same field as the other normal part numbers, not a whole field of them.

How do I make the calculated volume of one part number = to the the calculated volume of another. I was hoping its as easy as in excel where cell B2 = C2 but it doesn't seem that way.



Thanks!

EDIT: A more straight-forward explanation may be that when a user enters in this specific part number, the database does a bunch of calculations. I want those calculations to occur using the same numbers with certain other part numbers. Basically those part numbers follow the entered part number throughout the whole process, but each part number has its own details still. I dont know if this helps.

[June7]

You will need to establish some sort of relationship between these dependent part numbers. Either a table or hard-coded in VBA procedure. Then with query and/or code, determine if a part number has partner and if found use the appropriate values in calcs.

[element32d]

You will need to establish some sort of relationship between these dependent part numbers. Either a table or hard-coded in VBA procedure. Then with query and/or code, determine if a part number has partner and if found use the appropriate values in calcs.

Hey, sorry for the late reply but I'm coming back to this project. I get what your saying but I'm not sure I'm good enough at access to implement it currently. I have made a small version database of what I'm working with here.

As you can see, 1 5001 and 1 5002 make 1 5003. But only 5003 is able to be put into the orders table. But all 3 parts are made here. So i want 5001 and 5002 so show up in orders in the back end, it doesn't have to show up in the front end of the database. That's why I want 5001 and 5002 to basically "attach" to 5003 when it's put in the system and follow it through from the order entry until the order is fulfilled. But 5001, 5002, and 5003 have different properties from each other. For example, 5001 runs twice as fast as 5002.

[June7]

The only way to 'attach' 5001 and 5002 to 5003 is to establish a relationship. This is what junction tables are for. Think something like:

tblConstructs
ID
PartPrimary
PartDependent

Type of db you appear to need is manufacturing. I never built one but seems like one of the most difficult to build. Review some other threads on the topic: https://www.accessforums.net/databas...oin-28334.html

[element32d]

The type of database I'm dealing with is in fact manufacturing, but the database is completely built. This issue just cropped up, but it doesn't make or break the integrity of the database, It would just make things easier if I could get my idea working.

I created a junction table, but when I enter an order in for 5003, the 5001 and 5002 components won't enter in the orders table, just the 5003.

I have a revised example database attached with the order entry screen.

[rpeare]

ExampleDatabaseRevised.zip

This is a very simple example of what you're trying to accomplish. You would ideally check what you have in inventory (a sum of all receipts vs a sum of all disbursement of parts) to see if you have enough on hand to fill the order.

[element32d]

I looked at your example, and it's kinda over my head haha. If I put in an order for, say, 400 5003 parts, it should automatically update 5001 and 5002 to a quantity of 400 each in the orders table. I don't see that happening here. Am I missing something?

[rpeare]

I was assuming you just cared about the total number of parts that are involved in the sale to check against inventory. Not that you would want to see an itemized list as you're doing your order data entry.

You would likely need a much more complex (unbound) form to do what you're doing which is going to be a lot more programming if you think the queries are over your head. If you just care about the itemization for the purposes of producing an invoice that's a completely different matter.

For instance let's say your sale price for item 5003 is basically just labor with a markup then I would understand adding all the component parts to the invoice, then you're getting the full charge of parts plus labor.

If, however, the sale price of your item 5003 already has the cost of parts included and you just want to show the component parts that go into making that item WITHOUT charging for the component items as well (just a visual reference) that's a different question.

So the question is, why do you want them added at the time you create the invoice is it because:
A. Your manufactured item sale cost is just a markup for labor and you have to charge for all the component parts as well
OR
B. Your manufactured item sale cost INCLUDES part costs and you just want a visual reference at the time of data entry and/or producing an invoice

[element32d]

It's actually neither. It is because I have a form that rips information from the inventory table and order table, and creates an inventory on hand/scheduling report. Only orders from assembled parts are put into the system, and we just know which components need to be made (5001 & 5002) to produce that assembled part (5003). I want 5001 and 5002 to show up in inventory on hand/scheduling because they are made in different machines and have different properties.

In short, only 5003 shows up in inventory on hand/scheduling report because 5001 and 5002 do not get put into orders form, only 5003 does. This is why I just want 5001 and 5002 to attach to 5003 until 5003's order is met, because they all have the same quantity needed, just different properties such as 5001 runs in machine 1 at 40 parts an hour and 5002 runs in machine 2 at 30 parts an hour.

[rpeare]

I'm not sure why what I gave you won't work then, it still seems like you want to know what your total commitment for the job is. That's what the little box on the right shows, you can use the same method and apply it to anything else.

Or do you specifically need to know that a component part is for a specific finished product.

say on an order if you receive an order for 500 of component part 5001 and 500 of finished product 5003 that your total job commitment is 1000 of part 5001.

Is it important in your system to know that for this order 500 of component 5001 are being ordered as individual pieces and 500 of component 5001 are related directly to the construction of item 5003?

[theperson]

That's the whole point of what I'm trying to do. The database needs to recognize that 500 pieces of 5003 have been ordered, so that means we need 500 pieces of 5001 and 500 pieces of 5002. Right now, it only knows that 500 pieces of 5003 were ordered. 5001 and 5002 are just in the system for information and inventory purposes - not for orders, which is where my scheduling report takes information from. That is why I want the orders part of the database to recognize that 5003 has an order of 500 parts, that means we need 500 5001's and 500 5002's so all 3 part numbers can show up in this guy's scheduling form!

[rpeare]

Ok one problem is I had my junction table messed up.

Here's the database back again. The box on the right should now correctly show the required parts for the order. I think my point is that you do not need to have separate records on your data entry to do what you want (which I think is what you're asking for and probably the source of most of our miscommunication). The method I'm using to show the total parts needed for the job based on the smallest data entry footprint I can think of. You want a detail line for every subsequent component part which I think is not necessary for what you're currently asking.

So on the data entry side you put in 500 units of item 5003. That's all that shows in your order detail. However the summary to the right will show that not only do you need 500 of item 5003, but 500 of 5001 and 5002 as well.

This summary on the right can also be produced on any scheduling form without the scheduling form user having to know how you did your data entry.

ExampleDatabaseRevised.zip