excel into access

[Apollo13a]

Ok, here's one for the experts
Is there a way import 24 excel spreadsheets into an access databse. the reason I'm bringing them into access is for daily reports. I have 24 folders (one for each foreman and each foreman has ten employees.) the spreadsheet allows the foreman to assign task codes and hours to each employee. I create new spreadsheets everyday for the foreman, they fill them out and I compile them into one master. I know I could import external data, but I would have to do it 24 times. Any ideas on how I could automate or speed this up?
Could I create an access form or datasheet that resembles the excel spreadsheet? Maybe even import it directly and create a table with the same fields and write to it?
thanks,
Jim G

 

[ToeShot]

Look into the help Files for DDE (Dynamic Data Exchange) The help files are usefull here sense the have examples using Excel

Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[ReluctantDataGuy]

Lots of ways to skin this cat...

If each Excel spreadsheet is pretty simple, you could just link them. If they all have the same name, you can "rename" the link in Access to a name that uniquely identifies each one.

To avoid a lot of confusion, you can rename each one as you link it.

Then, if you need to combine the data you might be able to do one big honkin' UNION query (assuming all the spreadsheets have the same column names...)

Example:

Select * From Spreadsheet1
UNION
Select * From Spreedsheet2
UNION
Select * From Spreadsheet3
UNION
...
...
Select * From Spreadsheet;

Note Spreadsheet1, 2, 3 through are just example names. You'd use your own unique names.

If you don't want to just combine them all with a union query, you could append them to a table one at a time, or write some code to loop through the linked tables. If you do the latter, you might want to name your linked spreadsheets in a manner that lets you identify them among the list of tables so that you can loop through all tables and IF the current table name meets certain criteria (like part of the name is Like "XL-*" Then use that linked XL spreadsheet to append to a table.

Without details its hard to get more specific.

I hope this at least points you in one of many directions this could go...

 

[Apollo13a]

Great I going to try this. I know what you mean about linking as opposed to importing. My only difficulty is depending on the week the data could be in different cells on the spreadsheet. e.g. there is a list of names along the left border and a list of codes along the top so one week could have three names along the leftside and another week there might be 10 names. How do I relate a specific record everyday or week?

 

[ReluctantDataGuy]

Inconsistent data in the spread sheet could be a problem. I've had trouble going the other direction - Access to Excel when the query in Access had varying #s of rows or columns.

One option would be to rethink the input in the spreadsheets so that it has more consistent data. A better option might be to switch to Access for their input. They may be spreadsheet heads, but a simple input form usually doesn't put spreadsheet heads off too much - as long as they don't have to think to hard (just kidding).

But if you gave them an access form, you could set it up so that the data is being entered directly into the database in the format and location you want it rather than having to load it as you do now.

I relate it to the airplane business. I work at Boeing, and I've made databases for one airplane model and over time other models discovered it and wanted the same thing. The quick and dirty solution was to just give them a copy and change a few things that were model specific.

In the long run though, that turned out to be a mistake because now I have 3 different groups on each of 5 different models - and every time I make a change, I have to do it to all those databases.

So I'm now working on merging it all into one database, adding a model identifier to everything.

Just some ideas to chew on.

If you really have to use the current scenario, you might have to resort to code to loop through the imported rows in order to get the job done.

 

[lmn]

ReluctantDataGuy,

If you were to build an Access database for all of them to enter information into - do you...

1) Build a database and put it on a shared drive? (only one user could make changes at a time)
2) Create a web form and have it feed the data into a format that could be read by a DB
3) Build an "application" where the databases all sync up

