General discussion

Locked

Excel sheets in to one access database.

By ehsan ·
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.

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel sheets in to one access database.

by mike_bowling In reply to Excel sheets in to one ac ...

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.

Collapse -

Excel sheets in to one access database.

by ehsan In reply to Excel sheets in to one ac ...

Poster rated this answer

Collapse -

Excel sheets in to one access database.

by RedEyes In reply to Excel sheets in to one ac ...

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

Collapse -

Excel sheets in to one access database.

by RedEyes In reply to Excel sheets in to one ac ...

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

Collapse -

Excel sheets in to one access database.

by ehsan In reply to Excel sheets in to one ac ...

Poster rated this answer

Collapse -

Excel sheets in to one access database.

by allan In reply to Excel sheets in to one ac ...

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.

Collapse -

Excel sheets in to one access database.

by ehsan In reply to Excel sheets in to one ac ...

Poster rated this answer

Collapse -

Excel sheets in to one access database.

by Dennis@l In reply to Excel sheets in to one ac ...

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.

Collapse -

Excel sheets in to one access database.

by ehsan In reply to Excel sheets in to one ac ...

Poster rated this answer

Collapse -

Excel sheets in to one access database.

by ehsan In reply to Excel sheets in to one ac ...

This question was closed by the author

Back to Windows Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums