Help with Getting A Range of Cost

[Guest]

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.

 

[MGFoster]

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?

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

 

[Guest]

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

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?

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

 

[Jamie Collins]

You can't have the CustCost and MXCost w/o including them in aggregate
functions (Min, Max, etc.) or GROUPing them.

Something more like this:

SELECT DT1.[PART#], DT1.LowestQTPRICE,
DT1.CostRange, DT1.QTPriceRange,
T1.MXCOST FROM (
SELECT T2.[Part#],
MIN(T2.QTPRICE) AS LowestQTPRICE, (
SELECT MIN(T3.COST)
FROM query_name AS T3
WHERE T3.[Part#] = T2.[Part#]
) & ' - ' & (
SELECT MAX(T3.COST)
FROM query_name AS T3
WHERE T3.[Part#] = T2.[Part#]
) AS CostRange, (
SELECT MIN(T3.QTPRICE)
FROM query_name AS T3
WHERE T3.[Part#] = T2.[Part#]
) & ' - ' & (
SELECT MAX(T3.QTPRICE)
FROM query_name AS T3
WHERE T3.[Part#] = T2.[Part#]
) AS QTPriceRange
FROM query_name AS T2
GROUP BY T2.[Part#]
) AS DT1, query_name AS T1
WHERE DT1.[PART#] = T1.[PART#]
AND DT1.LowestQTPRICE = T1.QTPRICE;

Jamie.

--