hopefully easy DMAX question

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

I know basically how DMAX works. This is what I have so far.

LastPrice: DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl")

where in_vpbrk_effdt is the date. The date is in this format
12/12/2007, I run a query on the table then run another query off of
the first query that uses the DMAX function. Now everything works
except that the date pulled

Let's say I have three dates for one item #.

02/02/1999
05/13/2003
09/08/2007

DMAX should pull 09/08/2007 right? But on my query (with DMAX) I get
01/01/2100 date. What is the issue?

Here is the SQL if anyone needs to see it.

SELECT QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl") AS LastPrice
FROM QryVendorPriceFreight
GROUP BY QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl");

Thanks,

Ryan
 
I know basically how DMAX works. This is what I have so far.

LastPrice: DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl")

where in_vpbrk_effdt is the date. The date is in this format
12/12/2007, I run a query on the table then run another query off of
the first query that uses the DMAX function. Now everything works
except that the date pulled

Let's say I have three dates for one item #.

02/02/1999
05/13/2003
09/08/2007

DMAX should pull 09/08/2007 right? But on my query (with DMAX) I get
01/01/2100 date. What is the issue?

Here is the SQL if anyone needs to see it.

SELECT QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl") AS LastPrice
FROM QryVendorPriceFreight
GROUP BY QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl");
Click to expand...


There are no criteria applied to the DMax expression -- no third argument
supplied -- so it will always pull the maximum date in the table/query
"ingres_in_vpbrk_tbl". You could apply a criteria argument to limit the
dates examined to those for the current set of keys, or maybe you could use
the Max() function in your query instead of DMax(). The Max() function
would be more efficient.

Maybe this would work for you:

SELECT
gl_cmp_key,
so_brnch_key,
en_vend_key,
en_vend_name,
in_item_key,
in_desc,
Max([in_vpbrk_effdt]) AS LastPrice
FROM QryVendorPriceFreight
GROUP BY
gl_cmp_key,
so_brnch_key,
en_vend_key,
en_vend_name,
in_item_key,
in_desc;

However, I don't know whether the field [in_vpbrk_effdt] exists in the query
QryVendorPriceFreight, or exactly at what level of grouping you want to get
the latest date, so that may be wide of the mark.
 
Thank you for the quick reply, that did the trick thank you!


I know basically how DMAX works. This is what I have so far.
Click to expand...
LastPrice: DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl")
Click to expand...
where in_vpbrk_effdt is the date. The date is in this format
12/12/2007, I run a query on the table then run another query off of
the first query that uses the DMAX function. Now everything works
except that the date pulled
Click to expand...
Let's say I have three dates for one item #.

DMAX should pull 09/08/2007 right? But on my query (with DMAX) I get
01/01/2100 date. What is the issue?
Click to expand...
Here is the SQL if anyone needs to see it.
Click to expand...
SELECT QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl") AS LastPrice
FROM QryVendorPriceFreight
GROUP BY QryVendorPriceFreight.gl_cmp_key,
QryVendorPriceFreight.so_brnch_key, QryVendorPriceFreight.en_vend_key,
QryVendorPriceFreight.en_vend_name, QryVendorPriceFreight.in_item_key,
QryVendorPriceFreight.in_desc,
DMax("[in_vpbrk_effdt]","ingres_in_vpbrk_tbl");
Click to expand...

There are no criteria applied to the DMax expression -- no third argument
supplied -- so it will always pull the maximum date in the table/query
"ingres_in_vpbrk_tbl". You could apply a criteria argument to limit the
dates examined to those for the current set of keys, or maybe you could use
the Max() function in your query instead of DMax(). The Max() function
would be more efficient.

Maybe this would work for you:

SELECT
gl_cmp_key,
so_brnch_key,
en_vend_key,
en_vend_name,
in_item_key,
in_desc,
Max([in_vpbrk_effdt]) AS LastPrice
FROM QryVendorPriceFreight
GROUP BY
gl_cmp_key,
so_brnch_key,
en_vend_key,
en_vend_name,
in_item_key,
in_desc;

However, I don't know whether the field [in_vpbrk_effdt] exists in the query
QryVendorPriceFreight, or exactly at what level of grouping you want to get
the latest date, so that may be wide of the mark.
Click to expand...
 
Back
Top