If Then. Maybe?

  • Thread starter Thread starter RedHeadedMonster via AccessMonster.com
  • Start date Start date
R

RedHeadedMonster via AccessMonster.com

I've got a table that lists parts and prices. Prices are down by Years.
Currently Years are 2007, 2008 & 2009.

What I need to do is determine IF year = 2008 & Price is Null, THEN i need to
take the Price that is listed in year = 2007 and escalate that price by a set
percentage and then use that as the 2008 price.

Any suggestions?
RHM
 
Please provide more information - Are the years 2007, 2008 and 2009 separate
fields in the same table (BAD design!!), or are they in a separate table?

John
 
One method of handling this

NZ([2008 Price],[2007 Price] * (1 + IncreaseFactor]))
OR

IIF([2008 Price] Is Not Null,[2008 Price],[2007 Price] * (1 +
IncreaseFactor]))


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Where Price1 is to Qty1, etc. So they can adjust prices due to quantity
discounts, ie, the more you buy the cheaper they are each.

I've inherited this database (always fun.....NOT), so trying to assist this
group produce their product and make recommendations for improvements. Im
not happy with how its currently put together, but..having to work with what
I have for the time being (unfortunately)

Heres whats happening though, For some parts we have vendor quotes for 2007,
2008 & 2009. for others we just have a quote for 2007, thats when the
escalation would come into play.

ex.

23456, 2007, 1.16, 1.13, 1.10, 1, 5, 10
23456, 2008. 1.20, 1.17, 1.15, 1, 5, 10
23456, 2009, 1.22, 1.20, 1.17, 1, 5, 10
1234, 2007, 34.20, 33.50, 32.00, 1, 25, 75
1234, 2008
1234, 2009
4321, 2007, 128.12
4321, 2008
4321, 2009

so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008.
Basically its for the creation of a Catalogue of sorts

Any suggestions would be great
RHM

J_Goddard said:
Please provide more information - Are the years 2007, 2008 and 2009 separate
fields in the same table (BAD design!!), or are they in a separate table?

John
I've got a table that lists parts and prices. Prices are down by Years.
Currently Years are 2007, 2008 & 2009.
Click to expand...
[quoted text clipped - 5 lines]
Any suggestions?
RHM
Click to expand...
Click to expand...
 
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Where Price1 is to Qty1, etc. So they can adjust prices due to quantity
discounts, ie, the more you buy the cheaper they are each.
Click to expand...

Yep. BADLY denormalized! "Committing spreadsheet upon a database".
I've inherited this database (always fun.....NOT), so trying to assist this
group produce their product and make recommendations for improvements. Im
not happy with how its currently put together, but..having to work with what
I have for the time being (unfortunately)

Heres whats happening though, For some parts we have vendor quotes for 2007,
2008 & 2009. for others we just have a quote for 2007, thats when the
escalation would come into play.

ex.

23456, 2007, 1.16, 1.13, 1.10, 1, 5, 10
23456, 2008. 1.20, 1.17, 1.15, 1, 5, 10
23456, 2009, 1.22, 1.20, 1.17, 1, 5, 10
1234, 2007, 34.20, 33.50, 32.00, 1, 25, 75
1234, 2008
1234, 2009
4321, 2007, 128.12
4321, 2008
4321, 2009

so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008.
Basically its for the creation of a Catalogue of sorts

Any suggestions would be great
Click to expand...

Stop struggling with this badly flawed design, and just normalize it. You
should have a parts table, related one to many to a price table with fields
Partno, EffectiveDate, EndDate, MinQuantity, Price; this would have multiple
rows for each part, rather than multiple fields. You can run Append queries to
migrate the data. I suggest the two date fields rather than just a year field
because someday you might want to change prices on some date other than
January 1.

John W. Vinson [MVP]
 
Exactly, it was a spreadsheet........ a 50MG spreadsheet. Thats not a typo
thats 50MG. You should of seen the Macros they had and the convoluted
formulas to create a report for them. They at least decided, yes was need
this in a database and then imported, directly into Access. AS IS. Now they
want their reports running yesterday and dont want to spend the time or money
to do it right. I should just go on and normalize it and be done with it,
you are right.......Im screwed. LOL.
RHM
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Where Price1 is to Qty1, etc. So they can adjust prices due to quantity
discounts, ie, the more you buy the cheaper they are each.
Click to expand...

