Append Query?

[kwooten]

My boss creates monthly files, headcount data for our organization. I need to create a headcount report for each month. Right now i have to take his files and combine them in excel and add a date label to each row for that specific month. Anyway that file is getting pretty big and is soon going to be to big to handle.

How can i create a database in Access that will import his files and combine them into 1 database, keeping the "import month" (which is something I can manually add to the file before importing to access if needed). Would I do an append query? Im not sure how to create this? he also has a turnover report off this file which calculates the turnover percentage (average headcount/total turnover).. I would need to create this report as well. But i need the headcount database first..



Any ideas?

[alansidman]

First you will have to import your data (spreadsheets) into a temporary table in Access. Once you have the temporary table populated, you will then need to create an append query to update the primary table. Make sure to delete the data in the temporary table once the main table has been updated.

Here is a tutorial on append queries.

http://599cd.com/tips/access/student...y/?key=YouTube

and here is a tutorial on importing data into Access

http://accessdatabasetutorial.com/20...rosoft-access/

[kwooten]

First you will have to import your data (spreadsheets) into a temporary table in Access. Once you have the temporary table populated, you will then need to create an append query to update the primary table. Make sure to delete the data in the temporary table once the main table has been updated.

Here is a tutorial on append queries.

http://599cd.com/tips/access/student...y/?key=YouTube

and here is a tutorial on importing data into Access

http://accessdatabasetutorial.com/20...rosoft-access/

That was really no help. The video goes into things im not doing. The files I am getting are about 12,000 rows each. I know how to import data into Access so that isnt an issue.. Should I add that his files are DBF files.

[rpeare]

What you're describing is an extremely inefficient way to do things. Is there any reason you can't create the entire application in Access? This would mean providing a method for data entry to your boss that was relatively simple (and from what you're describing it's not complex) Then you don't have to do any data transfers at all and instead of having an unknown number of files to manipulate over time it's all contained in the database already.

If you can show an example of the source spreadsheet we can likely point you in a direction to make this an all access application.

[kwooten]

My boss is 62 years old and has been doing this same process for 12 of the 36 years he has been here. Getting him to change his process is about as likely as Israel and Palestine becoming allies. I cant supply the DBF as it has sensitive information in it. I could do it in excel of just the data i really need and exclude the sensitive data if that will work.

[rpeare]

I'll guarantee you you can make a method to enter data that is easier than what he's doing and if you show it to him he'd likely try it especially if you can show him that it will save time (money).

At any rate yes, if you show an example of the spreadsheet, don't put in any sensitive data, just put garbage data in and indicate which fields you need to extract from the file.

[kwooten]

Yeah I wish he was that easy to change. It would take too long to show him what to do..

Attached is January-March 2011...

Typically the way the report looks is Div1 in the 1st column, each month on the same row with the headcount for each Div1 under the month.. Which I can take care of if I can just figure out how to get a database that shows these employees for each month.

[rpeare]

Here's a sample.

It includes the three files you originally posted and the database. Look at the table first and see that it's empty. After you run the module ImportData it should be populated and the ORIGINALFILENAME field should be filled in with the first portion (before the .xlsx) of the filename.

Just make sure all the files are in the same folder.

If you intend to have them in different folders (the database and the source files) you can account for that in the code.

There's also no notification in the module when the thing is complete, you can add one if you like.

[kwooten]

I think this is perfect... I will have to save my file as the actual month name so it will show on a report correctly.

What if i need to add a field, can i just create a field name for the new field? Say Grade? The grade value may also have to be reassigned to a consolidated range (01-07 & 08 and UP) which I assume I can do when by creating a query off this import table?

[rpeare]

Just double click the module it should open up the code, then just click the right facing arrow in the toolbar.

If you need to add a field you add it in the same order on the imports table that they appear on the spreadsheet.

Reassigning the grade is not done on the table. You can do that in a query because it's a calculated value.

[kwooten]

I found the run button... Can I save these files and rename them anywhere, as long as they are in the same place?

This is great... You have once again bailed me out and I really appreciate it.

[kwooten]

OH! Also.... Can I still use his DBF files or will I need to convert them to excel?

[rpeare]

if you rename them you have to alter the code to handle whatever the original file names are, my tests are:

is the file name 11 characters long
are the first 6 characters numeric
are the last 5 characters '.xlsx'

If your source files have different names you have to change those tests. but otherwise yes, as long as the database is in the same folder as the .xlsx files it will work. It will also work if you specifically designate a folder where all the .xlsx files will be and then the database and the files do not have to be in the same folder.

[rpeare]

any format that access recognizes you can use. .dbf are dbase files I think and those are compatible.

[kwooten]

Well I guess it doesnt matter if I rename them... I can create a label in the report for "January"...