Max Subquery with Alias

[PSSMargaret]

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)

 

[theDBguy]

Hi. If I understand what you said correctly, I'm not sure it's a good idea to use the current query as a subquery as well.

Most examples you will see uses the same table source in the query and subquery. Is that not possible in your case?

 

[plog]

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.

 

[PSSMargaret]

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.

ProjectID	VCRatio	MaxVCRatio
1	.58	.83
2	0	0
3	.72	.83
4	.83	.83

 

[theDBguy]

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.

ProjectID	VCRatio	MaxVCRatio
1	.58	.83
2	0	0
3	.72	.83
4	.83	.83

Hi. Why does ProjectID 2 in your sample data have a MaxVCRatio of 0 instead of .83?

 

[PSSMargaret]

Hi. Why does ProjectID 2 in your sample data have a MaxVCRatio of 0 instead of .83?

Because not every project will have a VCRatio which is why my formula states if VCRatio is greater than 0, populate the MaxVCRatio, otherwise enter 0.

MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([VCRatio]) FROM qryRank1),0)

 

[plog]

D+

That's one set of data with no table name. Did you post starting data or expected data? Please see my first post and respond appropriately.

 

[PSSMargaret]

D+

That's one set of data with no table name. Did you post starting data or expected data? Please see my first post and respond appropriately.

What's D+?

 

[plog]

Your grade thus far. It's only a midterm so its subject to change. See my first post to raise it.

 

[PSSMargaret]

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)

 

[theDBguy]

Because not every project will have a VCRatio which is why my formula states if VCRatio is greater than 0, populate the MaxVCRatio, otherwise enter 0.

MaxVCRatio: IIf([VCRatio]>0,(SELECT Max([VCRatio]) FROM qryRank1),0)

Okay. Thanks for explaining that. So, to alias a table, you could try something like.

 MaxVCRatio: IIf([VCRatio]>0,(SELECT Max(Q1.[VCRatio]) FROM qryRank1 AS Q1),0)

Hope that helps...

 

[PSSMargaret]

Okay. Thanks for explaining that. So, to alias a table, you could try something like.

 MaxVCRatio: IIf([VCRatio]>0,(SELECT Max(Q1.[VCRatio]) FROM qryRank1 AS Q1),0)

Hope that helps...

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.

 

[theDBguy]

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.

 

[plog]

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.

 

[PSSMargaret]

theDBGuy,

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.

Any suggestions or ideas are greatly appreciated.

 

[theDBguy]

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.

Any suggestions or ideas are greatly appreciated.

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:

NetPrice: [GrossPrice]-[TotalCost]

You might have to do something like:

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:

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).

Hope that makes sense...