Yep. BADLY denormalized! "Committing spreadsheet upon a database".
I've inherited this database (always fun.....NOT), so trying to assist this
group produce their product and make recommendations for improvements. Im
Click to expand...
[quoted text clipped - 21 lines]
Any suggestions would be great
Click to expand...

Stop struggling with this badly flawed design, and just normalize it. You
should have a parts table, related one to many to a price table with fields
Partno, EffectiveDate, EndDate, MinQuantity, Price; this would have multiple
rows for each part, rather than multiple fields. You can run Append queries to
migrate the data. I suggest the two date fields rather than just a year field
because someday you might want to change prices on some date other than
January 1.

John W. Vinson [MVP]
Click to expand...
 
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Where Price1 is to Qty1, etc. So they can adjust prices due to quantity
discounts, ie, the more you buy the cheaper they are each.

I've inherited this database (always fun.....NOT), so trying to assist this
group produce their product and make recommendations for improvements. Im
not happy with how its currently put together, but..having to work with what
I have for the time being (unfortunately)

Heres whats happening though, For some parts we have vendor quotes for 2007,
2008 & 2009. for others we just have a quote for 2007, thats when the
escalation would come into play.

ex.

23456, 2007, 1.16, 1.13, 1.10, 1, 5, 10
23456, 2008. 1.20, 1.17, 1.15, 1, 5, 10
23456, 2009, 1.22, 1.20, 1.17, 1, 5, 10
1234, 2007, 34.20, 33.50, 32.00, 1, 25, 75
1234, 2008
1234, 2009
4321, 2007, 128.12
4321, 2008
4321, 2009

so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008.
Click to expand...

Ok... this can be done, even with this mess. But what do you mean by
"escalate"? That term may have meaning for you but doesn't for me.


John W. Vinson [MVP]
 
Escalate is double talk for Adjust for Inflation. For Example, you take the
2007 Price and Escalate(Inflate) it by 3%.
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Click to expand...
[quoted text clipped - 23 lines]
so for parts 1234 & 4321 I would need to escalate the 2007 price to 2008.
Click to expand...

Ok... this can be done, even with this mess. But what do you mean by
"escalate"? That term may have meaning for you but doesn't for me.

John W. Vinson [MVP]
Click to expand...
 
Escalate is double talk for Adjust for Inflation. For Example, you take the
2007 Price and Escalate(Inflate) it by 3%.
Click to expand...

Ok: an Update query should work. Put a unique Index on the combination of
fields PartNumber and Year (open the table in design view and use the Indexes
tool; put some distinctive name in the left column, PartNumber in the right,
and Year on the next row down in the right column, and check the Unique
checkbox; or else ctrl-click PartNumber and Year and then the Key icon to make
these two fields a joint Primary Key).

Then run the following Update query:

UPDATE yourtable AS ThisYear
INNER JOIN yourtable AS LastYear
ON ThisYear.PartNumber = LastYear.PartNumber
AND ThisYear.Year = LastYear.Year + 1
SET ThisYear.Qty1 = LastYear.Qty1,
ThisYear.Qty2 = LastYear.Qty2,
ThisYear.Qty3 = LastYear.Qty3,
ThisYear.Price1 = Round(LastYear.Price1 * 1.03, 2),
ThisYear.Price2 = Round(LastYear.Price2 * 1.03, 2),
ThisYear.Price3 = Round(LastYear.Price3 * 1.03, 2)
WHERE ThisYear.Price1 IS NULL
AND ThisYear.Price2 IS NULL
AND ThisYear.Price3 IS NULL
AND LastYear.Price1 IS NOT NULL
AND LastYear.Price2 IS NOT NULL
And LastYear.Price3 IS NOT NULL;

Back up your database first, and check the results carefully of course!

John W. Vinson [MVP]
 
OK I highjacked their DB and normalized it, much the way you suggested it
and have it running with reports. Should of just did it to start with, but
was already juggling projects. Anyway its done.

