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

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