2 different Qty in order details in northwind

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

0
Coco111
11/12/2009 9:57:51 AM
access.tablesdbdesign 510 articles. 0 followers. Follow

5 Replies
1208 Views

Similar Articles

[PageSpeed] 36

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@uwe> wrote in message 
news:9eff8d00db622@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
> 


0
Jeff
11/12/2009 12:58:50 PM
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

0
Coco111
11/13/2009 1:01:43 AM
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@uwe> wrote in message 
news:9f07714027f8c@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
> 


0
Jeff
11/13/2009 2:04:41 AM
On Nov 12, 8:01=A0pm, "Coco111 via AccessMonster.com" <u56083@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 a=
ny
> 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. =A0How to check or see?
> Everything as original in Northwind template, did not change anything. Ju=
st
> 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 othe=
r
> >procedure to do this.
>
> >> Hi,
>
> >[quoted text clipped - 19 lines]
> >> Is there any easy way to do? I dont know anything about macros...thank=
s 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) =3D 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=3Dzzle 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.
0
Monkey
11/13/2009 5:17:49 PM
Thanks, Steve. Im a bit slow, but I try to visualize and try to follow your
step....coco111

Monkey Butler wrote:
>On Nov 12, 8:01 pm, "Coco111 via AccessMonster.com" <u56083@uwe>
>wrote:
>> Hi Jeff,
>>
>[quoted text clipped - 33 lines]
>> --
>> 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.

-- 
Message posted via http://www.accessmonster.com

0
Coco111
11/14/2009 4:24:09 PM
Reply:

Similar Artilces:

Viewing Distribution Groups in a different domain
I opened a user account in one domain, using Active Directory, connected to the Global Catalog domain controller of that domain. UIder the Member Of tab, it only lists the distribution groups that the user is a member of in the current domain, and not the lists that are in a different domain. I am using WXP Pro SP2. When using W2K, and I try this, I can view those distribution groups. Any reason as to why I am unable to view them using WinXp? ...

Spacing #2
Hi Trim removes spacing in the beginning and end of word. How can i delete a spacing betwee two words (name) ex "De Wit" > i want "dewit" THX Luc You can use: =substitute(a1," ","") to remove all the spaces (leading/trailing/embedded) or =lower(substitute(a1," ","")) if you really wanted lower case. Luc Vandenhoeck wrote: > > Hi > Trim removes spacing in the beginning and end of word. > How can i delete a spacing betwee two words (name) ex "De Wit" > i want > "dewit" > THX Luc -- ...

One column with different widths
I want to make Column A with rows 1-29 one width and rows 30 and above another width. -- Calpitor Excel does not allow varying widths of cells within a column (or heights within rows). You could used 'Merged' cells to accomodate the wider column needs. HTH -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101 View this thread: http://www.excelforum.com/showthread.php?threadid=476150 ...

select case to replace text with different text
I'm trying to use a Select Case in a Private Sub Worksheet_Change event to do the following: if I type w in a cell in col B, I want to replace it with WIDGETS if I type g in a cell in col B, I want to replace it with GIDGETS seems like it should be simple but I can't come up with the code. On Sun, 10 Jan 2010 10:28:54 -0600, "John" <nodak457@yahoo.com> wrote: >I'm trying to use a Select Case in a Private Sub Worksheet_Change event to >do the following: > >if I type w in a cell in col B, I want to replace it with WIDGETS >if ...

Limitations to # of Decimal places for seconds (time)? #2
Bernie Deitrick Wrote: > Xprezons, > > Are you really recording every 1/10 millionth of a second? And, yes > Excel > limits you to three decimal places on the seconds > > Thanks a ton for your help, Bernie. I guess this is what I have to do. And yes, the system does log time to the accuracy of 6 decimal digits. Thanks and Regards, Xprezons -- Xprezon ----------------------------------------------------------------------- Xprezons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505 View this thread: http://www.excelforum.com/showthread....

Excel VBA
Hi again, I have enclosed a timesheet which i want to break down the day activities by a percentage, so if my sheet says an activity took a hour then the day column would tell you percentage of a seven hou day. Also i want the date column date to change each day. Currently it work for one day but then the next day it changes all the above cells to th current date. I have attached the file Cheers Stev Attachment filename: book2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56396 -- Message posted from http://www.ExcelForum.com ...

