Two Active Price Lists

[Meredith9053]

Is it possible to access one of two price lists in a database.

I.e. I have a local price and an agent price, and my customers either
receive the local or interstate price, never needing to access both price
list for one particular customer.

So i have generated a form (for the sake of invoicing); Selecting customer,
which in turn selects a discount percent off the price list. how do i have
it automatically to access either price list one or two.

At the moment for example i select customer name and an automatic discount
of 15% applies to the order, now i would like to tell the form to access
agent or local price list.

In my customer table i have already stipulated in a seperate column either
agent or local.

Hope this makes sense to someone.

Cheers Meredith

 

[Wayne-I-M]

Hi

This is just what access does best. You can "relate" data from one table
(your customers) to another table (either your local or intersate). To do
this you will need to have a linking field in both tables. Press F1 and
search Relationships.

I can't see your set up but the standard method would be to have a price
list containing details of items for sale. In this table would be the ID,
The ItemType, The Item, ItemCost, etc. This would allow you to select either
local or interstate ( thi would be from ItemType) when you apply a cost to a
certain customer. Of course if a customer always gets a certain rate you
could add this information to the customer's table (something like -
[Salestype] = Local or [Salestype] = Inter).

As I said I can't your application so it's up to you how you set this up.

But I think you have 2 tables in this case, not the best idea but it does
not cause any problems. One table would reduce the chance of erros and allow
better reporting for the administrator.

In your customer's table add a field called SalesType, format text.
On your form I would think you will have combo (cmbSalesType) giving ItemID,
ItemType, ItemName, ItemCost, etc

You need to set the source of the combo to show item from a certain table
(either Local or Interstate) depending on the contents of the SalesType field
in the customer's table. Something like this


If selecting items from one table with a field called ItemType which give
the Local or Interstate option

Private Sub Form_Current()
If Me.SaleType = "Local" Then
Me.cmbSalesType.RowSource = "SELECT ItemID, ItemType, ItemName,Itemost FROM
" & " TableName WHERE ItemType = "Local"
Else
Me.cmbSalesType.RowSource = "SELECT ItemID, ItemType, ItemName,Itemost FROM
" & " TableName WHERE ItemType = "Interstate"
End If
End Sub


Or - if selecting items from 2 tables ( tblLocal and tblInterstate)

Private Sub Form_Current()
If Me.SaleType = "Local" Then
Me.cmbSalesType.RowSource = "SELECT ItemID, ItemType, ItemName,Itemost FROM
" & " tblLocal
Else
Me.cmbSalesType.RowSource = "SELECT ItemID, ItemType, ItemName,Itemost FROM
" & " tblInterstate
End If
End Sub


Good luck

 

[Meredith9053]

Hi Wayne, thanks for quick response! Manchester hey! I used to go to school
in Colden Commen - near Winchester.... small world.

I see what you are saying that you cant see my applicaiton, so i explained
in brief at the end of this post, if it helps. if not, save yourself the
reading!

Anyway, I totally see what you are getting at with the two table option,
however i am happy to hear i can make it one table, think as you, it would
be tidier! At the moment it is in two, as i thought that would be the only
way, but can merge.

My problem is with each Local and Interstate list is seperate prices! IE
same products, description and all but the lists differ in price...... thats
where it got too sticky for me.

So do i understand that in my sub form (ie, order details) where i have the
product id, that is where i insert my enquiry to lookup if local use local
details? will it be too messy when it gets to the price as, at the moment
the price comes up automatically once product id is selected, then it applies
the discount to the order as ordered by the customers table which is specific
to that cusotmer. Will it be all too much?

If you need to understand the database i am building, this might help, read
if you want!LOL

Customer Table:
ID
Name
Address
Post Cost
Suburb
State
Discount Percent - Fixed per Customer, but varies for customer list. (ie 10%
one, 12% another etc)
Price Type - Local or Interstate - Never changes with customer once set

Products Table: Which would be in use, if merged to one table
ID
Name
Description
Local Price
Inter Price
Discountinued

Basically once the customer is entered we establish a price structure for
that client. be that Local price minus say 11%. This will never change.

Then when the product is ordered,a nd i do the invoice I would like to
select the customer and the name, address, account number, disc % everything
get filled out on my form - this is already working and up.

Then i have the body of the order with the list of goods purchased. So when
I get to the product to select, this is what i would like to see happen upon
the product selection; Description goes in, the correct price (ie local or
inter) MINUS the set order discount amount. then all i have to do for each
order is select product name, and type qty and the rest is history!

Cheers Mate,
Thanks again for your help.

 

[Wayne-I-M]

Hi

If it was me I would be tempted to run an append to merge the 2 product
tables you have at the moment. Do this ON A BACKUP of your database - just
in case.

Next
The tables look ok but you will need to add a new table (tblOrders)
In this table you will have
OrderID
CustomerID
ItemID
(Plus details of the items)

Plus such things as
OrderDate
OrderMadeBy
OrderDispacted
etc
etc

Run this through a query then base a form on this. In the query you will
work out the cost of the item (amount * discount) - don't store this
calculation just run it as and when you need it.

Creat a main form based on Customers
Create a sub form from tblOrder
Link these 2 form on the OderID

I may put this one a tab control on the main form (so you can very quickly
see all the orders made by the customers) and then a second tab that is
linked to the 1st tab (by OrderID). This will mean that you can all the
orders (continous) then select an Order which will take you to the 2nd tab
which will give details (products, costs, totals cost, etc)

It may take a while to set up but it will be worth it in the end as it will
make ordering much simpler.

You will run the invoices from another query based on tblOrders

 

[Wayne-I-M]

Sorry that was a mistake

You will "not" have (Plus details of the items) in table orders - just the
linking ID

Was thinking of something else - sorry