I have a query built and the original calculation field for a net sale was based on [weight]*[bid] - and it worked (in bold below), but now I need to be able to have an if statement so that my net sale column is calculated based on either weight or quantity, depended on wether or not the line item being sold is based on "lbs" or "ea"
Here is my original sql:
SELECT [Inventory Transactions].*, Inventory.*, [Container Types].*, Suppliers.*, ([Inventory Transactions].[Weight (lbs)]*[Inventory Transactions].[Bid]) AS [Net Sale], IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]![Quantity],-([Inventory Transactions]![Quantity])) AS [Actual Quantity]
FROM [Container Types] RIGHT JOIN (([Transaction Types] INNER JOIN (Inventory INNER JOIN [Inventory Transactions] ON Inventory.ID = [Inventory Transactions].[Transaction Item]) ON [Transaction Types].ID = [Inventory Transactions].[Transaction Type]) LEFT JOIN Suppliers ON [Inventory Transactions].Recycler = Suppliers.ID) ON [Container Types].ID = [Inventory Transactions].[Container Type];
I have added a [Measure] field to my Inventory table to assist in sorting out the fields of interests. I am wanting though to be able to create a billing invoice based on the conditions mentioned above.
Here is my original sql:
SELECT [Inventory Transactions].*, Inventory.*, [Container Types].*, Suppliers.*, ([Inventory Transactions].[Weight (lbs)]*[Inventory Transactions].[Bid]) AS [Net Sale], IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]![Quantity],-([Inventory Transactions]![Quantity])) AS [Actual Quantity]
FROM [Container Types] RIGHT JOIN (([Transaction Types] INNER JOIN (Inventory INNER JOIN [Inventory Transactions] ON Inventory.ID = [Inventory Transactions].[Transaction Item]) ON [Transaction Types].ID = [Inventory Transactions].[Transaction Type]) LEFT JOIN Suppliers ON [Inventory Transactions].Recycler = Suppliers.ID) ON [Container Types].ID = [Inventory Transactions].[Container Type];
I have added a [Measure] field to my Inventory table to assist in sorting out the fields of interests. I am wanting though to be able to create a billing invoice based on the conditions mentioned above.