Public Folder Auto-reply #2
Hi Is there any way that I can enable an auto-reply for emails that are going to a Public Folder that is mail enabled. The emails will not only becoming from inside th company but externally as well. Thanx for the help Sulaiman yes, you can...on the Administration tab, click Folder Assistant...but be very cautious with this...it's very easy to get into a "mail loop" condition that could conceivably crash the store... -- Susan Conkey [MVP] "Sulaiman" <Sulaiman@discussions.microsoft.com> wrote in message news:9E3D127F-2CBE-40D3-9AC3-333F3D25B465@microsoft....

Sorting rows out of order after all rows are numbered in sequence
Data is in 4 columns with Headers described below: Col A - Line# (rows are numbered in original order in worksheet) Col B - Acct# (property number) Col C - Category (code for the type of information in the rows ie: E, V, X, T) Col D - Label (description of the data or values in the row) There is one row of Expenses (all begin with "LOE - ") that is numbered out of sequence from the other "LOE -" items. I need to Move it up with the other "LOE -" items and if possible Re-sort all the "LOE -" items BUT only the "LOE -" rows within e...

outlook mobile access broken #2
I noticed this morning that my OMA was give ASP application error reports when a device tried to connect. I put it down to changing the installed .net framework at the end of last week - using the aspnet_regiis.exe command located in the C: \WINDOWS\Microsoft.NET\Framework directory, I changed from v1.1.4322 to v2.0.50727. So I reverted back to v1.1.4322 hoping it would resolve the issue, no all I get is a 404 page not found - even though there are files there - now I'm a bit stumped. What is going to be the easiest way to fix/repair/recreate this virtual directory? Any pointers would ...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

Creating New Mailboxes #2
Hi, ANYONE KNOW WHY AFTER CLICKING CREATE MAILBOX FROM THE EXCHANGE TASKS ON A USER IT TAKES UPWARDS OF 1-2 HOURS BEFORE THE MAILBOX APPEARS? ADVICE/INFO GREATLY APPRECIATED. THANKS PHIL MATHER ZETEX PLC IT SUPPORT Before the mailbox appears where, exactly? In the GAL? In ESM? -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "PHIL MATHER" <PHIL MATHER@discussions.microsoft.com> wrote in message news:577D27BC-B370-4501-A09D-65151B5E901F@microsoft.com... > Hi, > ANYONE KNOW WHY AFTER CLICKING CREATE MAILBOX FRO...

Macro for checkbox (2) #2
Hi Still does not work new message is Sub or function not defined. I normaly just record macros so this is a bid more of a chalange Sub Macro1() ' ' If CheckBoxes(46).Value = True Then Range("O54").FormulaR1C1 = "50000" Range("T54").FormulaR1C1 = "4000" Range("Y54").FormulaR1C1 = "4000" Range("AD54").FormulaR1C1 = "4000" Range("AI54").FormulaR1C1 = "4000" Range("AN54").FormulaR1C1 = "4000" Range("AS54").FormulaR1C1 = "...

PatchFactory 2.0 Released. Professional and easy-to-use patch building tool for Win9x/Me/NT/2000/XP.
Description: Professional and easy-to-use patch building environment that can help you to create instant patch packages for software and file updating. Generated patch packages are small size self-extracting executable update programs in a famous installer style with adjustable user-friendly interface and multilingual support. Enhanced with features like easy-to-use interface including a Wizard mode, powerful patch engine, integrated compression technology, adjustable multilingual user-friendly interface of the update program, this program will most definitely become a valuable asset for softw...

