looking for IFF expression in query to calculate total cost (1 Viewer)

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
Hi everyone,

I need to have write a formula that will calculate rate based on quarter.

Here is the snapshot of 3 tables I have. I have tried to left Join VolumeTble with MaterialTble based on source and material. meaning if the material 123 is coming from Toronto take the rate from MaterialTbl for the product 123 that is coming from Toronto and

if the in volume table we are going to sell the product in Jan, take the rate from Q1 cost from MaterialTbl and multiply it by volume from VolumeTbl.

if the in volume table we are going to sell the product in Apr, take the rate from Q2 cost from Table and Multiply it by volume from Tbl.
If in the volume table for the months of July-Aug there is not any volume, it shouldn't calculate Q3 cost
 

Attachments

  • Model2.PNG
    Model2.PNG
    38 KB · Views: 104

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
Hi,

Here is code:

SELECT VolumeTblwithQuarter.Destination, VolumeTblwithQuarter.Material, VolumeTblwithQuarter.Field1, VolumeTblwithQuarter.Source, VolumeTblwithQuarter.Month, VolumeTblwithQuarter.Quarter, VolumeTblwithQuarter.Volume, [VolumeTblwithQuarter].[Volume]*[MaterialTbl].[2019 Component cost] AS [2019 Cost],

IIf([VolumeTblwithQuarter].[Quarter]="Q1",[MaterialTbl].[Q1 2020]*[VolumeTblwithQuarter]*[Volume],
IIf([VolumeTblwithQuarter].[Quarter]="Q2",[VolumeTblwithQuarter].[Volume]*[MaterialTbl].[Q2 2020],
IIf([VolumeTblwithQuarter].[Quarter]="Q3",[VolumeTblwithQuarter].[Volume]*[MaterialTbl].[Q2 2020],
IIf([VolumeTblwithQuarter].[Quarter]="Q4",[VolumeTblwithQuarter].[Volume]*[MaterialTbl].[Q4 2020]))))
 

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
Sorry, I didn't know the policy about crossposting. I posted on two forums as I have a deadline to make for my work which is very important. that's why I couldn't wait days to post on another forum.
Moving forward, I will practice crossposting.

I hope someone will give me an answer as I need to make this deadline
 

isladogs

MVP / VIP
Local time
Today, 14:03
Joined
Jan 14, 2017
Messages
18,186
Nothing to practice...
If you feel the need to cross post, make sure you provide a link at each site and tell each site when you have a solution.

That sql isn't complete. There is no FROM clause.
What happens when you tried your query?

If you're in a hurry, suggest you post a cut down version of your dB with those 2 tables and your query.
You received several answers at the other site. All, like me, feel your table structure is wrong
 

GinaWhipp

AWF VIP
Local time
Today, 10:03
Joined
Jun 21, 2011
Messages
5,901
Looks like Duane has posted a solution in the other Forum. That said, you may want to take to take a step back and learn about normalization as if this is the beginning of your database you are going to have to implement *hacks* to get your calendar to work properly going forward.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:03
Joined
May 7, 2009
Messages
19,169
it would be easy if you make VolumeTblwithQuarter].[Quarter] field numeric (1,2,3,4), then the expression can be simplified:

[Volume]*(Choose([VolumeTblwithQuarter].[Quarter],[MaterialTbl].[Q1 2020],[MaterialTbl].[Q2 2020],[MaterialTbl].[Q3 2020],[MaterialTbl].[Q4 2020])
 

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
it would be easy if you make VolumeTblwithQuarter].[Quarter] field numeric (1,2,3,4), then the expression can be simplified:

[Volume]*(Choose([VolumeTblwithQuarter].[Quarter],[MaterialTbl].[Q1 2020],[MaterialTbl].[Q2 2020],[MaterialTbl].[Q3 2020],[MaterialTbl].[Q4 2020])


Thanks for your answer. but I need to show my data on a monthly basis but the cost will be a quarterly basis as It shows in the attached file.
 

Attachments

  • Model 2-Quarterly cost calculation example.xlsx
    9.7 KB · Views: 108

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
Looks like Duane has posted a solution in the other Forum. That said, you may want to take to take a step back and learn about normalization as if this is the beginning of your database you are going to have to implement *hacks* to get your calendar to work properly going forward.

Where is the Forum, I tried to open the link you have copied,but can't find the post with solution related to my question.

In regards to normalization, I have read about it, but I still don't understand how I have not practiced proper normalization?
 

GinaWhipp

AWF VIP
Local time
Today, 10:03
Joined
Jun 21, 2011
Messages
5,901
The suggested solution it in Duane's response which is the same as the one posted by arnelop above.

Tables should not have field names like Q1..., Q2... They should be long not wide. If you post an image of your Relationship window which will show you table layout we could help with normalization.
 

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
The suggested solution it in Duane's response which is the same as the one posted by arnelop above.

Tables should not have field names like Q1..., Q2... They should be long not wide. If you post an image of your Relationship window which will show you table layout we could help with normalization.


Ok, thanks! but it didnt workout though! I ended up using IIf function to work it out. now the first table is what I have got. I want my table to look like the second table. I would like to have quarter field in first table broken down into four different fields.
here is the table I got
 

Attachments

  • Model2-Lasttbl.PNG
    Model2-Lasttbl.PNG
    66.6 KB · Views: 105

GinaWhipp

AWF VIP
Local time
Today, 10:03
Joined
Jun 21, 2011
Messages
5,901
Hmm, I believe you are trying to use Access tables like Excel spreadsheets which they are not. To get to the below you create a Table for input, i.e.

tblInput (using name for example only)
iInputID (Primary Key)
iDate (Date/Time)
iDeliveryOfficeID (Maybe tied to another table)
iMaterialID (Maybe tied to a table with Material details)
iVolume (Volume)
iValue (Cost [Amount})

Then all you need is a Crosstab query.
 

elly.khanlar

Registered User.
Local time
Today, 10:03
Joined
Oct 3, 2019
Messages
23
Thanks Gina for your response. I will try to use this format. In regards to my question for my query I ran into different problem.
in my table, the volume for each material get repeated as the category lines is added for each product.should i use distinct criteria to only have one volume for combination of material and location?





Hmm, I believe you are trying to use Access tables like Excel spreadsheets which they are not. To get to the below you create a Table for input, i.e.

tblInput (using name for example only)
iInputID (Primary Key)
iDate (Date/Time)
iDeliveryOfficeID (Maybe tied to another table)
iMaterialID (Maybe tied to a table with Material details)
iVolume (Volume)
iValue (Cost [Amount})

Then all you need is a Crosstab query.
 

Attachments

  • Model2-Lasttbl.PNG
    Model2-Lasttbl.PNG
    66.6 KB · Views: 97

GinaWhipp

AWF VIP
Local time
Today, 10:03
Joined
Jun 21, 2011
Messages
5,901
I'm not able to match up what you are asking with the image you provided. Please clarify.
 

Users who are viewing this thread

Top Bottom