Referencing Fields in the same Query

[chc5286]

I am an experienced Excel user trying to learn the ins and out of Access. One road block I am hitting is when I am trying to write an expression in a Query (or form) that needs to reference another calculated field in that same query. Thanks in advance for the help.

 

[John Big Booty]

Welcome to the forum.

Perhaps the following image will help.

The field Volume in the above query is a calculated field derived by multiplying the preceding three fields. In fact the following will work just as well;

 

[chc5286]

Thanks for the reply John. My question is can you add an additional field that uses the volume calculation. For example, to get half of the the volume can you create a calculated field: Half volume: [volume]*.5

 

[John Big Booty]

You certainly can;

Given that you are coming at this from an Excel background, one thing you will need to do is get your head around the concept of Data Normalisation, this concept is probably the hardest thing you will have to grasp in your transition from, the flat structure of, Excel to the Relational structure of Access

 

[John Big Booty]

Note in the above image the second calculated field still retains the default label of Expr 1 which can be changed to whatever you wish.

 

[chc5286]

Thanks for the continued help. Now I realize that the reason I am having this problem is because I am trying to do this in a totals query.

I have a field named Invoice set to "group by" to consolidate all my invoices from my invoicedetails table.

I have a field named amount: [quantity]*[salesprice] to get the total of each invoice. It is set to group by.

I want to run an iif statement using the field "amount" to put my invoices into groups. so iif(amount>300,"0-300",iif(amount>500,"300-500"),iif(etc.)))

Is this possible? I guess I should have posed my question this way from the start. Sorry for the wasted time. Thanks again.

 

[vbaInet]

I bet that John Big Booty is busy watching his favourite drama series so in his absence here's what you could use:

Switch()
http://www.techonthenet.com/access/functions/advanced/switch.php

 

[chc5286]

Thanks for your help. The switch function will be a helpful replacement for the IF function. However, I don't have an answer to my original question.

Can I reference a calculated field in a totals query?

I have a field named Invoice set to "group by" to consolidate all my invoices from my invoicedetails table.

I have a field named amount: [quantity]*[salesprice] to get the total of each invoice. It is set to group by.

I want to create another field that references the "amount" field that I created in this query. Is this possible?

 

[John Big Booty]

You may need to do this in two steps. You first query does your calculations and then use those results in a query to collect the sum of your calculated field.

 

[vbaInet]

Can I reference a calculated field in a totals query?

I want to create another field that references the "amount" field that I created in this query. Is this possible?

If it was a simple SELECT query you can (like John Big Booty mentioned in post #4), but because you are grouping it won't work so you will need to do what John Big Booty advised in his last post, i.e. using two queries.

 

[jetberrocal]

I was looking to solve the same question and found the solution for it. At least on Access 365 there is a solution. In the Query design window instead of "Group by" select "Expression" in the dropdown for the new calculated field based on other calculated fields.