How to change headers?

[wharting]

Hi,

Every month I download data from a server which I import into a Access DB for trend analyzis. The header of the download changes every month.
For example: fields 1,2, 3 and 4 for last month are "2011-4", "2011-5", "2011-6" and "2011-7".
This month it will be "2011-5", "2011-6", "2011-7" and "2011-8".
The fields contain amounts.

Question: The name of the (current)fields need to be displayed in a Access PivotChartForm. But how do I (automatically)change the header of the database into the actual fieldnames according to the download fieldnames?
Many thanks in advance.

Best regards,
Willem

[June7]

Not sure what you mean by 'change the header of the database'. You import the data into a new table? What method do you use for the import?

[wharting]

Hi June7,

Thanks for your respons.

Normally I import a CSV download the normal way. But in this case the headers of the involved download changes every month.
When I download the CVS and import it into the excisting table, i get an error because the table doesn't recognize the fields.

The download shows in some (4) fields the costs per month (4 fields, 4 months) for phonecalls made by employees. Every month the header changes; they move on.
The best option should be to create a new table every month I download the data. But I don't know how to create a (new) table out of nothing, using the downloaded CSV (including headerrow) by using a commandbutton.

I hope that you understand what I mean.

Best regards,
Willem

[June7]

Use DoCmd.TransferText

Review
http://bytes.com/topic/access/answer...ile-access-vba
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[rpeare]

If you don't care about preserving the column headers you can set your import options to exclude the first row (first row contains field names). I'm assuming you do not care what the columns are lableed though otherwise you'd be redoing your queries every month.

[wharting]

@June7: I'ff tried this but doesn't give the wanted result.

@rpeare: that is just the problem. The headers must be visible for management purposes as trend analizes. If I was the only one that uses the database it wasn't a problem at all.
It's because other users with less knowledge of access who have to work with it.

The needed data is present in other tables too but just for one month. If there's another solution to create an Excel file for trend analizing purposes I would be glad.

Best regards,
Willem

[rpeare]

Ok, I think I would be inclined to change your import procedure so that you DID NOT have to change your queries every month because your table would end up with new field names.

What I would do is import the data (including the field names) into an all text table with columns A, B, C, D (for example) then run a query that would append the information to a normalized table in the format then you can do whatever you want with it (since you specifically mentioned queries it's not a big deal to create one.

Enclosed is an example. I don't know what the end product of this data is going to be, whether it will be cost by employee, but the method is sound. You may want to make changes and only append NEW months as you go along. For instance if you are getting this data after a month is complete you will be getting three months of duplicate information each time you import data which is kind of a waste. If you're only interested in adding 'new' data to your table that's a different matter but can be accomplished in much the same way.

In my example I have already imported one of the .csv files (Wharting1.csv). In order to get this to run correctly you'll have to put the database in a trusted location (it uses an import spec) Open the import form and click the second file name then click the import button and it will add to the existing table. Then you can run the crosstab query for an example. Again, I don't know what your final output is supposed to look like but you can do anything with your data once it's normalized.

[wharting]

Hi rpeare,

Reading your reply this could be the solution.
Next Monday I'll try to implement this in my database; I can't login in the companyserver this weekend. Offcourse I'll keep you informed.

Best regards,
Willem

[wharting]

Hi rpeare,

Just looked at it and it seems that this solves my 'problem'. Great!
I'll change the routine (names and so on) so I can use it in my database.
Many thanks for your help. Many thanks for June7 to.

Best regards,
Willem

[wharting]

Hi Rpeare,

May I ask for some help again?
Your solution solves mij problem, that's for sure.
But, adding colomns seems to be a problem (for me then).

Enclosed an Excel sheet with data including the headerrow.
The headerrow changes every month as I told before.
It has to be changed into a table with the same header.

Your solution works fine but changing and/or adding things give just more problems. Many thanks in advance.

Best regards,
Willem

[wharting]

Oops! I forgot the attachment.

[rpeare]

You'll have to modify my code because you're using a different number of columns than I used. You have to modify the tblRawImport table to handle all the rows you're trying to accommodate, the first three fields can be permanent field names but next four columns are the ones you want to and you'll have to modify tblImportedData as well, to handle the additional 'description' fields (company, service, customer etc). You'll also have to modify the code behind the Import Information button because I'm only doing the amounts, you'll have to add the additional fields to the SQL statement that appends to tblImportedData. Your file is also semi-colon delimited, not comma delimited so you will need to change the import spec as well.

[wharting]

Ok, thanks.
I'll keep you informed.

[wharting]

I've tried to changes the code. I made it sofar that I can import the data into de tblRawImport table. But the 'Insert into' routine gives an syntaxerror on 'db. Excecute sSQL'; see jpg.
Offcourse I'm doing something wrong but can't figure what.

[rpeare]

Well there are a *bunch* of problems with your modifications.

First, you're using spaces in your field names:

Cost Type
Estimated Cost at the End of the Year

Spaces or special characters (#, %, &, etc) are a big problem in field/object names within access databases. Underscores (_) are the only exception.

Second this is my original SQL statement:

sSQL = "INSERT INTO tblImportedData (MonthLabel, MonthAmount) VALUES ('" & sMonthLabel1 & "'," & rst.Fields("FieldA") & ")"

One time for each of the value fields.

sSQL = "INSERT INTO tblImportedData (Company, Debtor, [Cost Type], MonthLabel, MonthAmount, Estimated Cost At The End Of Year) VALUES ('" & sMonthLabel1 & "'," & rst.Fields("Company") & ")"

This is your modified statement. You are adding company, debtor, cost type but you are not property but you only have 2 values in your values list. The order of the fields have to be the same in both parts of the SQL statement as well, you're inserting the month label and the company field but no value and the two fields you're trying to add are out of order.

Lastly and probably most importantly, is the same question I asked before. When you get a new spreadsheet you are getting four months at a time. 3 of which are repeated from the previous month is that data in the three common months identical? or does it change each month?

If it doesn't change you don't need the estimated cost field at all when you import the data. You can calculate it if you're doing a simple projection. For instance if they have 10,000 in usage in january of a given year their projection would be 120,000 for the end of the year (cost per month * 12 months). if you are just adding new data and the 'old' data from the previous months does not change the import procedure has to be modified and probably the tblImportedData as well.