Price Levels

[shannaj via AccessMonster.com]

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it
to reflect the correct price for the customer's price level. I started by
adding Price level in my customer table to reflect which price level they are
on. then I added the price levels in my items table. (There are 10 price
levels total). But after that I am drawing a blank on how to pull them in to
my form. If at all possible, I would like it to be a combo box that brings
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[Klatuu]

Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)

 

[KARL DEWEY]

I can think of several approaches based on your formulation of price level.
Do you use a base price and then level 1 is a percent reduced and level 5 a
percent increased or all are discrete prices?
Using the percent method the customer would have a field for level. Then
use a multiplier factor for raise or lower price
Level % Reduce Multiplier
0 0 1
1 1.00% 0.99 =1-0.01
2 2.00% 0.98 =1-0.02
3 3.00% 0.97 =1-0.03
4 4.00% 0.96 =1-0.04
5 5.00% 0.95 =1-0.05
10 10.00% 0.9 =1-0.1
15 15.00% 0.85 =1-0.15
-1 101.00% -0.01 =1-1.01
-2 102.00% -0.02 =1-1.02
-3 103.00% -0.03 =1-1.03
-4 104.00% -0.04 =1-1.04
-5 105.00% -0.05 =1-1.05
-10 110.00% -0.1 =1-1.1
-15 115.00% -0.15 =1-1.15
The formula would be one minus the percent stated in decimal form.
Discrete prices will require a translation table.

 

[shannaj via AccessMonster.com]

I try this and it gives me an error: compile error: Syntax error

Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it

[quoted text clipped - 5 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[Klatuu]

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

--
Dave Hargis, Microsoft Access MVP

I try this and it gives me an error: compile error: Syntax error

Use the Form Current event to set the value of the combo box. Use a DLookup
to the customer table to determine the price level, then assign that value to
the combo box:

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = "
& Me.txtCustID)

I have an order entry form that runs off of a query. It started off with
just using the unit price and it works fine. But now my boss is wanting it

