If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Invoices with Sales Order & Purchase Order
Background
- We obtain order request from customer & then we will find our suppliers for EXACT QTY delivery. - In this case, we need total Two (2) suppliers to cover this request. - So we arise PC050 from supplier A & PC051 from supplier B to cover customer Z's order SC001. - As a result, No inventory will keep for all our business. - After that two invoices will arise and bill our customer Z (IV-888 & IV-777, 2 invoices due to date of delivery are different) - This is the whole "1 set" of data after all of the above data being entered. - So, Sales contract, Purchase contract & invoice should have a special relationship at a whole. TABLE 1 (sales contract detail) SALES CONTRACT NO PRODUCT QTY UNIT PRICE ------------------- ------- ----- ---------- SC001 AC 5000 $30 SC001 AC 3000 $35 SC001 AD 10000 $20 TABLE 2 (purchase contract detail) PURCHASE CONTRACT NO PRODUCT QTY UNIT COST -------------------- ------- ----- ---------- PC050 AC 4000 $20 PC050 AC 1500 $25 PC050 AD 8000 $10 PC051 AC 1000 $22 PC051 AC 1500 $27 PC051 AD 2000 $12 TABLE 3 (invoice detail) INVOICE NO PRODUCT QTY SELLING PRICE -------------------- ------- ----- ------------- IV-888 AC 4000 $30 IV-888 AD 10000 $20 IV-777 AC 1000 $30 IV-777 AC 3000 $35 Relationship need to establish ============================== 1. "SALE CONTRACT NO" ---- "PURCHASE CONTRACT NO" is "1 to many" (eg. SC001 are covered by PC050 plus PC051) 2. "SALE CONTRACT NO" ---- "INVOICE NO" is "1 to many" (eg. SC001 are covered by IV-888 plus IV-777) Actually I copy the field "SALES CONTRACT NO" from table 1 to table 2 & table 3 as foreign key. It works fine for relationship b/w "tabe 1 with table 2" & "table 1 with table 3" but cannot get the relatiohsip b/w "TABLE 3 with TABLE 2". ----------- Request ----------- We need to keep check all corr. relationship between the said tables. eg. relationship between SC with PC, SC with INVOICE & INVOICE with PC. how ACCESS relationship it should be set in more convenience way & what foreign key should be established under those tables?! -- aw |
#2
|
|||
|
|||
Invoices with Sales Order & Purchase Order
AW,
Seems to me you are missing a few tables, see below... You seem to be commiting spreadsheet with a realtional database. I would also consider a set of tables for Product and Vendors but that's just me. As for your 'issue' with Access realtionships and FK (Foreign Keys) you must understand realational database design. Seems to me you are trying to 'relate' a flat file format with no clear PK's (Primary Keys) defined. With the information you provided I can't see how you even created relationships at all. I would suggest you have a look at.... Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP): http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials (1) tblSalesContract scSalesContractID (PK) tblSalesContractDetail scdSalesContractID (FK) scdSalesContractDetailID (PK) scdProduct scdQuantity scdUnitPrice (2) tblPurchaseContract pcPurchaseContractID (PK) pcSalesContractID (FK) tblPurchaseContractDetail pcdPurchaseContractID (FK) pcdPurchaseContractDetailID (PK) pcdProduct pcdQuantity pcdUnitCost (3) tblInvoice iInvoiceID (PK) iPurchaseContractID (FK) ***This relates back to Purchase Contract which relates back to Sales Contract. Not sure why you need to go directly to Sales Contract if you can get there via Purchase Contract.*** tblInvoiceDetail idInvoiceID (FK) idInvoiceDetailID (FK) idProduct idQuantity idSellingPrice -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "aw" wrote in message ... Background - We obtain order request from customer & then we will find our suppliers for EXACT QTY delivery. - In this case, we need total Two (2) suppliers to cover this request. - So we arise PC050 from supplier A & PC051 from supplier B to cover customer Z's order SC001. - As a result, No inventory will keep for all our business. - After that two invoices will arise and bill our customer Z (IV-888 & IV-777, 2 invoices due to date of delivery are different) - This is the whole "1 set" of data after all of the above data being entered. - So, Sales contract, Purchase contract & invoice should have a special relationship at a whole. TABLE 1 (sales contract detail) SALES CONTRACT NO PRODUCT QTY UNIT PRICE ------------------- ------- ----- ---------- SC001 AC 5000 $30 SC001 AC 3000 $35 SC001 AD 10000 $20 TABLE 2 (purchase contract detail) PURCHASE CONTRACT NO PRODUCT QTY UNIT COST -------------------- ------- ----- ---------- PC050 AC 4000 $20 PC050 AC 1500 $25 PC050 AD 8000 $10 PC051 AC 1000 $22 PC051 AC 1500 $27 PC051 AD 2000 $12 TABLE 3 (invoice detail) INVOICE NO PRODUCT QTY SELLING PRICE -------------------- ------- ----- ------------- IV-888 AC 4000 $30 IV-888 AD 10000 $20 IV-777 AC 1000 $30 IV-777 AC 3000 $35 Relationship need to establish ============================== 1. "SALE CONTRACT NO" ---- "PURCHASE CONTRACT NO" is "1 to many" (eg. SC001 are covered by PC050 plus PC051) 2. "SALE CONTRACT NO" ---- "INVOICE NO" is "1 to many" (eg. SC001 are covered by IV-888 plus IV-777) Actually I copy the field "SALES CONTRACT NO" from table 1 to table 2 & table 3 as foreign key. It works fine for relationship b/w "tabe 1 with table 2" & "table 1 with table 3" but cannot get the relatiohsip b/w "TABLE 3 with TABLE 2". ----------- Request ----------- We need to keep check all corr. relationship between the said tables. eg. relationship between SC with PC, SC with INVOICE & INVOICE with PC. how ACCESS relationship it should be set in more convenience way & what foreign key should be established under those tables?! -- aw |
Thread Tools | |
Display Modes | |
|
|