2 different Qty in order details in northwind
in [Tables]
|
From: Coco111 via AccessMonster.com on 12 Nov 2009 04:57 Hi, im pretty new with access 2007, i decide to use northwind template. In order detail both form, i have 2 different Qty, the original Qty is the Qty that will link to Inventory and I add another field Qty1 for invoice purpose. For example, I got 1 roll of tape, 1 roll will be cut into 10 pcs. We need to bill according to customer request qty 10, but actual qty sold is only 1. Anyone come across this issue. How to modify it w/o effect the function? table/form as below:- ID ORDER ID PRODUCT ID QUANTITY (FOR INVENTORY) QTY1 (FOR INVOICE) UNIT PRICE AND SO ON Is there any easy way to do? I dont know anything about macros...thanks in advance.....coco111 -- Message posted via http://www.accessmonster.com
From: Jeff Boyce on 12 Nov 2009 07:58 Will every item in QTY be "cut into 10 pcs"? How do you (and how does Access) know the relationship between the QTY (as-received/Inventory) and the QTY1 (as-sold/Invoice)? Without that information, it will be tough to set up a macro or any other procedure to do this. -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Coco111 via AccessMonster.com" <u56083(a)uwe> wrote in message news:9eff8d00db622(a)uwe... > Hi, > > im pretty new with access 2007, i decide to use northwind template. > In order detail both form, i have 2 different Qty, the original Qty is the > Qty that will link to Inventory and I add another field Qty1 for invoice > purpose. For example, I got 1 roll of tape, 1 roll will be cut into 10 > pcs. > We need to bill according to customer request qty 10, but actual qty sold > is > only 1. Anyone come across this issue. How to modify it w/o effect the > function? > > table/form as below:- > ID > ORDER ID > PRODUCT ID > QUANTITY (FOR INVENTORY) > QTY1 (FOR INVOICE) > UNIT PRICE > AND SO ON > > Is there any easy way to do? I dont know anything about macros...thanks in > advance.....coco111 > > -- > Message posted via http://www.accessmonster.com >
From: Coco111 via AccessMonster.com on 12 Nov 2009 20:01 Hi Jeff, The cutting q'ty is not fix, always change sometimes 10 pc, sometimes 20 pc, etc.. 10 is just example... sometimes 1 rolls, cut into 100 pcs.... The relationship is as original as northwind template, I did not change any link in relationship. As I see, there is 3 relationship which I can see as below:- In order details table Product ID link Product ID in Product table Order ID link to Order ID in Order table Status ID link to Status ID in Order status table. I did not see any link in Qty. How to check or see? Everything as original in Northwind template, did not change anything. Just add some extra field like size and etc. Thank you.. Jeff Boyce wrote: >Will every item in QTY be "cut into 10 pcs"? > >How do you (and how does Access) know the relationship between the QTY >(as-received/Inventory) and the QTY1 (as-sold/Invoice)? > >Without that information, it will be tough to set up a macro or any other >procedure to do this. > >> Hi, >> >[quoted text clipped - 19 lines] >> Is there any easy way to do? I dont know anything about macros...thanks in >> advance.....coco111 -- Message posted via http://www.accessmonster.com
From: Jeff Boyce on 12 Nov 2009 21:04 But the original Northwind doesn't do that. You'll have to tell Access how to do what you're describing. You'll have to tell Access about the new fields. Sometimes taking an existing application as a template and just changing a few things doesn't actually get you any closer to what you want. Sometimes you need to just dig into HOW its doing things, and work out your own ways to get the new tasks done. Good luck! -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Coco111 via AccessMonster.com" <u56083(a)uwe> wrote in message news:9f07714027f8c(a)uwe... > Hi Jeff, > > The cutting q'ty is not fix, always change sometimes 10 pc, sometimes 20 > pc, > etc.. 10 is just example... sometimes 1 rolls, cut into 100 pcs.... > > The relationship is as original as northwind template, I did not change > any > link in relationship. > > As I see, there is 3 relationship which I can see as below:- > > In order details table > Product ID link Product ID in Product table > Order ID link to Order ID in Order table > Status ID link to Status ID in Order status table. > > I did not see any link in Qty. How to check or see? > Everything as original in Northwind template, did not change anything. > Just > add some extra field like size and etc. > > Thank you.. > > Jeff Boyce wrote: >>Will every item in QTY be "cut into 10 pcs"? >> >>How do you (and how does Access) know the relationship between the QTY >>(as-received/Inventory) and the QTY1 (as-sold/Invoice)? >> >>Without that information, it will be tough to set up a macro or any other >>procedure to do this. >> >>> Hi, >>> >>[quoted text clipped - 19 lines] >>> Is there any easy way to do? I dont know anything about macros...thanks >>> in >>> advance.....coco111 > > -- > Message posted via http://www.accessmonster.com >
From: Monkey Butler on 13 Nov 2009 12:17 On Nov 12, 8:01 pm, "Coco111 via AccessMonster.com" <u56083(a)uwe> wrote: > Hi Jeff, > > The cutting q'ty is not fix, always change sometimes 10 pc, sometimes 20 pc, > etc.. 10 is just example... sometimes 1 rolls, cut into 100 pcs.... > > The relationship is as original as northwind template, I did not change any > link in relationship. > > As I see, there is 3 relationship which I can see as below:- > > In order details table > Product ID link Product ID in Product table > Order ID link to Order ID in Order table > Status ID link to Status ID in Order status table. > > I did not see any link in Qty. How to check or see? > Everything as original in Northwind template, did not change anything. Just > add some extra field like size and etc. > > Thank you.. > > Jeff Boyce wrote: > >Will every item in QTY be "cut into 10 pcs"? > > >How do you (and how does Access) know the relationship between the QTY > >(as-received/Inventory) and the QTY1 (as-sold/Invoice)? > > >Without that information, it will be tough to set up a macro or any other > >procedure to do this. > > >> Hi, > > >[quoted text clipped - 19 lines] > >> Is there any easy way to do? I dont know anything about macros...thanks in > >> advance.....coco111 > > -- > Message posted viahttp://www.accessmonster.com I deal with this as a routine part of our business. We pull hose off a large reel and add end fittings and sell it as an assembly, or pull a length of saw stock off of a roll and weld it into a band saw blade. You need to set up a unit of measure table tblItemUM ItemID UM UnitSize BaseUM ---------------------------------------------------- Hose1 IN 1 Y Hose1 FT 12 N Hose1 RL 250 N All inventory is controlled using the base unit but different transaction are processed using other UM's. For instance on hose the manufacturer wants us to order by the roll which is approximately 250 feet long. But when we receive a roll it may be 256 feet long so the form for the receiving transaction displays feet which is entered as 256 but when the RECEIVE command button is clicked we update the inventory using code as 256*[UnitSize]) or (256*12) = 3072 in. To sell the item by the piece which could be any length we do the following. First set up an inventory item call something like "Hose1/ Cut to Length". Set up a table that in essense a bill of materials for your cut to lenght pieces. tblBOM ItemID ComponentItemId QtyPerItem ComponentUM --------------------------------------------------------------------------------------------------------- Hose/Cut to Length Hose1 Null IN GardenHoseCTL Hose1 Null FT GardenHoseCTL CutCharge 1 EA GardenHoseCTL BrassEndFitting 2 EA GardenHoseCTL SprayerNozzle 1 EA You never purchase or stock the item ABC1/Cut to Length. Say a customer wants 2 pieces 12'-6" long. You enter an oder for 2 EA ABC1/ Cut to Lenght and design you system so that it automatically adds the item ABC1 as a seperate line as a component. The quantity per item is blank (null) so you enter 150 in. on the order. The price per each cut to length piece is a roll up of 150*price per inch. This method is very flexible in that it allows you do things like sell a made to order hose assembly. When someone adds a GarenHoseCTL to an order 4 components are automatically added, the hose itself, a cutting charge, 2 pieces of an end fitting, and 1 Sprayer Nozzle. Our BOM knows the each hose has two ends but that only one nox=zzle is required, and we get to add a CutCharge for our labor. Once again the hose quantity is blank and is filled in at order entry time. this way you can use the single BOM to create any number of different length hoses that you need. HTH Steve P.
|
Next
|
Last
Pages: 1 2 Prev: command button not opening related fields Next: Design with no experience |