Complex query that is getting inofrmation from a form

[psmaglik]

I have been trying to design a query that requires input from a form that I have create:



Here is the form see attachment form01.jpg

Here is the query that I am building see attachment query01.jpg


Here is the parameter that I am using for Product Name: This works this looking for the product that has a check mark to display that product in the query.
IIf([Forms]![FRMProductName]![Check8],"CHL1- Hamilton Adirondack Chair") Or IIf([Forms]![FRMProductName]![Check11],"CHL1- Hamilton Adirondack Chair- Two Tone")


Here is the parameter that I am using for the QTY01 - Expression Field:
QTY01: Switch([Forms]![FRMProductName]![QTY01]>=1,[Forms]![FRMProductName]![QTY01]*[TBL_Product]![BP01],[Forms]![FRMProductName]![QTY02]>=1,[Forms]![FRMProductName]![QTY02]*[TBL_Product]![BP01])

The problem that I am having is when I type in the QTY field the second box on the form and the first one has a value it is taking the first result and multiplying against the QTY01:Expression. I am hoping I am explaining it right.

I need some to point me in the right direction on how should I write the query I have banged my head at my desk for days on this issue.

Paul

[Minty]

I think you have an inherent design problem, you shouldn't have qty1 and qty2 as fields. If you suddenly need qty3 you are into a re-write of you system.

You should have a table containing Order or BOM lines with a ProductD field and a qty field, this should be linked back to an d order header storing the order date and who the order is for.
This way you have as many or as few related qty lines as you need and your querying becomes simple.

[psmaglik]

Here are my tables and relationships.


What I am trying to achieve is to create a query that will require the user to select a product and type in the QTY on the form and then I want the query to total all the pieces of lumber that is needed. A lot of our products use the same type of lumber so what I am trying to achieve is a lumber calculator.

[Minty]

You have what somebody on here describes unfortunately accurately as "Committed Spreadsheet".

You have taken a spreadsheet design and pretty much dumped it into a table. It won't work.
You need to read up about normalisation.

Start here https://www.fmsinc.com/microsoftacce...transpose.html
and google lots !