Query Help

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

ryan.fitzpatrick3

I have a list of item numbers which have all the cost change
transactions next to it, meaning, everytime that item price has gone
up or down its recorded. So an item could look like

# 300365 1/1/2000 $21.00
3/5/2002 $ 22.20
6/8/2003 $ 20.80
2/1/2006 $ 20.50
10/12/2007 $22.20

This would be an example of an item with cost changes with price. I'm
running a query that has date parameters on it, so far this works
fine. What I'm having trouble with is lets say I want to run a query
between 1/1/2006 to 12/31/2006, for a full year, how do I get the
price of $20.50 to reflect instead of $22.20? I'm tried max and last
in the query field which has the pricing, but that just gives me the
max and last price for that item code. Does this make sense? Did I
explain enough? Here is SQL just in case.

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,
Max(QryVendorPriceFreightSum.LastOfTotal) AS MaxOfLastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
AND (QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key)
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
HAVING (((QryPODetailsQuantitySpend.in_desc) Like "*" & [Item Name] &
"*"));



LastofTotal is the query field that contains the pricing. Thanks in
advance.

Ryan
 
This is an example, I don't want to hard code the parameters, I want
the query to figure out what is the last cost increase during that
time frame, it could be 1 month, 1 quarter, 1 year time frame not
necessarily 2006, that was an example.


In the criteria for date put Between 1/1/2006 and 12/31/2006

[email protected] said:
I have a list of item numbers which have all the cost change
transactions next to it, meaning, everytime that item price has gone
up or down its recorded. So an item could look like
Click to expand...
# 300365 1/1/2000 $21.00
3/5/2002 $ 22.20
6/8/2003 $ 20.80
2/1/2006 $ 20.50
10/12/2007 $22.20
Click to expand...
This would be an example of an item with cost changes with price. I'm
running a query that has date parameters on it, so far this works
fine. What I'm having trouble with is lets say I want to run a query
between 1/1/2006 to 12/31/2006, for a full year, how do I get the
price of $20.50 to reflect instead of $22.20? I'm tried max and last
in the query field which has the pricing, but that just gives me the
max and last price for that item code. Does this make sense? Did I
explain enough? Here is SQL just in case.
Click to expand...
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,
Max(QryVendorPriceFreightSum.LastOfTotal) AS MaxOfLastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
AND (QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key)
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
HAVING (((QryPODetailsQuantitySpend.in_desc) Like "*" & [Item Name] &
"*"));
Click to expand...
LastofTotal is the query field that contains the pricing. Thanks in
advance.
Click to expand...
Ryan
Click to expand...
Click to expand...
 
This may not help, but in your query hit the totals button to turn on totals,
group bys, etc. Scroll down to the "Last" criteria. That should give you the
last entered. Do that on your date column.

[email protected] said:
This is an example, I don't want to hard code the parameters, I want
the query to figure out what is the last cost increase during that
time frame, it could be 1 month, 1 quarter, 1 year time frame not
necessarily 2006, that was an example.


In the criteria for date put Between 1/1/2006 and 12/31/2006

[email protected] said:
I have a list of item numbers which have all the cost change
transactions next to it, meaning, everytime that item price has gone
up or down its recorded. So an item could look like
Click to expand...
# 300365 1/1/2000 $21.00
3/5/2002 $ 22.20
6/8/2003 $ 20.80
2/1/2006 $ 20.50
10/12/2007 $22.20
Click to expand...
This would be an example of an item with cost changes with price. I'm
running a query that has date parameters on it, so far this works
fine. What I'm having trouble with is lets say I want to run a query
between 1/1/2006 to 12/31/2006, for a full year, how do I get the
price of $20.50 to reflect instead of $22.20? I'm tried max and last
in the query field which has the pricing, but that just gives me the
max and last price for that item code. Does this make sense? Did I
explain enough? Here is SQL just in case.
Click to expand...
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,
Max(QryVendorPriceFreightSum.LastOfTotal) AS MaxOfLastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
AND (QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key)
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
HAVING (((QryPODetailsQuantitySpend.in_desc) Like "*" & [Item Name] &
"*"));
Click to expand...
LastofTotal is the query field that contains the pricing. Thanks in
advance.
Click to expand...
Ryan
Click to expand...
Click to expand...
Click to expand...
 
I did that, the issue is if I have a cost change on 3/11/08 and I want
to pull data from 1/1/2007 to 12/31/2007 it'll pull the LAST date
recorded, being 3/11/08. I tried that method.


This may not help, but in your query hit the totals button to turn on totals,
group bys, etc. Scroll down to the "Last" criteria. That should give you the
last entered. Do that on your date column.

