Calculation in a query?

[Dave14867]

Hello all,

I have a query built that is giving me exactly what I want. Now I want to take that query and expand on it, I want to be able to put in a PN and have the results output as they currently are but then add another field for "[Qty to Build]" and use the "Qty" and multiply that by the "[Qty to Build] which would be entered when the query is ran. I also want the query to create a new table. Is this something that can be done or is there another, better way to do it?

The existing query consists of the following fields

MfrPartNumber - this is the parent Assembly PN


PartDescription - Description of the parent Assembly
Child_Part_Number - PN of the child parts in the Assembly
Qty - Qty of each child part
Child_Part_Description - Description of the child Part

I want the new table to have all of those fields plus another for Order Qty, so the new table would have the following fields.

MfrPartNumber - this is the parent Assembly PN
PartDescription - Description of the parent Assembly
Child_Part_Number - PN of the child parts in the Assembly
Qty - Qty of each child part
Child_Part_Description - Description of the child Part
Order_Qty - Calculated qty for each child part based on input qty for Order Qty when the query runs.

Would it be better just to have the table predefined so the Order Qty field is already present and just recreate the table when the query runs?

I was planning on have 2 input fields when the query runs, 1 for "Assembly Part Number" which I already have and is working great, and another for "Order Qty".

Thanks

Dave

[June7]

Why have code that creates table? A procedure that routinely creates tables indicates a non-normalized data structure. Why do you need data saved to a table as opposed to just using the query? If you need records saved, why not data entry through a form? Shouldn't [Qty to Build] be a field in the Orders table? Is [Qty to Build] synonymous with Order_Qty?

[Dave14867]

June7,

I thought it would be good to have the query create a table, then use the table to append another table for "Transactions" that will keep track of all transactions by part number. If I can do it with a form that would be great but I would have to use the query to pull all of the child part numbers together correct? Can I call a query from a form? I would also need to give the "Build" a lot number so I could capture the lot number to save that in the 'Transactions" too, I forgot about that in the initial post.

Yes [Qty to Build] is synonymous with Order_Qty.

Thanks
Dave

[June7]

I have processes where I use a 'temp' table to hold data then purge after. But the table is permanent, records are temporary. And database is split which means the 'temp' table is in the frontend.

Code that routinely modifies db structure causes db bloat and increases chance of db corruption.

Yes, query can be called by code behind form.

I don't really understand what you are trying to accomplish.

[Dave14867]

June7,

I will try to put something together today at work and zip the file so you can actually see the data etc, and try to explain better what I am trying to accomplish.

Thanks

Dave

[Dave14867]

June7,
What I want to do is take what ssanfu helped me with and expand on that. I want to create a work order based on that “frmPartMain” after the user has selected the Part Number they want, then I need a field to put in a “Lot Number” and a “Qty to Build” of that “Part Number” and have the info get written to the “Work Orders” table. I also have an extra Column in the “SubAssemblyInfo” subform for the “Build Qty” which will be the result of the “Qty” (in the subform)*”Qty to Build” (in the “frmPartMain”). I just can’t get this to work properly. I believe I have taken out my attempts that haven’t worked.

Any Help is greatly appreciated.

3-7-18 -In Process.zip

Thanks
Dave

[Dave14867]

I got this issue resolved, Thank You to those who replied and assisted.

Dave