Multiple Price query based on customer type

[Don]

Hello,
I have a customers table that can have a sellprice level of 1 thru 5.
My products table has these five price levels stored.
What i need to do is at the order entry form call the default pricing for
that customers' pricing level.
eg: Customer A has a pricing level of 1, so the order details table will
call price level 1 from the products table. Customer B has price level 3 so
the order details table will call price level 3 from the products table.

How would I go about this?

 

[Al Campagna]

Don,
A typical order entry system usually involves the Customer detailks on
the main form, and a continuous subform of ordered items.
Each customer should be assigned a PriceLevel.
Also typical is a combo box in the subform to allow you to select an item
purchased. That combo could use the PriceLevel value on the main form to
filter it's results.
A combo RowSource filter like...
PriceLevel = Forms!frmYourOrderMain!PriceLevel
should only show the appropriate pricing for the customer.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[aaron.kempf]

If you are doing anything that is this complex; then you should just
move to SQL Server.

Do you want to do it twice or do it NICE?

Access doesn't even support SCD. What a piece of crap database.

Honestly- you'll be much better off if you can just use PARAMETERS.
Store your 5 different prices in a different table.

-Aaron

 

[Don]

Al, thanks for the reply. What you've described is exactly what I have already.
On the OrderDetails subform the ProductID looks up the ID, part number and
description and the AfterUpdate event fills these values to the OrderDetails
table. (see Northwind Sample).
My Products table has all five price levels in place. That is, Part No,
description, Bin location, PL1,PL2 etc, etc. If I use your suggestion this
won't filter the price levels to the individual customer. If I use the
filtered combobox, I can only return products with a price of $5 for
pricelevel5, a price of $3 for pricelevel 3 etc.
Have I got the table design wrong? I've kept the price levels in the
products table for ease of future updates.
I think you understand what I'm trying to achieve but didn't realise the
structure of my table.
I look forward to your thoughts........

 

[Al Campagna]

Don,
I'm afraid I'm a bit confoosed...
Would you show me some data sample data from the Customer table, and your
items table. (just the important ones, involved in this particular "logic")

I'm thinking that perhaps you have the items table like this...

PartID PartDesc Price1 Price2 Price3 Price 3
1324 Ratchet 1.00 1.20 1.30 1.45 .... etc

If so, I can think of a couple of ways we can attack that...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Al, what you've described, along with other pertinent info, is exactly how
the products table is designed (i have a part number field as well as ID to
allow for various suppliers using the same part number for different
parts-not common but does happen)
The customers table has obvious fields: name, address, phone, etc and a
default price level of 1 thru 5.
The Orders main form/table has a defaultpricelevel field derived from the
CustomerID columns afterpdate. The OrderDetails subform/table has the line
item details dreived from the productid AfterUpdate.
eg: Part No; description; bin location(info only-not stored) qtyord; price;
tax; extended. The price needs to reflect the pricing level as discussed. in
other words, i need to call from 5 fields into one dependant on that
defaultpricelevel.

clearer?

 

[Al Campagna]

OK Don,
Try this... for example only...adjust your combo columns to suit your
needs.

A cboItemID combobox with these columns...
ItemID ItemDesc P1 P2 P3 P4 P5

Assuming a PriceLevel 3 customer's order...
Using the AfterUpdate event of cboItemID...

YourPriceField = cboItemID.Column(1 + Forms!frmMain!PriceLevel)

will always select the right price column for that customer.
(combos are numbered 0,1,2,3,4, etc)
Didn't have time to test completely, but I did test a
= cboXYZ.Column(3 + 4)
and that selected the right combo column. So "math" in the column variable
is OK.
And, if you ever add PriceLevels, this method allows for easy inclusion.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Thanks Al,will give it a go tonight....

 

[Al Campagna]

Let me know how you made out.

Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Al, column variable works a treat. Well Done! Now if i can only work out how
to assign a new invoice number to each and every sales order. That is, a
customer orders 5 widgets, I supply 3 immediately and backorder 2. The 2 come
in at a later date so i call up the original order via a backorder qry and
sell the remaining 2 but I need to use a new invoice number for tax/reporting
purposes.
Any thoughts?

 

[Al Campagna]

