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
|
|||
|
|||
I Need Help With This Problem
I have a hugh data the was broken up into 4 tabs in excel because each row
has over 600 columns. I have been able to bring each tab into Access as a table but I can't figure out how to bring all these tables together unto one form. I need help in figuring out how to use data from all four tables on a form. I know I have to join the tables together but my problem is there is no field to use as a primary key. How do I accomplish this with what I have. |
#2
|
|||
|
|||
I Need Help With This Problem
This is a huge question you are asking, and there will not be a 5-minute
answer for you. The question boils down to: a) How do you create a relational database to handle the 600 columns from Excel, and b) How do I populate the relational dataase with the existing data. To answer the first question, you will need to gain some understanding of the process of normalization: where and how to break this data down into a series of one-to-many relationships. If you have never done this before, it will involve a considerable amount of reading and experimenting to get it right. Here's a starting point, consisting of 8 PDFs that will get you started in Access: http://allenbrowne.com/casu-22.html The 3rd one explains the normalization process. Here's a couple of ultra-basic examples to get you started: http://allenbrowne.com/casu-06.html http://allenbrowne.com/casu-23.html And here's a bunch more links dealing with this: http://www.accessmvp.com/JConrad/acc...abaseDesign101 Once you have a nice relational structure set up so the data is easily queryable, you will have the advantages of using a database using a spreadsheet, e.g. suitably typed fields (e.g. a Date/Time column where you can't type invalid dates like you can in Excel), validation (e.g. certain field marked as Required, so they can't just be left blank as they can in Excel), and relational integrity between your tables. The second question then becomes how to import the existing data from Excel. Again, this not a simple process, because you will find the spreadsheet contains bad data (such as "Not applicable" typed into a date column, or a blank where the field you created in Access is required.) Importing existing data into a good, relational structure can therefore be time-consuming, depending on how bad the Excel data really is. After you have a good relational structure, and the data imported, the next stage of development involves creating the queries (to select, sort, and combine the data from different tables), the forms (the interface for entering/searching/editing/deleting data), and the reports (for outputing results.) Whether it is worth your time to create a relational database and import it will depend on how important the data is to you, and whether Access really is the best tool to manage it in. It may well be worth the effort, but you need to be clear before you start as to what you are doing. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ayo" wrote in message ... I have a hugh data the was broken up into 4 tabs in excel because each row has over 600 columns. I have been able to bring each tab into Access as a table but I can't figure out how to bring all these tables together unto one form. I need help in figuring out how to use data from all four tables on a form. I know I have to join the tables together but my problem is there is no field to use as a primary key. How do I accomplish this with what I have. |
Thread Tools | |
Display Modes | |
|
|