accounting (different) State holidays
This is a call to the quintessence - the fifth and highest source o intelegence known to mankind (the WWW) I work with performance reporting, the performance of an activity i calculated by comparing the actual (number of days) with the agree days for the activity I use NETWORKING DAYS and can get the result. But now I am going NATIONAL and the holidays in each state i different, so I am not able to use the NETWORKING DAYS (with holidays to meet my requirement Can you please assist? Thanks and regards, Ra -- Message posted from http://www.ExcelForum.com Hi Raj Could you use an IF or ...

RMS object to store some details
I am configuring Microsoft RMS and I am integrating with another product. The integration went fine and I am able to get the response from the other product in to the RMS through a DLL (the hook)... But I want to store inside some RMS object.. Does RMS provide any object to store the data I want? Session.Variable(index) If you want to print your saved values to the receipt, use "Session.Variable1" through "Session.Variable10" Those variables will not persist to the database - they only exist within the POS Session. If you want to connect the values to the transaction...

beginning balances #2
Hello: This is a new client who is entering invoices and data as of 2006. There are two open fiscal years--2005 and 2006. We created 2005, as the client will need to enter beginning balances for 2006. I just want to make sure that I have the correct steps down. In order to enter beginning balances for 2006, they need to: (1) In GL Transaction Entry, enter a debit or credit to the balance sheet account and a debit or credit to Retained Earnings (or a suspense account) as of 12/31/2005 and (2) They need to close 2005 in the GL, so those beginning balances (BBFs) will roll forward to...

Variant #2
Good Day, I have a VB6 Com object that I am attempting to use in VC++. One of the methods returns a VB6 Variant datatype. When I look at the IDL for the object that value is also defined as a VARIANT*. The problem I have is I am not sure how to deal with this value on the C++ side. What datatype do I use to store this value. Unfortunately, I can really be almost anything. Do I need to store it as a byte array? Any help would be appreciated. Thanks! >I have a VB6 Com object that I am attempting to use in VC++. One of the >methods returns a VB6 Variant datatype. When I look at the...

FRx Forecaster #2
Hello, In a Calc Set when a range of Accounts is used the Calc set does not work (Calc = '1000..5000') Have followed all the TK and it does satisfy all the requirements for a Calc Set to work. Any suggestions would be highly appreciated. Thanks in advance Ajay Ajay-- is the variable on the left side a valid Account? (i.e., does 'Calc' exist in the list of accounts, and in the lineset? Also, check the syntax in the actual calculation. It should read Calc='1000..'5000 with the single quote in front of the variables. "Ajay" wrote: > Hello, &...

Help with combining 2 seperate Worksheet Change Event scripts
Hi, I have 2 seperate Worksheet Change Event scripts that I would like to combine to use in one woeksheet and I'm sure how to do. I am kind of new to VB. The first script is for hiding columns based on a value selected in picklist. Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAid Software If Target.Address <> Range("L2").Address Then Exit Sub 'MsgBox Month(Target) If Target = "All" Then Range("N:BV").EntireColumn.Hidden = False Else lastcol = Cells(6, Columns.Count).End(xlToLe...

Report WRITER legends values #2
Hello. I need to modify existing modified report. Where can I find values for legends fields? Thanks. Sofiya. Hello Sofiya, Legend fields are placeholders that may contain pretty much anything, depending of the report you are working with. For example, on many reports, they are used to display the range values used when generating the report. (From-To) One thing you must remember though is that they are always typed as strings so you need to convert them if you want to do any arithmetic operations with them. In conclusion, if you want to evaluate the legend field values for a report,...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...

Project Green #2
Those of you counting on or worrying about Project Green might want to check out the rants and replies on this forum on the Sept 20 subject entitled ".Net Plans?". ...

Same .pst Files Looks Different on other PC
Hello, I have a weird problem. I have a small network at home with 1 desktop and 1 (wireless) laptop. I have installed MS Office XP on both computer including all the servicepacks and updates (both Outlook version numbers 10.4712.4219 SP-2). I share my document folder which is on the desktop, so I can access it through the laptop. When I open the .pst file on my laptop, Outlook handles the full-day appointments differently. It moves them up one hour, i.e. makes the appointments start at 23.00 hours the night before (and then they last till 23.00 hours on the right day). When I double-click th...

Need Help #2
I have created a custom button for Tag along item.and I am having problem with cursor box. It is not moving down. It is staying where the tag along item rang up. It should move down to empty line, But It is not. How Can I fix his problem. Any one can help me ? ...

Two different organizers in one meting
imagine the folowing: Person A is the assistant of Person B, who has given delegation on his calender. When person A makes a meeting request in the calendar of person B and has made a reservation of a resource (meeting room), everything seems to be working fine. however, when person A opens the meeting request in the calendar folder of Person B, I can see that the organizer of the meeting is Person B. When I open the meeting in the calendar of the resource mailbox, the organizer of the meeting is Person A ?? This occurs with Outlook 2007 SP2 and Exchange 2010. Is there a way t...