Importing from Excel

  • Thread starter Thread starter Tim Schubach
  • Start date Start date
T

Tim Schubach

I was recently given responsibility for an Excel data file. Each row in the
file contains customer information and sets of usage data. To make
administration of the data more user-friendly and easier to read, I created
a simple Access application. The application consists of two tables and
associated forms, one for the user information and one for the usage data.
When the user form displays, the usage form also displays as a sub-form with
user-specific statistics.

When I enter data using the forms, the tables are updated as expected, and
the application works great. However, I now need to import the whole Excel
spreadsheet into the Access application. My problem is that I don't know
how to import one spreadsheet into two separate tables while maintaining
data integrity.

Is there a way to import rows in the Excel spreadsheet, direct the data into
the appropriate tables, and generate the keys required to associate the
usage stats with the user in that row? Any help would be appreciated.

Thanks,
Tim
 
Can you import everything into a single table in Access, then use queries to
split it into the appropriate tables?
 
For that matter, you may not even need to import from Excel into a single
table: link to Excel, and write queries on that linked table.
 
Doug,

Thanks much for your response. I am very much an Access novice, so I'm not
exactly sure how to accomplish what you're suggesting.

For privacy reasons, I can't divulge the purpose of the application, but I
can give you an example. For the sake of argument, assume that the first
cells in each Excel row contains employee information ( name, address,
phone, employee number, etc. ). The rest of the cells are grouped together
( not merged ) and contain date, time, and building location where the
employee swiped their ID badge when they entered or exited the building.
The Access application would show the employee information in the main form
( static information ), and the subform would contain a row for each
entry/exit occurrence. Depending on the employee, the number of
entrances/exits could be significant. In other words, the number of
occurrences is not the same for each employee.

That then means that I have no real way to set up a single table for all of
the data. What I would like to be able to do is import each row, and split
off the data into the appropriate table and number of Access records.

Again, any pointers would be greatly appreciated.

Thanks,
Tim
 
It's not clear from your description whether or not each row with swipe
information includes all of the employee information or not, not exactly how
the swipe information is arranged: is there one column for each swipe, or
one row?

Do you know how to link to Excel from Access? When you link, does the linked
table give you anything useful?
 
Back
Top