2 different Qty in order details in northwind

[Coco111 via AccessMonster.com]

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

 

[Jeff Boyce]

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]

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..

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

 

[Jeff Boyce]

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.

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..

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

 

[Monkey Butler]

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..

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.
[quoted text clipped - 19 lines]

Is there any easy way to do? I dont know anything about macros...thanks in
advance.....coco111

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.

 

[Coco111 via AccessMonster.com]

Thanks, Steve. Im a bit slow, but I try to visualize and try to follow your
step....coco111

Hi Jeff,

[quoted text clipped - 33 lines]
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.