Need to add another Field in Form already created...

[Stephanie53]

I am using Northwinds Trader template.

Here is the issue:

Under Product Details - the Table only has one field "List Price" - I want to be able to have the option of choosing between 2 prices (List Price A and List Price B)

It all depends on the customer on who gets what price and quanity that they may order.

what I would like that under Customers table put in who is getting that list price and have it generated to show when selecting that Customer for each item that they order.

I also want to be able to list the Customers that are Tax Exempt and have that generated on the Invoice.



TIA for all your help!

Stephanie

[rpeare]

Are the two list prices a percentage of one another.

For instance is list price B always (and I mean always) say 75% of list price A. If you have a consistent method of assigning a discount you wouldn't need to have two list prices in your database, you'd be able to calculate it.

If they are not related reliably you'd likely need a formula like

CustListPrice: iif(customertype = "A",
[listpriceA], iif(customertype = "B",
[ListpriceB], 0))

in your queries and base all your taxing/totals on this calculation.


as far as applying taxes go you can have a tax exempt yes/no field and if the value is -1 (yes) you can suppress all taxes in your calculations.

Tax: iif([taxexempt] = -1, 0, [custlistprice] * .15

for example.

[Stephanie53]

Ok I think I know what you mean...lets do more in laymans terms and see if I got this right:

I have a Customer Table (in Excel) that has a field that is called List Price to which in the column listed next to each customer says either A or B (of course there is not a field either for this in this template so it wasnt brought over when I imported it from Excel)
I have a Inventory Table that has 2 Fields - List Price A and List Price B (have yet to inport this to Access because of these Fields are not listed in the templates)

Now I need to get these Fields into Access too and I can create these fields in Access Table but my issue is this...

Not every product is the same in percentage off...so I will need put in the following code..

Now I understand the code:

CustListPrice: iif(customertype = "A",

[listpriceA], iif(customertype = "B",

[ListpriceB], 0))

Do I put that where? Under what type of event? and can it be done for that one Field List Price

Again I am using a form that is part of the Northwinds Template.

See attached if you can to see where I can add this in

[June7]

Sorry, 2007 doesn't recognize that db format. I don't think rpeare can view 2010 files. Calculated fields in tables and data macros (and maybe some other stuff) can make the file incompatible with 2007. I might be able to look at it with 2010 in a few days if you still have issue.

I think rpeare is suggesting you put that expression in a query to create field with that calculation.

[rpeare]

Here's an example let's say you have the following tables:

tblCustomerType
TypeID  TypeDesc
1       A
2       B

tblCustomers
CustomerID  CustomerName TypeID ----> other customer related fields
1           Acompany Inc  1
2           Bcompany Mfg  2

tblParts
PartID  ListA  ListB
1       10.00  8.00
2       20.00  17.00
3       5.00   3.50

Now let's say you have a table for your orders and line items:

tblOrders
OrderID  CustomerID  -----> other order related data
1        2
2        2
3        1

tblOrderDetail
DetailID  OrderID  PartID Qty
1        1        1       2
2        1        2       5
3        1        3       10

When you are creating your invoice in a query you can link your DETAIL to your ORDER to your CUSTOMER to your CUSTOMER TYPE table

then have this formula in your query:

LineItemCost: iif(customertype = 1, ListA, ListB) * qty

So, on your first line item you have 2 items of part number 1, when you link back to the orders, then the customers (in this case I used the PK but it might be easier if you backtrack it all the way to the label which means adding the customer type table) you'll see that they are listed as a TYPE A customer so the query should take the value of the LISTA column and multiply it by your quantity to get 20$ total cost.

[Stephanie53]

relationship errors.zip

I am getting these errors when creating the relationship between Customers and CustomerType as stated above. is it because of the integrity?

TIA!

[rpeare]

I can't read access 2010 files. you'll have to convert it to something prior if you want me to look at it.

[Stephanie53]

that attachment is not access file...its screen shots of the errors i am getting

[rpeare]

I can't tell from that if you've linked the tables correctly.

is the ID field in your CUSTOMERS table the CUSTOMER unique identifier? if so then you've done it wrong.

The customer TYPE should be a field within your CUSTOMERS table but not the primary key (see my example)

[Stephanie53]

Ok in my CUSTOMERS table this is what i have as my fields:

ID

Company

Last Name

First Name

E-mail Address

Job Title

Business Phone

Home Phone

Mobile Phone

Fax Number

Address

City

State/Province

ZIP/Postal Code

Country/Region

Web Page

Notes

Attachments

Tax Exempt

TypeID

Now in my CustomerType I have the following fields in it

TypeID TypeDesc

(I am waiting on another department to get me ALL their products (parts) in detail to me. So basically I cant continue until I have the rest of this info is what your saying...correct?

[Stephanie53]

if your still not sure can you advise who here has Access2010 to help me?

[rpeare]

look at your references, you're linking ID in the CUSTOMER field to the TYPEID (I assume) in the CUSTOMERTYPE table. If you set your tables up the same way I did then you're linking the wrong fields together

TYPEID to TYPEID in the two different tables.

[Stephanie53]

ok I have done that but my question was when I was getting those errors was it because I selected reinforced integrity?

But my main question is this....

ON THE FORM:

There is only ONE box listed there as LIST PRICE

Now that I have 2 seperate prices for different customers is there a code that goes JUST INTO THAT FORM in that BOX or in properties to refer to these relationships?

and if so..WHERE please do I put this code? In the properties in the query? or properties of the Form? Because I dont see LineItemCost in the Form Properties

[rpeare]

If you were trying to use the table other than the relationship (one to many) you set up, for instance going from the MANY side to the ONE side it may have caused the error. That's really part of the reason I never set up relationships at all, to me it causes more problems than it solves.


I have just thought of something regarding your database. Do customers change their list price or once they're a customer they are always the same list price? It would make a huge difference how you were storing your data if they can change on and off lista or listb prices. I'd like to know the answer to that beofre I answer the second part.

[Stephanie53]

our customers do not change our price list. it all depends on the customer if they are a distributor, supplier or end user. The distrubutor and Supplier gets the ListPriceA whereas the Enduser gets the ListPrice B.