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
|
|||
|
|||
chg design from EXCEL to ACCESS
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. |
#2
|
|||
|
|||
chg design from EXCEL to ACCESS
If I understand your situation properly, you might just import the whole
thing into a table and then use the analyze - table tool. |
#3
|
|||
|
|||
chg design from EXCEL to ACCESS
On Tue, 7 Jul 2009 01:44:17 -0700, aw wrote:
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/acc...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 -- John W. Vinson [MVP] |
#4
|
|||
|
|||
chg design from EXCEL to ACCESS
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. -- aw "John W. Vinson" wrote: On Tue, 7 Jul 2009 01:44:17 -0700, aw wrote: 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/acc...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 -- John W. Vinson [MVP] |
#5
|
|||
|
|||
chg design from EXCEL to ACCESS
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. -- aw "John W. Vinson" wrote: On Tue, 7 Jul 2009 01:44:17 -0700, aw wrote: 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/acc...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 -- John W. Vinson [MVP] |
#6
|
|||
|
|||
chg design from EXCEL to ACCESS
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. |
Thread Tools | |
Display Modes | |
|
|