Query Help

[ryan.fitzpatrick3]

I have a query that pulls usage volumes, this works fine. The catch is
I have a list of all of the prices that that item has ever had (price
increases and decreases)

i.e. Item #300500

prices would look like this

1/1/1999 $4.50
3/4/2000 $ 4.10
7/8/2003 $ 5.56
11/1/2006 $ 5.20
1/1/2008 $ 6.00

I have a date parameter in the query that a person can program when
they would like to pull the from i.e. between 1/1/2007 and 12/31/2007.
is there a way to have it where what ever date they put into the
datefield that it'll pull the last date applying to that range? So it
would pull 11/1/2006 since it was the last price change. Can this be
done? I'm not so good at VBA.

Ryan

 

[KARL DEWEY]

Try this --
SELECT Pricing.Item, First([Price]+0) AS [Price as of],
Max(Pricing.PriceDate) AS [Pricing Date]
FROM Pricing
GROUP BY Pricing.Item
HAVING (((Max(Pricing.PriceDate))<=CVDate([Enter begining date])));

 

[ryan.fitzpatrick3]

Where do I put that in SQL. Here is my code for the query

SELECT QryPODetailsQuantitySpend.gl_cmp_key,
QryPODetailsQuantitySpend.so_brnch_key,
QryPODetailsQuantitySpend.en_vend_key,
QryPODetailsQuantitySpend.en_vend_name,
QryPODetailsQuantitySpend.in_item_key,
QryPODetailsQuantitySpend.in_desc,
Sum(QryPODetailsQuantitySpend.po_dtl_ordqt) AS SumOfpo_dtl_ordqt,
QryPODetailsQuantitySpend.po_dtl_uom,
Sum(QryPODetailsQuantitySpend.Spend) AS SumOfSpend,
QryVendorPriceFreightSum.LastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
GROUP BY QryPODetailsQuantitySpend.gl_cmp_key,
QryPODetailsQuantitySpend.so_brnch_key,
QryPODetailsQuantitySpend.en_vend_key,
QryPODetailsQuantitySpend.en_vend_name,
QryPODetailsQuantitySpend.in_item_key,
QryPODetailsQuantitySpend.in_desc,
QryPODetailsQuantitySpend.po_dtl_uom,
QryVendorPriceFreightSum.LastOfTotal
HAVING (((QryPODetailsQuantitySpend.in_item_key)=[Item #]));




in_item_key is the item #
po_hdr_orddt is the date
lastoftotal is the pricing

I'm sorry i'm not that versed in SQL yet to manipulate it. Can you
help me out?

Ryan

Try this --
SELECT Pricing.Item, First([Price]+0) AS [Price as of],
Max(Pricing.PriceDate) AS [Pricing Date]
FROM Pricing
GROUP BY Pricing.Item
HAVING (((Max(Pricing.PriceDate))<=CVDate([Enter begining date])));

--
KARL DEWEY
Build a little - Test a little

I have a query that pulls usage volumes, this works fine. The catch is
I have a list of all of the prices that that item has ever had (price
increases and decreases)

i.e. Item #300500

prices would look like this

1/1/1999 $4.50
3/4/2000 $ 4.10
7/8/2003 $ 5.56
11/1/2006 $ 5.20
1/1/2008 $ 6.00

I have a date parameter in the query that a person can program when
they would like to pull the from i.e. between 1/1/2007 and 12/31/2007.
is there a way to have it where what ever date they put into the
datefield that it'll pull the last date applying to that range? So it
would pull 11/1/2006 since it was the last price change. Can this be
done? I'm not so good at VBA.

Ryan

 

[KARL DEWEY]

po_hdr_orddt is not in your query. What table is it in?
What is the table name that has your pricing information?
--
KARL DEWEY
Build a little - Test a little

Where do I put that in SQL. Here is my code for the query

SELECT QryPODetailsQuantitySpend.gl_cmp_key,
QryPODetailsQuantitySpend.so_brnch_key,
QryPODetailsQuantitySpend.en_vend_key,
QryPODetailsQuantitySpend.en_vend_name,
QryPODetailsQuantitySpend.in_item_key,
QryPODetailsQuantitySpend.in_desc,
Sum(QryPODetailsQuantitySpend.po_dtl_ordqt) AS SumOfpo_dtl_ordqt,
QryPODetailsQuantitySpend.po_dtl_uom,
Sum(QryPODetailsQuantitySpend.Spend) AS SumOfSpend,
QryVendorPriceFreightSum.LastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
GROUP BY QryPODetailsQuantitySpend.gl_cmp_key,
QryPODetailsQuantitySpend.so_brnch_key,
QryPODetailsQuantitySpend.en_vend_key,
QryPODetailsQuantitySpend.en_vend_name,
QryPODetailsQuantitySpend.in_item_key,
QryPODetailsQuantitySpend.in_desc,
QryPODetailsQuantitySpend.po_dtl_uom,
QryVendorPriceFreightSum.LastOfTotal
HAVING (((QryPODetailsQuantitySpend.in_item_key)=[Item #]));




in_item_key is the item #
po_hdr_orddt is the date
lastoftotal is the pricing

I'm sorry i'm not that versed in SQL yet to manipulate it. Can you
help me out?

Ryan

Try this --
SELECT Pricing.Item, First([Price]+0) AS [Price as of],
Max(Pricing.PriceDate) AS [Pricing Date]
FROM Pricing
GROUP BY Pricing.Item
HAVING (((Max(Pricing.PriceDate))<=CVDate([Enter begining date])));

--
KARL DEWEY
Build a little - Test a little

I have a query that pulls usage volumes, this works fine. The catch is
I have a list of all of the prices that that item has ever had (price
increases and decreases)

i.e. Item #300500

prices would look like this

1/1/1999 $4.50
3/4/2000 $ 4.10
7/8/2003 $ 5.56
11/1/2006 $ 5.20
1/1/2008 $ 6.00

I have a date parameter in the query that a person can program when
they would like to pull the from i.e. between 1/1/2007 and 12/31/2007.
is there a way to have it where what ever date they put into the
datefield that it'll pull the last date applying to that range? So it
would pull 11/1/2006 since it was the last price change. Can this be
done? I'm not so good at VBA.

Ryan