So, What I need to do now is...There is a form that allows the user to put in
the quantity of how many parts are wanted (QTYParts) I need to search through
the table and match the QTYParts with MinQty so I can use the correct price.
Remembering that sometimes there is only 1 price and other times there can be
up to 6 discounts. I think I need a loop function to search through, but not
sure how to go about it. Im still a novice when it comes to programming.
Thanx!
RHM
Actually the table is set up as, PartNumber, Year, Price1, Price2, Price3,
Qty1, Qty2, Qty3
Where Price1 is to Qty1, etc. So they can adjust prices due to quantity
discounts, ie, the more you buy the cheaper they are each.
Click to expand...

Yep. BADLY denormalized! "Committing spreadsheet upon a database".
I've inherited this database (always fun.....NOT), so trying to assist this
group produce their product and make recommendations for improvements. Im
Click to expand...
[quoted text clipped - 21 lines]
Any suggestions would be great
Click to expand...

Stop struggling with this badly flawed design, and just normalize it. You
should have a parts table, related one to many to a price table with fields
Partno, EffectiveDate, EndDate, MinQuantity, Price; this would have multiple
rows for each part, rather than multiple fields. You can run Append queries to
migrate the data. I suggest the two date fields rather than just a year field
because someday you might want to change prices on some date other than
January 1.

John W. Vinson [MVP]
Click to expand...
 
OK I highjacked their DB and normalized it, much the way you suggested it
and have it running with reports. Should of just did it to start with, but
was already juggling projects. Anyway its done.

So, What I need to do now is...There is a form that allows the user to put in
the quantity of how many parts are wanted (QTYParts) I need to search through
the table and match the QTYParts with MinQty so I can use the correct price.
Remembering that sometimes there is only 1 price and other times there can be
up to 6 discounts. I think I need a loop function to search through, but not
sure how to go about it. Im still a novice when it comes to programming.
Thanx!
Click to expand...

What's the current structure of your tables and some sample data? I don't
think any sort of loop would be needed or appropriate, but a subquery should
work.
 
Table structure:
PartNumber, Year, Qty, Price

So for data looks like:
XYZ, 2007, 1, $2.40
XYZ, 2007, 10, $2.35
XYZ, 2007, 25, $2.20
XYZ, 2007, 35, $2.15

Where, if you were looking at 2007 prices, for 15 parts, you would want your
search to go thru all the prices and select the $2.35 price.

So I'm trying to figure out how to search through the different quantity
breaks to find the correct price to use for calculations.

RHM


OK I highjacked their DB and normalized it, much the way you suggested it
and have it running with reports. Should of just did it to start with, but
Click to expand...
[quoted text clipped - 7 lines]
sure how to go about it. Im still a novice when it comes to programming.
Thanx!
Click to expand...

What's the current structure of your tables and some sample data? I don't
think any sort of loop would be needed or appropriate, but a subquery should
work.
Click to expand...
 
Table structure:
PartNumber, Year, Qty, Price

So for data looks like:
XYZ, 2007, 1, $2.40
XYZ, 2007, 10, $2.35
XYZ, 2007, 25, $2.20
XYZ, 2007, 35, $2.15

Where, if you were looking at 2007 prices, for 15 parts, you would want your
search to go thru all the prices and select the $2.35 price.

So I'm trying to figure out how to search through the different quantity
breaks to find the correct price to use for calculations.
Click to expand...

You need to get out of the usual programming mindset of loops and sequential
processing. Queries don't work that way! They happen "all at once" - the WHERE
clause of a query is just a Boolean logical statement which (eventually)
evaluates to True (retrieve this record) or False (don't).

You will need a Self Join query joining this table to itself by Partnumber and
Year in order to get the price range within the same record:

SELECT A.PartNumber, A.[Year], A.[Qty] AS Low, B.[Qty] AS High, A.Price
FROM tablename AS A INNER JOIN tablename AS B
ON A.PartNumber = B.Partnumber
AND A.[Year] = B.[Year]
WHERE B.Qty = (SELECT Min(C.[Qty]) FROM tablename AS C WHERE C.Partnumber =
A.Partnumber AND C.[Year] = A.[Year] AND C.Qty > A.Qty);

As written this will need another record with an "infinite" quantity for the
top price bracket, I'm sure there's some clever way to turn the logic around.
 
Back
Top