Help with Getting A Range of Cost

J

Juan

Hi,
not sure if this can be done or maybe something close to it. I have a group
total query.
Query. Here's sample:
PART# COST QTPRICE Cust Cost MXCOST
XYZ 1.72 1.9 2.96 2.6
XYZ 1.8 1.8 2.96 2.6
XYZ 2.3 2.3 2.96 2.6
XYZ 2.83 2.83 3.019 2.6

I want to do something like this:

PART# Range COST LowestQTPRICE QTPRICE RANGE Cust Cost MXCOST
XYZ 1.73-2.83 1.8 1.8-2.83
2.96 2.6

I'm thinking I will need to do a couple of Queries to maybe come to my
end product. So is there a way to get a field to get the range of the cost?

Any feedback will be very appreciated.
 
M

MGFoster

Juan said:
Hi,
not sure if this can be done or maybe something close to it. I have a group
total query.
Query. Here's sample:
PART# COST QTPRICE Cust Cost MXCOST
XYZ 1.72 1.9 2.96 2.6
XYZ 1.8 1.8 2.96 2.6
XYZ 2.3 2.3 2.96 2.6
XYZ 2.83 2.83 3.019 2.6

I want to do something like this:

PART# Range COST LowestQTPRICE QTPRICE RANGE Cust Cost MXCOST
XYZ 1.73-2.83 1.8 1.8-2.83
2.96 2.6

I'm thinking I will need to do a couple of Queries to maybe come to my
end product. So is there a way to get a field to get the range of the cost?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It looks like all you need is one query that uses the first query.
E.g.:

SELECT [Part#],
Min(COST) & " - " & Max(COST) As CostRange,
Min(QTPRICE) & " - " & Max(QTPRICE) As QTPriceRange,
FROM query_name
GROUP BY [Part#]

You can't have the CustCost and MXCost w/o including them in aggregate
functions (Min, Max, etc.) or GROUPing them.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKBtBYechKqOuFEgEQJJ0wCfXx7eviBsyR1ZCt7+QaVnF1lPoIwAoMJL
KLFhSTN5YMCi88Him1E3Z3qb
=d0uK
-----END PGP SIGNATURE-----
 
J

Juan

Hello Mg,
thanks It seems that works fine. And yeah as you mentioned I can't have the
CustCost and MXCost w/o including them in aggregate or grouping.
But looks good.

Thanks alot for the help, really appreciated it.

Have a good one.

J



MGFoster said:
Juan said:
Hi,
not sure if this can be done or maybe something close to it. I have a group
total query.
Query. Here's sample:
PART# COST QTPRICE Cust Cost MXCOST
XYZ 1.72 1.9 2.96 2.6
XYZ 1.8 1.8 2.96 2.6
XYZ 2.3 2.3 2.96 2.6
XYZ 2.83 2.83 3.019 2.6

I want to do something like this:

PART# Range COST LowestQTPRICE QTPRICE RANGE Cust Cost MXCOST
XYZ 1.73-2.83 1.8 1.8-2.83
2.96 2.6

I'm thinking I will need to do a couple of Queries to maybe come to my
end product. So is there a way to get a field to get the range of the cost?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It looks like all you need is one query that uses the first query.
E.g.:

SELECT [Part#],
Min(COST) & " - " & Max(COST) As CostRange,
Min(QTPRICE) & " - " & Max(QTPRICE) As QTPriceRange,
FROM query_name
GROUP BY [Part#]

You can't have the CustCost and MXCost w/o including them in aggregate
functions (Min, Max, etc.) or GROUPing them.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKBtBYechKqOuFEgEQJJ0wCfXx7eviBsyR1ZCt7+QaVnF1lPoIwAoMJL
KLFhSTN5YMCi88Him1E3Z3qb
=d0uK
-----END PGP SIGNATURE-----
Click to expand...
 
Top