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
|
|||
|
|||
Import xls data
I have a excell workbook that contains 4 sheets each sheet representing a
country and each have the same columns of data. Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice details-Invoice Amt There are hundreds of rows and the data above needs to be imported into several tables.Is there a way to avoid the manual entries into the tables? Thank you |
#2
|
|||
|
|||
Import xls data
Yes, if you go to File-Get External Data, you can import Excel data into
Access Tables. You may have to import the same data several times to get it into the tables you want. "jk" wrote: I have a excell workbook that contains 4 sheets each sheet representing a country and each have the same columns of data. Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice details-Invoice Amt There are hundreds of rows and the data above needs to be imported into several tables.Is there a way to avoid the manual entries into the tables? Thank you |
#3
|
|||
|
|||
Import xls data
That sounds simple enough but what happens when you want the client number as
the primary key since it is unique and this information is repeated many time in the rows of excell? How does the import determine that the different row headings belong in different tables? "GB" wrote: Yes, if you go to File-Get External Data, you can import Excel data into Access Tables. You may have to import the same data several times to get it into the tables you want. "jk" wrote: I have a excell workbook that contains 4 sheets each sheet representing a country and each have the same columns of data. Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice details-Invoice Amt There are hundreds of rows and the data above needs to be imported into several tables.Is there a way to avoid the manual entries into the tables? Thank you |
#4
|
|||
|
|||
Import xls data
I would like to know if when you do the import and the client number is the
primary key which is unique then how does access collect all the rows of data in excell that belong to each of the unique client numbers? "GB" wrote: Yes, if you go to File-Get External Data, you can import Excel data into Access Tables. You may have to import the same data several times to get it into the tables you want. "jk" wrote: I have a excell workbook that contains 4 sheets each sheet representing a country and each have the same columns of data. Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice details-Invoice Amt There are hundreds of rows and the data above needs to be imported into several tables.Is there a way to avoid the manual entries into the tables? Thank you |
#5
|
|||
|
|||
Import xls data
On Wed, 21 Feb 2007 15:23:18 -0800, jk
wrote: I have a excell workbook that contains 4 sheets each sheet representing a country and each have the same columns of data. Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice details-Invoice Amt There are hundreds of rows and the data above needs to be imported into several tables.Is there a way to avoid the manual entries into the tables? Thank you You'll probably want to create three new tables: Clients, with the client and account information (this will be two tables in a one-to-many relationship if each client can have multiple accounts); Invoices (related one to many to Accounts); and InvoiceDetails, related one to many to Invoices. You'ld use File... Get External Data... Link to link to your spreadsheet (either running it four times, linked to each sheet in turn, or you could create four queries). You can then run Append queries to migrate the data into your normalized tables. I can't tell from here (since I can't see the data) which fields should go in which table - does the EntryDate pertain to an invoice detail or to an invoice? Is the Invoice Amt a sum of details? Are there multiple columns of invoice details, or multiple rows? - so I can't write the query for you, but don't hesitate to post back with more details (perhaps a few rows of the spreadsheet, munged for privacy if you wish). John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|