Dmax Function (I think!)

[Jennifer]

First hand- I'm working on a pricing database- and if you know anything about
prices, they are Always changing...
My current table is set up as follows:
Fields:
Vendor, PO #, Item #, Price, PO Create Date, Price Effect Date, Determiner.

Field: Determiner is calculated; "Price Effect Date - PO Create Date"
I have a qry that deletes all positive numbers. I know want to qry the "MAX"
of the determiner to find the lowest negative # per each po# & item #.

For Example:
PO # Item# Price PO Create Date Price Effect Date
Determiner
4414541 0464164 $10.00 2/9/09 1/28/08
-378
4414541 0464164 $11.08 2/9/09 5/6/08
-279
4464641 8090797 $12.03 2/9/09 3/27/08
-319
4464641 8090797 $12.97 2/9/09 8/28/08
-165

So, I want to query off this table to show the MAX of the determiner at each
PO & Item #. Meaning I would want to query to show only, the second & fourth
lines, determiner -279 & -165.

I've tried using the Dmax function, but I don't think its helping me or it's
not what I need. Any help???

Thanks, Jennifer

 

[MGFoster]

First hand- I'm working on a pricing database- and if you know anything about
prices, they are Always changing...
My current table is set up as follows:
Fields:
Vendor, PO #, Item #, Price, PO Create Date, Price Effect Date, Determiner.

Field: Determiner is calculated; "Price Effect Date - PO Create Date"
I have a qry that deletes all positive numbers. I know want to qry the "MAX"
of the determiner to find the lowest negative # per each po# & item #.

For Example:
PO # Item# Price PO Create Date Price Effect Date
Determiner
4414541 0464164 $10.00 2/9/09 1/28/08
-378
4414541 0464164 $11.08 2/9/09 5/6/08
-279
4464641 8090797 $12.03 2/9/09 3/27/08
-319
4464641 8090797 $12.97 2/9/09 8/28/08
-165

So, I want to query off this table to show the MAX of the determiner at each
PO & Item #. Meaning I would want to query to show only, the second & fourth
lines, determiner -279 & -165.

I've tried using the Dmax function, but I don't think its helping me or it's
not what I need. Any help???

Thanks, Jennifer

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

Probably this:

SELECT [PO #], [Item#],
MAX([Price Effect Date]-[PO Create Date]) As Determiner
FROM table_name
WHERE ... some criteria? ...
GROUP BY [PO #], [Item#]

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSmSWK4echKqOuFEgEQLkngCg/Ou7ayPhX6lLBbz78wGa8twSLrkAn1Ni
pN3QKaLIm3F8rBS3U3dOWw9B
=uwkN
-----END PGP SIGNATURE-----

 

[Jennifer]

In general that works- but obviously only returns several fields. I need it
to return all the fields, while searching for the MAX of the determiner
within each PO/Item #.

First hand- I'm working on a pricing database- and if you know anything about
prices, they are Always changing...
My current table is set up as follows:
Fields:
Vendor, PO #, Item #, Price, PO Create Date, Price Effect Date, Determiner.

Field: Determiner is calculated; "Price Effect Date - PO Create Date"
I have a qry that deletes all positive numbers. I know want to qry the "MAX"
of the determiner to find the lowest negative # per each po# & item #.

For Example:
PO # Item# Price PO Create Date Price Effect Date
Determiner
4414541 0464164 $10.00 2/9/09 1/28/08
-378
4414541 0464164 $11.08 2/9/09 5/6/08
-279
4464641 8090797 $12.03 2/9/09 3/27/08
-319
4464641 8090797 $12.97 2/9/09 8/28/08
-165

So, I want to query off this table to show the MAX of the determiner at each
PO & Item #. Meaning I would want to query to show only, the second & fourth
lines, determiner -279 & -165.

I've tried using the Dmax function, but I don't think its helping me or it's
not what I need. Any help???

Thanks, Jennifer

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

Probably this:

SELECT [PO #], [Item#],
MAX([Price Effect Date]-[PO Create Date]) As Determiner
FROM table_name
WHERE ... some criteria? ...
GROUP BY [PO #], [Item#]

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSmSWK4echKqOuFEgEQLkngCg/Ou7ayPhX6lLBbz78wGa8twSLrkAn1Ni
pN3QKaLIm3F8rBS3U3dOWw9B
=uwkN
-----END PGP SIGNATURE-----

 

[Jennifer]

Disregard previous post. Though it does only bring back three fields... I'll
be able to make this query a make-table... Link to original and pinpoint the
specific ones I want, allowing me to have all information but still use the
max.

Thanks!!!!

First hand- I'm working on a pricing database- and if you know anything about
prices, they are Always changing...
My current table is set up as follows:
Fields:
Vendor, PO #, Item #, Price, PO Create Date, Price Effect Date, Determiner.

Field: Determiner is calculated; "Price Effect Date - PO Create Date"
I have a qry that deletes all positive numbers. I know want to qry the "MAX"
of the determiner to find the lowest negative # per each po# & item #.

For Example:
PO # Item# Price PO Create Date Price Effect Date
Determiner
4414541 0464164 $10.00 2/9/09 1/28/08
-378
4414541 0464164 $11.08 2/9/09 5/6/08
-279
4464641 8090797 $12.03 2/9/09 3/27/08
-319
4464641 8090797 $12.97 2/9/09 8/28/08
-165

So, I want to query off this table to show the MAX of the determiner at each
PO & Item #. Meaning I would want to query to show only, the second & fourth
lines, determiner -279 & -165.

I've tried using the Dmax function, but I don't think its helping me or it's
not what I need. Any help???

Thanks, Jennifer

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

Probably this:

SELECT [PO #], [Item#],
MAX([Price Effect Date]-[PO Create Date]) As Determiner
FROM table_name
WHERE ... some criteria? ...
GROUP BY [PO #], [Item#]

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSmSWK4echKqOuFEgEQLkngCg/Ou7ayPhX6lLBbz78wGa8twSLrkAn1Ni
pN3QKaLIm3F8rBS3U3dOWw9B
=uwkN
-----END PGP SIGNATURE-----