Discussion Locked

Excel sheets in to one access database.

0 helpful
10 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

To all of Excel\Access gurus out there:
Trying to import about 15 excel sheets in to one access file to keep track of my inventory. In a way, all these sheets have user, licesnce #, PO number, purchased dates, key #s and other purchase related information. So by having it in an access file we want to be able to update many fields by just clicking one button. e.g. if machine name was changed, the new machine name has to get updated in all the other fields with old machine name in an automated way through access.

10 replies

by mike_bowling

The important thing is to set up the Access database correctly, then import the data as needed.

Because Access is relational, a single field for ComputerName is used in one table and then displayed in various queries and reports. The multiple entries in your spreadsheets then become one entry in a single table called "system_info".

You only need new tables when the information does not have a direct correlation. For instance, if purchasing and vendor info for components is needed, you would create a second table "vendors" and link the two tables by a key reference, say serial number or asset number.

I hope this scratches the surface of your question. The whole concept of database design in Access is too complicated to describe here. There are many books that go into more detail.

by redeyes

Try This: From within an existing MSAccess97 database, select "File", "Get External Data",then "Import". Change the "Files of Type" selection to "Microsoft Excel (*.xls)" then navigate to your Excel workbook. When the "Import Spreadsheet Wizard" begins, just follow the prompts. --Good Luck

by redeyes

I'm assuming the user just needs help to get the data imported. Database design is a different subject.

by allan

The easiest way that I have found is to "copy and paste" all the info into a single sheet and then import into access.
By doing the"legwork" first the database comes out with a single table and is easier to work with.

by Member 35772270

The best sure fire way is to open Access and create a table in the design view, enter a field name for each colum in the spreadsheets, them import the data. Access 2000 has a wizard to do this with, but be careful you can end up with multiple tablethat you might not want.

This discussion is closed to new replies.