[email protected] said:
This is an example, I don't want to hard code the parameters, I want
the query to figure out what is the last cost increase during that
time frame, it could be 1 month, 1 quarter, 1 year time frame not
necessarily 2006, that was an example.
Click to expand...
In the criteria for date put Between 1/1/2006 and 12/31/2006
:
I have a list of item numbers which have all the cost change
transactions next to it, meaning, everytime that item price has gone
up or down its recorded. So an item could look like
# 300365 1/1/2000 $21.00
3/5/2002 $ 22.20
6/8/2003 $ 20.80
2/1/2006 $ 20.50
10/12/2007 $22.20
This would be an example of an item with cost changes with price. I'm
running a query that has date parameters on it, so far this works
fine. What I'm having trouble with is lets say I want to run a query
between 1/1/2006 to 12/31/2006, for a full year, how do I get the
price of $20.50 to reflect instead of $22.20? I'm tried max and last
in the query field which has the pricing, but that just gives me the
max and last price for that item code. Does this make sense? Did I
explain enough? Here is SQL just in case.
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,
Max(QryVendorPriceFreightSum.LastOfTotal) AS MaxOfLastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSum ON
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
AND (QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key)
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
HAVING (((QryPODetailsQuantitySpend.in_desc) Like "*" & [Item Name] &
"*"));
LastofTotal is the query field that contains the pricing. Thanks in
advance.
Ryan
Click to expand...
Click to expand...
Click to expand...
 
I did that, the issue is if I have a cost change on 3/11/08 and I want
to pull data from 1/1/2007 to 12/31/2007 it'll pull the LAST date
recorded, being 3/11/08. I tried that method.

This may not help, but in your query hit the totals button to turn on totals,
group bys, etc. Scroll down to the "Last" criteria. That should give youthe
last entered. Do that on your date column.
Click to expand...
[email protected] said:
This is an example, I don't want to hard code the parameters, I want
the query to figure out what is the last cost increase during that
time frame, it could be 1 month, 1 quarter, 1 year time frame not
necessarily 2006, that was an example.
On Mar 11, 10:32 am, Golfinray <[email protected]>
wrote:
In the criteria for date put Between 1/1/2006 and 12/31/2006
:
I have a list of item numbers which have all the cost change
transactions next to it, meaning, everytime that item price has gone
up or down its recorded. So an item could look like
# 300365 1/1/2000 $21.00
              3/5/2002 $ 22.20
              6/8/2003 $ 20.80
              2/1/2006 $ 20.50
            10/12/2007 $22.20
This would be an example of an item with cost changes with price. I'm
running a query that has date parameters on it, so far this works
fine. What I'm having trouble with is lets say I want to run a query
between 1/1/2006 to 12/31/2006, for a full year, how do I get the
price of $20.50 to reflect instead of $22.20? I'm tried max and last
in the query field which has the pricing, but that just gives me the
max and last price for that item code. Does this make sense? Did I
explain enough? Here is SQL just in case.
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,
Max(QryVendorPriceFreightSum.LastOfTotal) AS MaxOfLastOfTotal
FROM QryPODetailsQuantitySpend INNER JOIN QryVendorPriceFreightSumON
(QryPODetailsQuantitySpend.in_desc = QryVendorPriceFreightSum.in_desc)
AND (QryPODetailsQuantitySpend.in_item_key =
QryVendorPriceFreightSum.in_item_key) AND
(QryPODetailsQuantitySpend.en_vend_name =
QryVendorPriceFreightSum.en_vend_name) AND
(QryPODetailsQuantitySpend.en_vend_key =
QryVendorPriceFreightSum.en_vend_key) AND
(QryPODetailsQuantitySpend.so_brnch_key =
QryVendorPriceFreightSum.so_brnch_key) AND
(QryPODetailsQuantitySpend.gl_cmp_key =
QryVendorPriceFreightSum.gl_cmp_key)
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
HAVING (((QryPODetailsQuantitySpend.in_desc) Like "*" & [Item Name] &
"*"));
LastofTotal is the query field that contains the pricing. Thanks in
advance.
Ryan- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

Create a form with a text box for the start date (i.e. 1/1/2006) and a
text box for the end date (i.e. 12/31/2006). Then you can fill in
what ever dates you want. Have your query pull the dates from the
text boxes.

For example:
Form name: frmDatedQuery
Start Date text box: txtStDate
End Date text box: txtEnDate

Simple Query:
SELECT tblStuff.ItemNumber, Max(tblStuff.ItemPrice) AS MaxPrice
FROM tblStuff
WHERE tblStuff.EventDate BETWEEN Forms!frmDatedQuery!txtStDate AND
Forms!frmDatedQuery!txtENDate
GROUP BY tblStuff.ItemNumber

Hope this helps,
Chris M.
 
Back
Top