[quoted text clipped - 5 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
going to be looking up anything. I will change that to numeric. Also, I had
no CustomerID in my form because it is a subform with just the orderdetails
based from a query. So I guess you could say I have many things wrong. But I
have just been clueless on where to even start.

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

I try this and it gives me an error: compile error: Syntax error

[quoted text clipped - 9 lines]

 

[Klatuu]

To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?
--
Dave Hargis, Microsoft Access MVP

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was
going to be looking up anything. I will change that to numeric. Also, I had
no CustomerID in my form because it is a subform with just the orderdetails
based from a query. So I guess you could say I have many things wrong. But I
have just been clueless on where to even start.

Me.cboPriceLevel = DLookup("[PriceLevel]", "tblCustomer", "[CustID] = " &
Me.txtCustID)

The syntax, as posted, is correct, but there are some assumptions.
The combo box name is cboPriceLevel
The field name that stores the price level is named PriceLevel and is a
numeric field.
The table name is tblCustomer
The name of the table's primary key field is CustID
There is a text box control on the form named txtCustID boud to the field
CustID in the form's recordset.
The code is in the form's code module.

Please check these things.
If you are still having a problem, please copy/paste the code from your
module, so I can see if I missed something

I try this and it gives me an error: compile error: Syntax error

[quoted text clipped - 9 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it
has just thrown me off. There are 10 different prices for each Item in the
table, and different customers are set to different price levels.

To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was

[quoted text clipped - 25 lines]

 

[Klatuu]

Then I think you will need to modify the subform's record source to be a
query that can include the price by customer.
Since I don't know your table structures, I can't give you a specicif
answer, but basically, you should be able to find a price in a query. You
need to have your price table set up so it has fields for the client and the
item. Then you can use a query for the subform's record source that joins
the price list table on the combination of item and client.
--
Dave Hargis, Microsoft Access MVP

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it
has just thrown me off. There are 10 different prices for each Item in the
table, and different customers are set to different price levels.

To help, I need to know a bit about your configuration.
First, I assume you have the invoice header and client info in the main form
and the line item details are in the subform, but what I need to know is how
the price is calculated. If you put an unbound combo on the main form as I
described, you could use the value of the combo in the extended price
calculation.

How is the discount carrired? As a percentage off list, or is it a separate
price list for each client?

You pointed out a couple of problems that I can see. One, My field name that
stores that price level name is a text field because I was not sure if it was

[quoted text clipped - 25 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

I totally get what you are saying, but I can not figure out what field to put
in the price table to link the two together. Here is what my ItemList
contains. Any suggestions?

ItemID
ItemDescription
Active
UnitPrice
PriceLevel2
PriceLevel3
PriceLevel4
PriceLevel5
PriceLevel6
PriceLevel7
PriceLevel8
PriceLevel9
PriceLevel10
SalesAcct

Then I think you will need to modify the subform's record source to be a
query that can include the price by customer.
Since I don't know your table structures, I can't give you a specicif
answer, but basically, you should be able to find a price in a query. You
need to have your price table set up so it has fields for the client and the
item. Then you can use a query for the subform's record source that joins
the price list table on the combination of item and client.

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it

[quoted text clipped - 15 lines]

 

[Klatuu]

For starters, you would use the ItemID
To join to the customer, I don't know, but is SalesAcct the same as the
customer?
Is so, you would join SalesAcct to the Customer and ItemID to the Item
Table's primary key.
--
Dave Hargis, Microsoft Access MVP

I totally get what you are saying, but I can not figure out what field to put
in the price table to link the two together. Here is what my ItemList
contains. Any suggestions?

ItemID
ItemDescription
Active
UnitPrice
PriceLevel2
PriceLevel3
PriceLevel4
PriceLevel5
PriceLevel6
PriceLevel7
PriceLevel8
PriceLevel9
PriceLevel10
SalesAcct

Then I think you will need to modify the subform's record source to be a
query that can include the price by customer.
Since I don't know your table structures, I can't give you a specicif
answer, but basically, you should be able to find a price in a query. You
need to have your price table set up so it has fields for the client and the
item. Then you can use a query for the subform's record source that joins
the price list table on the combination of item and client.

Originally the price was calculated by looking up the unit price in my Items
table. When I found that they wanted the other 9 price levels included, it

[quoted text clipped - 15 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

No, the sales account is a GL account to put the price in to the correct
category.

For starters, you would use the ItemID
To join to the customer, I don't know, but is SalesAcct the same as the
customer?
Is so, you would join SalesAcct to the Customer and ItemID to the Item
Table's primary key.

I totally get what you are saying, but I can not figure out what field to put
in the price table to link the two together. Here is what my ItemList

[quoted text clipped - 27 lines]

 

[Klatuu]

Do you have a field in the Customer table that defines what price level the
customer gets?
--
Dave Hargis, Microsoft Access MVP

No, the sales account is a GL account to put the price in to the correct
category.

For starters, you would use the ItemID
To join to the customer, I don't know, but is SalesAcct the same as the
customer?
Is so, you would join SalesAcct to the Customer and ItemID to the Item
Table's primary key.

I totally get what you are saying, but I can not figure out what field to put
in the price table to link the two together. Here is what my ItemList

[quoted text clipped - 27 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

Yes, I have a field that is called PriceLevel in my customer table.

Do you have a field in the Customer table that defines what price level the
customer gets?

No, the sales account is a GL account to put the price in to the correct
category.

[quoted text clipped - 9 lines]

 

[Klatuu]

Okay, great.
I am about to leave for the day, but I will get back to you tomorrow with
some ideas.
--
Dave Hargis, Microsoft Access MVP

Yes, I have a field that is called PriceLevel in my customer table.

Do you have a field in the Customer table that defines what price level the
customer gets?

No, the sales account is a GL account to put the price in to the correct
category.

[quoted text clipped - 9 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[Klatuu]

What data type is the field PriceLevel in the customer table?
What value does it contain to identify a specific price level?
--
Dave Hargis, Microsoft Access MVP

Okay, great.
I am about to leave for the day, but I will get back to you tomorrow with
some ideas.
--
Dave Hargis, Microsoft Access MVP

Yes, I have a field that is called PriceLevel in my customer table.

Do you have a field in the Customer table that defines what price level the
customer gets?
No, the sales account is a GL account to put the price in to the correct
category.
[quoted text clipped - 9 lines]
up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

I just have it as text because I was not sure what to do with it.

What data type is the field PriceLevel in the customer table?
What value does it contain to identify a specific price level?

Okay, great.
I am about to leave for the day, but I will get back to you tomorrow with

[quoted text clipped - 9 lines]

 

[Klatuu]

What text value identifes which price level?
It would make it easier if you used a numberic value so like 0 = standard
price, 1 = PriceLeve1, etc.

In either case, let me know, I think this will take a function you will need
to call to return the correct price.
--
Dave Hargis, Microsoft Access MVP

I just have it as text because I was not sure what to do with it.

What data type is the field PriceLevel in the customer table?
What value does it contain to identify a specific price level?

Okay, great.
I am about to leave for the day, but I will get back to you tomorrow with

[quoted text clipped - 9 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?

 

[shannaj via AccessMonster.com]

Would it make since to make a table of Price levels with the appropriate
levels indicated with the numbers 1-10 as the primary key, and then look them
up from my customers price level table? Or am I making it too complicated?

What text value identifes which price level?
It would make it easier if you used a numberic value so like 0 = standard
price, 1 = PriceLeve1, etc.

In either case, let me know, I think this will take a function you will need
to call to return the correct price.

I just have it as text because I was not sure what to do with it.

[quoted text clipped - 5 lines]

 

[Klatuu]

That would actually be the easy way, but the problem is how your pricelist
table is structured. To do that, it would need to be:
ItemID
PriceLevelID
ItemPrice

The you could join it in a query using the ItemID from your Item table and
the PriceLevelID from the Customer table.
--
Dave Hargis, Microsoft Access MVP

Would it make since to make a table of Price levels with the appropriate
levels indicated with the numbers 1-10 as the primary key, and then look them
up from my customers price level table? Or am I making it too complicated?

What text value identifes which price level?
It would make it easier if you used a numberic value so like 0 = standard
price, 1 = PriceLeve1, etc.

In either case, let me know, I think this will take a function you will need
to call to return the correct price.

I just have it as text because I was not sure what to do with it.

[quoted text clipped - 5 lines]

up the correct price for that customer's bracket and then be able to view the
other 9 levels within the combo box. Any recommendations?