Don,
I think I'll need a bit more understanding of what the tax law wants to
see, and why..
Perhaps an example of what you want to see in the Order form subform
oredered items.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Al, the invoice number will only reflect that order at that time. eg: An
order is placed on Monday by customer A (say OrderID=20) for 5 widgets and
they want some urgently. I send 3 only on that day and an invoice number is
assigned to that order then (say 12345). On the following Friday I receive
into stock some more widgets of which customer A wants 2 from his backorder.
I call his original order (OrderID=20) from the backorders qry (showing whats
on backorder only) and send the remaining 2. I need to assing a new invoice
number (say 12514) to this second issue of this order.
In other words, each individual instance of a sale will have its own invoice
number regardless of the orderID.
Does this make sense?

 

[Al Campagna]

Don,
OK, from what I gather... this is what you need to see on your OrderNo
20?
OrdNO InvNo ItemID Qty Shipped
20 12345 ABC111 3 1/1/08
20 12457 ABC111 2 1/6/08

But, what would a multiple backorder look like?
Order 20 = ABC111= Qty 5
ABC222 = Qty 6
ABC333 = Qty 8
OrdNO InvNo ItemID Qty Shipped
20 12345 ABC111 3 1/1/08
20 12345 ABC222 5 1/1/08
20 12345 ABC333 6 1/1/08
20 12543 ABC111 2 1/8/08
20 12543 ABC222 1 1/8/08
20 12678 ABC333 2 1/15/08
Notice that on the Original order, all items have the same InvoiceNo.
Then the first Backorder shipped... those items all have the same
InvoiceNo
The last Backorder shipped has it's own InvoiceNo
**Is that the way you would like to see them?
-------------------
Let me just say what I would do, and perhaps that might be helpful.
I would give each Item Entry line it's own unique number
(autonumber), but... I would multiple ships by their shipping "batch".
Original Order...
Ord Ship Ship
OrdNO InvNo ItemID Qty Qty Date Bal
20 12345 ABC111 5 3 1/1/08 2
20 12346 ABC222 6 5 1/1/08 1
20 12347 ABC333 8 6 1/1/08 2

After first backorder ship...
20 12345 ABC111 5 3 1/1/08 0
20 12346 ABC222 6 5 1/1/08 0
20 12347 ABC333 8 6 1/1/08 2
20 12543 ABC111 2 1/8/08 0
20 12544 ABC222 1 1/8/08 0
After second backorder ship...
20 12345 ABC111 5 3 1/1/08 0
20 12346 ABC222 6 5 1/1/08 0
20 12347 ABC333 8 6 1/1/08 0
20 12543 ABC111 2 1/8/08 0
20 12544 ABC222 1 1/8/08 0
20 13765 ABC333 2 2/1/08 0
---- ---- ----
19 19 0
Using this setup, a "backorder" would include any OrderNo
with an Balance > 0
I calculate Balance with a DSum using =OrderNo and = ItemNo
In my example I use ShipDate to "batch" (group) the shipments,
but even better might be some ShipNumber, or UPS number,
or whatever you use to identify a "batch" of items shipped against
an order.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Al, thats pretty much what I'm looking for. The "shipment" you're alluding to
is the invoice number. Your multiple lines order sample is spot on too.
I remember reading a post about this same thing last year but do you think I
can find it? - -No....!
It did talk about the Orderdetails table having a one-to-many relationship
to the orders table which has a one-to-many relationship with the invoices
table. I think I've nutted out a solution along these lines. At the time of
printing the invoice, the report will open an invoices form (as hidden, or
maybe as normal so I can add/select comments to place on the invoice; a la
the service call management template) that will generate an invoice number
and invoice date in its BeforeUpdate event and use this info on the printed
invoice. I'll then use an append qry to dump the invoiceid, orderid and
details to an archive table for future enquiries.

Thanks again for your input. Will let you know how I go.....

 

[Al Campagna]

Don,
Well, this is not so much a matter of difficulty, as it is understanding
a lot of details about your setup.
The fact that you have an Invoice table separate from for OrderItems, has
quit an effect on the best solution.
And... I may have missed that fact...
And that setup should be fine.

Let me add one more shot...

Say you had an order that had multiple backordered items.
You now have 3 unique items ready to ship against the order.
We could have an autonumber field called InvoiceLineNo that assigns eack
Invoice record a unique key value.
Another field called InvoiceNo would be LongInteger.

When you enter the first item, double clicking InvoiceNo would cause
InvoiceNo to take on the value of the current InvoiceLineNo, AND make that
value the DefaultValue for the InvoiceNo field.
Now, every item you enter another item in this "batch" they will all
have the same InvoiceNo.

