chg design from EXCEL to ACCESS

[aw]

Existing I have the following table structure (in Excel) and now want to have
better control by using ACCESS.

SC no Buyer PC no Supplier Item QTY price cost
SC001 A Co PC501 H co SS 70 30 28
SC001 A Co PC501 H co CC 100 25 21
SC001 A Co PC502 Q co BB 20 90 80
SC001 A Co PC503 J co SS 50 31 27

remark :
SC no = sale contract number
PC no = purchase onctract number
Relationship b/w SC & PC is 1 to many
SC & PC will arise at the same time (no inventory base)

How to design my tables (in Access) so that I can handle this?
The main problem I meet is the duplicate input for SC & PC for the same
items & need to separate it one-by-one as per above table.

 

[Fred]

If I understand your situation properly, you might just import the whole
thing into a table and then use the analyze -> table tool.

 

[John W. Vinson]

Existing I have the following table structure (in Excel) and now want to have
better control by using ACCESS.

SC no Buyer PC no Supplier Item QTY price cost
SC001 A Co PC501 H co SS 70 30 28
SC001 A Co PC501 H co CC 100 25 21
SC001 A Co PC502 Q co BB 20 90 80
SC001 A Co PC503 J co SS 50 31 27

remark :
SC no = sale contract number
PC no = purchase onctract number
Relationship b/w SC & PC is 1 to many
SC & PC will arise at the same time (no inventory base)

How to design my tables (in Access) so that I can handle this?
The main problem I meet is the duplicate input for SC & PC for the same
items & need to separate it one-by-one as per above table.

You need to identify the "Entities" - real-life persons, things or events -
relevant to your application. I don't know your business of course but it
would appear that the following kinds of entities are relevant:

Sale Contracts
Purchase Contracts
Parts
Suppliers
Buyers

Each distinct type of entity needs its own table; the fields in that table
would be attributes of the entity.

It's not clear to me what entity - if any! - the spreadsheet represents. Is
each row a transaction, or a line item in a multi-part transaction?

You may want to investigate some of the resources at
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[aw]

Hi all,

Actually I have already established the following tables. My main problem
is that how my users can input all those information into these tables &
duplicate entry. (eg. duplicate input for SC & PC's "QTY" , "item" ..)
What I am seeking is the convenience way for designing a form for my users
data entry. I have no idea whether I need improve my tables structure or I
need to design a complex form for their input.

my tables :

(1) tbl CustomerMaster
cm.CustomerCode (PK)
cm.CustomerName
…

(2) tbl SaleOrderMaster
som.SaleOrderNumber (PK)
som.CustomerCode (fk)


(3) tbl SupplierMaster
sm.SupplierCode (PK)
sm.SupplierName
…

(4) tbl PurchaseOrderMaster
pom.PurOrderNumber (PK)
pom.SupplierCode (fk)
pom.SaleOrderNumber (fk)


(5) tbl ItemMaster
im.ItemNumber (PK)
im.ItemDescription
…

(6) tbl Sale OrderDetails
sod.SaleOrderTxNo (PK)
sod.SaleOrderNumber (fk)
sod.ItemNumber (fk)
sod.UnitPrice
sod.QTY

(7) tbl PurchaseOrderDetails
pod.PurchaseOrderTxNo (PK)
pod.PurOrderNumber (fk)
pod.ItemNumber (fk)
pod.UnitCost
pod.QTY

Thx a lot in advance.

 

[Fred]

Fro your original post I though you were still at the stage of dealing with
an unnormalized spreadsheet.

It looks to me like you have a good structure. Now your next stage is to
define your operational needs and designing queries and forms to fulfil
those. And ask any specific questions here.