How do I consider price of purchase combined with date of order?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello guys!

I have a problem on extracting the right prices of a product. Hope you can
help.

The price of a product I acquire has a very unstable price. In May I buy it
for $10,00 and in June for $20,00. When this product goes to my stock and I
order it in May for example, I want to consider my cost as $10,00. Right now
the best I can do as set the Query as average. I'm sure though that there's
gotta be a smarter way, like considering the most recent purchase?

Any suggestions?
 
I manage this by storing the each receipt of stock in seperate batches, so
that as you use the batch you can see the actual cost, and thus profit
margin.

You can also have more than one cost field in the stock table.

I have a "Average cost", which is the average of the costs in the batches
that hold stock, and "last cost" which is the cost I last purchased at, a
"Fixed cost" which is my suppliers fixed price in their currency and finally
"expected cost" which is the price I expect to pay next time I purchase.

My "expected cost" is a calculated field so its not actually in the table,
and it takes into account the current exchange rate.



I hope this helps

Jon
 
You have a table called something like ProductCost that's related to the
Products table. You add any product price changes to this table and the date
of the change. That way you can find out what the product cost you on a
certain day or month.
 
If your table of prices is like a table about taxes (which may fluctuate in
any direction, from time to time), like the following table


Taxes 'table name
Rate, FromDate, ToDate ' fields
0.06 1/1/2001 31/12/2003
0.07 1/1/2004 12/6/2007
0.03 13/6/2007 null


Which is self explanatory, except for the null, meaning that the closing
date for the last interval is not known, yet.



So, having a billing date, you can check the applicable tax rate:


SELECT ...., taxes.rate, ...
FROM bills INNER JOIN taxes
ON ( bills.dateOfBilling BETWEEN taxes.fromDate AND Nz(taxes.ToDate,
Now ))



Sure, it is your job to be sure that the FromDate / ToDate have no
overlapping.


If the billing date field has ALSO a time value, preferable to use:


Taxes 'table name
Rate, FromDate, ToDate ' fields
0.06 1/1/2001 1/1/2004
0.07 1/1/2004 13/6/2007
0.03 13/6/2007 null


and


SELECT ...., taxes.rate, ...
FROM bills INNER JOIN taxes
ON ( bills.dateOfBilling >= taxes.fromDate AND bills.dateOfBilling <
Nz(taxes.ToDate, Now ))


where the upper limit has changed from <= (implicitly taken using BETWEEN)
to a strict <



Hoping it may help,
Vanderghast, Access MVP
 
Hi guys thanks for your quick replies!

I might have complicated. My problem is actually quite simple.
I buy pens everymonth and they have different prices.
In january it was $5,00 and in March $2,00. Different departments of the
company order pens. If I order a pen in january 30th, I want the cost
considered to be $5,00 as it was my closest purchase.

I have a query for product (IDprodutc/product), orders
(IDproduct/quantity/dateordered) and purchase
(IDproduct/quantity/datepurchased). Any ideas??

Cheers!
 
SELECT *
FROM prices AS p
WHERE p.purchasedDate = ( SELECT MAX(q.purchasedDate)
FROM prices As q
WHERE q.productID =
p.productID
AND q.purchasedDate
<= p.purchasedDate)


should do.


Hoping it may help,
Vanderghast, Access MVP
 
Hello Michel,

Sorry I forgot to mention that I'm a almost beginner!
I didn't get it , sorry...

My two tables are:
1) productID, purchasedDate, price
2) productID, orderedDate, qtyOrdered

And I needed to design a query with one single price that is most appoximate
between purchase and order dates.

Thanks in advance once again mate.
 
SELECT p.productID,
p.orderedDate,
LAST(p.qtyOrdered),
LAST(q.price)

FROM (table2 AS p INNER JOIN table1 AS q
ON p.productID = q.productID
AND p.orderedDate >= q.purchasedDate)
INNER JOIN table1 AS r
ON p.productID = r.productID
AND p.orderedDate >= r.purchasedDate

GROUP BY p.productID, p.orderedDate, q.purchasedDate


HAVING q.purchasedDate = MAX(r.purchasedDate)
 
I should have explain a little.

Assume there is just one productID, for simplicity.

The FROM clause produces 3 'alias' (pointers). "p" is about the record we
try to find the price, "q" is about all records that speak of "p", but with
a previous date (ie, occurring before p.orderedDate), and "r" is identical
to "q", in the FROM clause.

The GROUP BY clause fixes p.orderedDate, and q.purchasedDate, but not
r.purchasedDate. That means we then have a GROUP for each pair
{p.orderedDate, q.purchasedDate}. For each of these groups, we have many
possible rows from "r", associated to each group.

Again, to simplify, imagine a single record from p (with 2002.01.01 date)
and three records in q (2001.01.01, 2002.01.01, 2010.01.01).

For the given record p, the join removed the last record from q. Given the
GROUP BY clause, we can summarize our records, per group, like:


p.orderedDate, q.purchasedDate, all r.purchasedDate
2002.01.01 2001.01.01 {2001.01.01, 2002.01.01}
2002.01.01 2002.01.01 {2001.01.01, 2002.01.01}


HAVING multiple row,we can compute MAX(r.purchasedDate), for each GROUP.

In our example, that is 2002.01.01 for both groups.


Now, the HAVING clause of the SQL statement removes the GROUPs that do not
satisfy
q.purchasedDate = MAX(r.purchasedDate)

which, in our example, eliminates the first group, keeping only the second
group. It is that (ONLY) group implying p.orderedDate that will supply
q.price, as wanted.


Question of syntax, we must aggregate, in the SELECT clause, what is not in
the GROUP BY clause, so, here, since we operate on a single record anyhow,
we can use any datatype compatible aggregate. I use LAST, personally, in
that case.


Hoping it may help,
Vanderghast, Access MVP
 
OK Michel,
Thank you very very much!!!!!!!!!

Michel Walsh said:
I should have explain a little.

Assume there is just one productID, for simplicity.

The FROM clause produces 3 'alias' (pointers). "p" is about the record we
try to find the price, "q" is about all records that speak of "p", but with
a previous date (ie, occurring before p.orderedDate), and "r" is identical
to "q", in the FROM clause.

The GROUP BY clause fixes p.orderedDate, and q.purchasedDate, but not
r.purchasedDate. That means we then have a GROUP for each pair
{p.orderedDate, q.purchasedDate}. For each of these groups, we have many
possible rows from "r", associated to each group.

Again, to simplify, imagine a single record from p (with 2002.01.01 date)
and three records in q (2001.01.01, 2002.01.01, 2010.01.01).

For the given record p, the join removed the last record from q. Given the
GROUP BY clause, we can summarize our records, per group, like:


p.orderedDate, q.purchasedDate, all r.purchasedDate
2002.01.01 2001.01.01 {2001.01.01, 2002.01.01}
2002.01.01 2002.01.01 {2001.01.01, 2002.01.01}


HAVING multiple row,we can compute MAX(r.purchasedDate), for each GROUP.

In our example, that is 2002.01.01 for both groups.


Now, the HAVING clause of the SQL statement removes the GROUPs that do not
satisfy
q.purchasedDate = MAX(r.purchasedDate)

which, in our example, eliminates the first group, keeping only the second
group. It is that (ONLY) group implying p.orderedDate that will supply
q.price, as wanted.


Question of syntax, we must aggregate, in the SELECT clause, what is not in
the GROUP BY clause, so, here, since we operate on a single record anyhow,
we can use any datatype compatible aggregate. I use LAST, personally, in
that case.


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top