I need the Maximum value of the VCRatio column to appear in a column in the same query. I have tried DMAX, but as you know it definitely drains performance so I'd like to try using Max in a subquery and I'm reading that I need to alias the subquery since both values will be in the same query. I have never used an alias. Could someone please show me how to alter the below. Thank you very much.
MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([VCRatio]) FROM qryRank1),0)
I built a table with VCRatio (t), I built a query named qryRank1, and then I built another query based on t and then pasted in the code you posted and it worked. So I don't know what issue you are having. What are you trying to fix?
Perhaps you can demonstrate your issue with data, please provide 2 sets:
A. STarting data from your table(s). Include table and field names and enough data to cover all cases.
B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.
Again, 2 sets of data that tie together; not 1 set and an explanation.
I built a table with VCRatio (t), I built a query named qryRank1, and then I built another query based on t and then pasted in the code you posted and it worked. So I don't know what issue you are having. What are you trying to fix?
Perhaps you can demonstrate your issue with data, please provide 2 sets:
A. STarting data from your table(s). Include table and field names and enough data to cover all cases.
B. Expected results of A. Show me what data you expect to end up with when you feed in the data from A.
Again, 2 sets of data that tie together; not 1 set and an explanation.
I receive a "Circular reference caused by qryRank1" error message. I believe it's because the subquery is based on the query that it reside in, yes? The goal is to have the MaxVCRatio in qryRank1 so I can perform a calculation using both VCRatio and MaxVCRatio.
I receive a "Circular reference caused by qryRank1" error message. I believe it's because the subquery is based on the query that it reside in, yes? The goal is to have the MaxVCRatio in qryRank1 so I can perform a calculation using both VCRatio and MaxVCRatio.
You're obnoxious and I just reported you. If you're not going to help me stop posting. I came to this forum as a last result and need help, not to be ridiculed. It's completely obvious what I need with one table. I need to get MacVCRatio in my query results. How do I alias?
MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([VCRatio]) FROM qryRank1),0)
Thank you very much for your assistance. I truly appreciate it and have been struggling with this issue all day. Unfortunately when I use your suggestion, I still get the Circular Reference error. In case it makes a difference, VCRatio is a calculation of two raw data fields.
Thank you very much for your assistance. I truly appreciate it and have been struggling with this issue all day. Unfortunately when I use your suggestion, I still get the Circular Reference error. In case it makes a difference, VCRatio is a calculation of two raw data fields.
Okay, I will go back to my original response. I really think it would be better to try and get the subquery to use the original source data rather than use the same query, which could result in circular reference.
I'm sure your current issue can be fixed, but I can't say how since I can't see your db.
One other approach you could try is leave your current query alone and create a new one based on your query but add the DMax() in the new query.
I was and still will help you. To do so, though, I will need what I requested in my first post. With no ridicule intended, it is difficult to discuss data issues with just words--a good data example cuts through all that.
Thank you. With a clear head this morning I read your post above. Your suggestion to utilize data in the table helped with two of my issues (one Max and one Min).
I changed the below formula
MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([VCRatio]) FROM qryRank1),0)
to the formula below and instead of using the calculation of VCRatio, I used the raw data that makes up VCRatio.
MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([Volume]/[Capacity) FROM tblProjectDetails),0)
I changed both the Max and Min calculations which made a huge difference in speed right away. So simple. I still have one calculation that I could leave as is since the performance improved but I would like to know if it's possible and to understand how Aliasing works?
I need to get the minimum values of the CostEffect column. Formula below:
CostEffect: [RequestedAmt]/(LocalMatchPoints]+[CriticalOppPoints]+[ProjectReadinessPoints]+[SafetyPoints] + [CongestionPoints]).
RequestedAmt is the only raw data in the table. All of the values in the formula for CostEffect are calculations themselves in the query where I need the value for minimum CostEffect. Just like the other two, the minimum CostEffect is then used in another calculation.
I need to get the minimum values of the CostEffect column. Formula below:
CostEffect: [RequestedAmt]/(LocalMatchPoints]+[CriticalOppPoints]+[ProjectReadinessPoints]+[SafetyPoints] + [CongestionPoints]).
RequestedAmt is the only raw data in the table. All of the values in the formula for CostEffect are calculations themselves in the query where I need the value for minimum CostEffect. Just like the other two, the minimum CostEffect is then used in another calculation.
You should be able to apply the same method you used to solve the first problem. If the table contains all the necessary information to derive the calculated values, you could use them to get the missing values. In other words, you may have to expand your expression to use the data available for the calculation. For example, rather than:
Code:
NetPrice: [GrossPrice]-[TotalCost]
You might have to do something like:
Code:
NetPrice: [Price]*[Qty]-[Cost]*[Qty]
If the needed information is not available in the table, then I suppose you will have no choice but to use the query. If that's the case, you could also try what I suggested earlier: Create another query based on your original query, so you don't run into circular references and don't have to worry about aliasing the subquery.
As for "aliasing," it's simply a way to give a different name to a table or field. Here's an example:
SQL:
SELECT T1.FirstName, T1.LastName, T1.FirstName & " " & T1.LastName AS FullName
FROM TableName AS T1
In the above example, both T1 and FullName are aliases. Once you assign an alias to a table or field in a query, you'll have to use it to refer to that table or field (you can't use the original name anymore, or you'll get an error or an unexpected result).