I'm in this type of pickle right now - we have an enormous checklist Project Managers fill out after a project, and I can create an Excel or Word doc to make it easy to enter for them - but then I have to get that data into the database. Ideally I'd do a web front end - but no one at this place would even know how to start (including myself) - or even an Outlook form (again, requires too much technical know how that isn't present at this site).

 

[Flo79]

Before you start importing all the Excel Data into Access it is important to recognize that the names of the foremen and his or her employees are kept consistent. Before you import all of your data, Access can help you to make this task a lot easier in the long run.

Step 1 ¡V Building the right table structure:

First, you need to have the right table structure that avoids redundancies within your data, meaning that all names of foremen as well as their respective employees are consistent throughout the entirety of your dataset. Also, ¡§normalizing¡¨ your data - as this process is called ¡V is vital in that it enables you to correlate all information in the correct manner, therefore ensuring that your database will function properly.

Specifically, this means that you need to have two tables: The first one, which I will call ¡§Foremen¡¨, should contain the names of all foremen. The second table, which I will title ¡§Employees¡¨, should list all employees and their respective foreman. More precisely, if one Foreman A has ten employees reporting to him, the ¡§Employees¡¨ table would have to list ten times Foreman A in one column, and the name of each employee reporting to that same foreman in another. Assuming then that the ¡§Foremen¡¨ table has one column titled ¡§Foreman Name¡¨, while the ¡§Employees¡¨ table has two columns entitled ¡§Foreman Name¡¨ and ¡§Employee Name¡¨, you are now able to link the two tables based on the ¡§Foreman Name¡¨ column, which is present in both tables. This of course only works if the ¡§Foreman Name¡¨ column in both tables reads exactly the same. Otherwise, the computer has no way of associating each employee with the right foreman. You now have a very basic normalized database structure.

While the prior is crucial, you need to recognize one more complexity that is equally vital in ensuring that your database will work: Since, two foremen might have the same name; simple names won¡¦t do in identifying each foreman as an individual and consequently do not guarantee that each foreman is associated with the right employees. Therefore, another identifier is needed that is unique for each foreman entered. Access has a need little trick to do this: When creating the ¡§Foremen¡¨ table mentioned earlier, include an additional field called ¡§Foreman ID¡¨, the type of which should be ¡§AutoNumber¡¨.

Step 2 ¡V Creating the actual database:

There is a bit of manual labor involved here, but it will make your work much easier in the long run and take care of that ¡§linking problem¡¨ of yours.

After you have created the two tables mentioned above, they should look something as follows:

FOREMAN TABLE

Field (data type):

1. Foreman ID (AutoNumber)
2. Forman Name (Text)

EMPLOYEE TABLE

Field (data type):

1. Foreman ID (Number)
2. Employee Name (Text)

Once you have created these tables do the following: First enter in a single foreman¡¦s name in the ¡§Foremen Table¡¨. Notice that if you have set up the table correctly, a number should automatically appear in the ¡§Foreman ID¡¨ field. Now open the ¡§Employees Table¡¨ and enter in two employees¡¦ names, making sure to specify the same ¡§Foreman ID¡¨ number for each of them as it appeared in the prior table (almost done¡K). Next, LINK the two tables in the relationship window of your database based on ¡§FOREMAN ID¡¨. It is important to ENFORCE REFERENTIAL INTEGRITY (see Access own help feature as a reference if need be) so that the relationship type becomes ONE-TO-MANY, with one on the ¡§Foremen Table¡¨ side.
Lastly open the ¡§Foremen Table¡¨ in table view and select insert sub-datasheet from the insert option on top. Select the ¡§Employees Table¡¨ as the table to be inserted and ¡§Foreman ID¡¨ for both the Child and Master Field options below.
You now have a trivial, but nevertheless very effective database.

Step 3 ¡V Importing your data:

While only using the ¡§Foremen Table¡¨ now, enter in the names of all Foremen. Then, reveal their respective employees by clicking on the ¡§+¡¨ to the left of their name. You can cut and paste each foreman¡¦s employees here (for each foreman separately of course).
Again, while this might seem a bit tedious at first, it will surely pay of by guaranteeing the accuracy and long-term actionability of your data.

 

[ReluctantDataGuy]

Thanks Flo! All good advice.

Imn, to answer your question, If data input is minimal, I might put the entire database out on a server where they can get to it. Put a shortcut to it on their desktop to make it easier for them to get to.

Multiple users in a database isn't a problem as long as there are never more than a few users (well, 254 max I think, but you'd never want to approach that limit!)

Another solution is to create a database that stores the data, using the principles stated by Flo, and put it out on the server.

Then create an application database and link it to the database tables on the server. You could then put the front end application on each user's machine.

That works good if you don't need to make a lot of changes to the front end and have only a few users.

For a more robust solution, you'd use the front end / back end scenario I just described, but automate the upgrade process. There's lots of ways to do that, and I think I've seen some white pages on it either in these forums or referenced from these forums.