Two weeks later, with more items to ship, double click the InvoiceNo for
a new DefaultValue, and enter multiple items against that new value.
OrdNO InvLineNo ItemID InvNo
20 12345 ABC111 12345 <GetInvLineNo
20 12346 ABC222 12345
20 12347 ABC333 12345
20 12588 ABC111 12588 <GetInvLineNo
20 12589 ABC333 12588

Yes, I'd like to know how you make out.
Good Luck
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Thanks for that Al, will adopt those suggestions and let you know what happens.
In this same db, I have a tblProducts and a tblSupplierProducts. The
Products are the parts that I sell (FYI appliance spare parts, washers,
fridges etc). The supplierparts table is my suppliers
versions/alternatives/minqty for my products.
eg:
PartNumber SupplierPartNumber Min Price Avail?
Preff
C100 C100 1 13.50
Yes Y(chkbox)
C100 C100 10 10.00
Yes
C100 0579200003 0 0
No
C100 27568403 0 0
No
C100 SG001 1 13.75
Yes

What I'd really like to achieve is at the OrderDetails and
PurchaseOrderDetails subforms is enter ANY of those supplier part numbers
(via combo or textbox) and return my part number as well the current
purchasing part number (preff=Y). for example enter 27568403 and return the
C100 and AfterUpdate the description,price etc..

Thoughts?...

 

[Al Campagna]

Don,
Here again, describing the problem is more difficult than solving the
problem.
At first blush though, I'd say your table design is more of a problem
than your posted question.

From your data, it appears as though neither the SupplierPartNos, or
your PartNumbers are unique.
The first 2 LineItems all share the same SupplierPartNo and PartNo.
This would be a violation of
the realtionship between the two. So... using one part number to relate to
the other is, in this case, unworkable.
Each PartNo (yours and thiers) must be unique. The supplier should have
no duplicate PNs, and neither should yours.
And one table must contain the relating value of the other.

**So let's discuss your table setup first. Then we can solve your
"selection" question.

ONE table (preferred) Example...(just the essential fields here, yours can
also contain other fields like Min/Available..ets))
tblProducts
-----------
SupplierPN SupplierPrice OurPN OurPrice
12345 1.25 15243 3.20
716242-2 2.00 7142-2D 4.60
83425-C 10.00 AB617 16.80
etc...
-or-
TWO tables
tblSupplierPNs
SupplierPN OurPN SupplierPrice ... other Supplier fields a/r
12345 15243 1.25
716242-2 7142-2D 2.00
83425-C AB617 10.00
|
Related by OurPN
|
tblOurPNs
OurPN OurPrice ... other OurPN fields a/r
15243 3.20
7142-2D 4.60
AB617 16.80
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Don]

Details, details...can't you read my mind Al?.....;-)
I am using PK autonumber ID fields in both tables to solve the problem of
duplicate part numbers (as per your two table example).
Not only do some suppliers share the same part number for a particular part,
but there is also the case where the same part number could used for totally
different parts. eg: SupplierA p/n 50488 is a stove switch; whilst SupplierB
p/n 50488 is a drive belt. In this instance I have renamed my version of this
part S50488 stove switch to avoid any confusion. What i want to achieve is
when the user enters 50488 at the orders screen, they will be presented with
the two choices and goes from there.
I have my PartID in the supplierparts table and have related them to each
other so my Products form has my p/n details and the associated SupplierParts
subform has its various supplier parts. (or in tableview, I click the little
cross and can see the related supplier parts.)
Currently I have fields in my tblproducts that reflect the current supplier
and that suppliers part number so when and if I update the part
crossreference with new supplier data that info is updated (via updateqry).
eg: my p/n C100 current supplier is SupplierA and their p/n is C100 (min qty
10). If I only have one supplier then the updateqry uses yes or no as the
Preff? criteria. This works well and I use this on the purchasing side of the
ledger, but on the sales side I need to be able to choose from lots of
alternatives to get to my p/n.....eg: user enters 27568403 and it returns my
p/n C100....
Does all this make sense?

 

[Al Campagna]

Don,
You should be able to use a combo box to select a legitimate OurPartNo.
After that selection, a 2nd combo cboSupplierPN, would use the OurPartNo
as a filter against the query behind it... to only display PNs associated
with that OutPartNo.
Place whatever associated fields you want to glean from
cboSupplierPartNo (Pref?, Price?. whatever) in additional colums of the cbo,
and on the AfterUpdate event, manually transfer those other field values to
bound field s on your form. As we discussed in an earlier post.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."