Split table :-)

[aner1755]

Hi All!
I'm in a situation where I have a Excel file with a budget for a project, and I think(!) I need to split the imported (excel -> access) table into two tables. Please take at look at the attached file, where the red section is the column heading, the blue section contains info about name of project etc and should go to one table and the green section is the time every employee spends every month and should be directed to another table.
As you all know projects tends to be imprecise in time... and it could be nice to add more records to the table containing time/employee/month.
I got two approaches;
1) Import the excel-file and divide it inside access, but how to split the table? As you can see from the attached file the columns heading should now serve as part of a record in the new table.
2) Could one in some way choose what area in the Excel file to be imported? Say that one states in some SQL/VBA syntax which column/row to import. Then it would just be to perform two imports without further editions.

Hope you could give me any advice.
Cheers!

 

[namliam]

Ussualy the easiest way to do this is to import* the excel into a temporary table.

Then either use Append queries to send the data into the right normalized tables or write some code to do same.

* Instead of importing you can also link the excel file, but I generaly dont do that.

 

[aner1755]

Thanks Mailman!
I'm glad you put me on the right track

 

[aner1755]

Hi again Mailman and All!
I’m afraid I not got the skills to work this out even with your previous help. I’ve been thinking, trying and reading in order to solve the problem but I just can’t manage. Would you be kind to read the below to see my “updated ” situation?
Situation is like this; I have an imported excel file (table Export) which contains two parts, see TableSplit.bmp. The first (green) got “main” information about a project and the second (red) lists the time each person has worked. This type of file is to be imported for every project our company is working with.
The green part always has the same structure (same columns) for every project, but the red part is of arbitrary length due to the length of the current project.

The excel file should be imported to the same table every time (Export) and then direct the green part to tblBudget and the red part to tblTid. The green part I got right, I used an append query where a fixed mapping is performed. But how to map the red part to tblTid, i.e how to map an arbitrary amount of column headings to be records in another table?
My wish is a file looking something like TableSplit2.bmp. (Please note that the dates in column MånadOÅr represent the column headings with names 39XXX in table Export. I haven’t yet got the dates in the excel file to be imported properly… hence the 39XXX headings )


I got to state that this forum is driven by a bunch of realy good people. Without this help and dialoge Access would be a lot harder... thanks!
Cheers!

 

[namliam]

Brrrr... so you have yourself a de-normalized mess on your hands that needs to be normalized.... *ouch*

It is possible to use coding to account for a flexible number of columns, but it is quite involved...

The best suggestion I can give you, if possible go back to the source where you got this report and ask for the data in a more proper layout i.e.

Instead of having
Project date1 date2 date3
xxxxxxx 12 13 14

Have something like
Project date hours
xxxxxxx 1 12
xxxxxxx 2 13
xxxxxxx 3 14

If that is not possible this may be a very long thread... You will need to do quite some coding to get where you want to go. Then again.... maybe its not that bad... But try getting a different, more proper source first. That is the magic solution

 

[aner1755]

Thanks for quick answer! :-D
Yes, a better structure of input data (read; normalized Excel file) would make things much easier. You probably know costumers (read; my boss) are… conservative and not up for a change in routines. He and many of my collaborators have had their standards for their excel files quite a while and are not easily convinced to break their patterns.
But you say it will be a lot of hustle to work this out? Humm… what is the simple path here…? Probably to sell the idea of changing a routine for the better… but what would be the most fun? Surely to overcome a major obstacle! J But as sure as “amen” in church, I won’t solve it without you (read; all nice forum people). Would you be kind to at least give me the raw sketches how to get there? I’m not afraid of getting my hands dirty with code…


Maybe this could come to use for many more?

 

[namliam]

Dont know what would be the "major obstacle", convincing your boss or doing the coding or explain you how to do the coding

Seriously, doing the coding is not THAT hard... if you know how... explaining it online, something quite different.
Also having dates beeing represented in numbers (though thats really all they are....) Nobody should accept anything like that, rediculous!

The idea of the code is to make a table that will import the spreadsheet, this will be a table with 256 to be able to accept "limitless" (or atleast excel's limit) number of columns.

Once loaded you need to load the first record of the table to find out the column headers to find the date.
Then run queries for each column to add the value + date + project # into your table tblTid. Rince and repeat that for all the columns where there is a value in the header record...

I really feel the 'challenge' and solution in this problem lies in convincing the custoimer that this is unworkable.
More so, data transfers from any system to Excel to Access is in general not a smart solution. Using Text files or even direct data transfer over ODBC are MUCH prefered over data transfer in Excel.

So
#1 Data problems (normalization)
#2 Type problems (ie. dates)
#3 Filetype problems (ie. Excel)

Solution to all, do it differently! And/or confince the customer/boss

 

[aner1755]

-Don’t know what would be the "major obstacle", convincing your boss or doing the coding or explain you how to do the coding
Word!

This is so good, without your opinion I think I would have been struggling with this a long time. Thanks for all great advice. I’ll drop this for now to proceed with my main task, but in some spare time I’ll try your guideline just to get deeper in under the shell...

You are great!

 

[namliam]

Dont hesitate to come back and ask questions if you have them

 

[aner1755]

I had a night’s sleep and a morning of reading, and it got me wonder if a way to go is to;

1) Direct the green part of TableSplit.gif to tblBudget (as earlier)
2) Use some kind of query to delete the green columns
3) Perform a transpose (linear algebra) of the remaining part (red)
4) Direct the outcome to tblTid

Is 2) possible?
How does it look “under the shell” of Access? Are the tables organized as a matrix with columns and rows as arrays? In that case it would be possible to do the transpose. If not, this idea halts at 3).

I see that I go to attach a primary key to each row somehow, but first thing first. If it’s not possible to do the above why bother…

 

[gemma-the-husky]

i dont think its too hard, but it depends on the number of columns

lets say your time sheets have at most at most 31 columns (ie 1 for each day per month)

then when you import the spreadsheet to a table, you then know that job x has potentially 31 days worth of labour to load

so either have 31 append queries to pickup each of the potential columns for each job, and run these one after the other (simple code on a button click say) - takes a few minutes to prepare, and then you dont have ot worry about it.

or iterate a recordset, and write code to append all the data (probably more work and slower to run)

i would do it the former way, and i wouldnt try to change tried and tested manual systems - instead just crunch 31 queries, and then demonstrate how much more you can achieve in access. after the data has been captured.