Need help with a query

[Scoop]

I have a query the displays the following information on cars:

Year
Make
Model
BodyStyle
MSRP
CurrentRetailValue
ValAsPercentOfMSRP

The last field is calculated as the current value as a percentage of
the orignal MSRP.

I now want to query the above query to obtain data for just "base
models" of each vehicle. Basically, this entails leaving out the
BodyStyle field (e.g. I want Audi - A3 included but I don't care if
it's the Premium or Sport edition), taking the Min of the MSRP and
leaving out the last two fields...This gives me the MSRP of all the
"Base" models.

My problem is that I want to include the "ValAsPercentOfMSRP" field in
the results of the second query. Since all the values in the original
query are different (i.e. based on the various BodyStyles and orignal
MSRP values), this field can't be grouped thus it returns more records
than I need. I can't figure out how to get the "ValAsPercentOfMSRP"
that corresponds with a particular record in the original query. Any
ideas?

 

[Smartin]

I have a query the displays the following information on cars:

Year
Make
Model
BodyStyle
MSRP
CurrentRetailValue
ValAsPercentOfMSRP

The last field is calculated as the current value as a percentage of
the orignal MSRP.

I now want to query the above query to obtain data for just "base
models" of each vehicle. Basically, this entails leaving out the
BodyStyle field (e.g. I want Audi - A3 included but I don't care if
it's the Premium or Sport edition), taking the Min of the MSRP and
leaving out the last two fields...This gives me the MSRP of all the
"Base" models.

My problem is that I want to include the "ValAsPercentOfMSRP" field in
the results of the second query. Since all the values in the original
query are different (i.e. based on the various BodyStyles and orignal
MSRP values), this field can't be grouped thus it returns more records
than I need. I can't figure out how to get the "ValAsPercentOfMSRP"
that corresponds with a particular record in the original query. Any
ideas?

Hi Scoop

It will help tremendously if the base query includes a unique identifier
from the table. If you don't have one, I don't know how to proceed. If
you do, check out the message below for a possible solution. The problem
you are trying to solve is very similar:

http://groups.google.com/group/microsoft.public.access/msg/c07b90e77d6ac249

Post back if you need more assistance.

HTH

 

[Scoop]

Thank you!

That definately put me on track.

Hi Scoop

It will help tremendously if the base query includes a unique identifier
from the table. If you don't have one, I don't know how to proceed. If
you do, check out the message below for a possible solution. The problem
you are trying to solve is very similar:

http://groups.google.com/group/microsoft.public.access/msg/c07b90e77d6ac249

Post back if you need more assistance.

HTH