Turning Column Names into field values?

[CazB]

Sorry if this should be posted somewhere else but I couldn't work out where to post it!

Anyway: I have a spreadsheet which contains data exported from another system (which I have no control over). I'm using this spreadsheet as a linked table in my database.

The problem I am having is that I can't guarantee that when the spreadsheet is updated, it will contain the same column headings as it did the last time...

The spreadsheet contains a list of temps, with a summary of info off their submitted timesheets. So the column headings (as well as WorkerName, TimesheetDate, etc) may be "Standard Hour", "Overtime Hour", "Over 12 hrs Hour", "Standard Day", "Overtime Day", etc - for each type there will be a Pay Rate and a number of units (hours or days) claimed. The columns only appear if 'someone' in the spreadsheet has claimed something under that heading this week.

What I need to do is to produce a report which gives a summary by person and week of the number of hours claimed and the total charge. I've done that - that part was easy The part I'm struggling with, is how to take the column headings and turn those into descriptors for each charge type... in otherwords, to go from the sample 'timesheet' below to the sample 'ByType' ?

... when I don't know which columns will be present each week?

At the moment I'm using a union query to pull out the info I need, but if the column headings change then I know it will stop working...

sample of my union query... I currently have 8 different sets of bill rate and charge rate, this just does the first couple...

SELECT qryTimesheetBaseData.[Time Sheet Start Date], qryTimesheetBaseData.[Time Sheet End Date], qryTimesheetBaseData.[Cost Centre], qryTimesheetBaseData.Worker, "Standard" AS RateType, "Hourly" AS RateCategory, qryTimesheetBaseData.[Bill Rate (ST/Hr)] AS Rate, qryTimesheetBaseData.[Time Sheet Billable Hours (ST/Hr)] AS Billable, qryTimesheetBaseData.[Time sheet Status] AS Status
FROM qryTimesheetBaseData
WHERE (((qryTimesheetBaseData.[Time Sheet Billable Hours (ST/Hr)])>0))
UNION SELECT qryTimesheetBaseData.[Time Sheet Start Date], qryTimesheetBaseData.[Time Sheet End Date], qryTimesheetBaseData.[Cost Centre], qryTimesheetBaseData.Worker, "Overtime" AS RateType, "Hourly" AS RateCategory, qryTimesheetBaseData.[Bill Rate (OT/Hr)] AS Rate, qryTimesheetBaseData.[Time Sheet Billable Hours (OT/Hr)] AS Billable, qryTimesheetBaseData.[Time sheet Status] AS Status
FROM qryTimesheetBaseData
WHERE (((qryTimesheetBaseData.[Time Sheet Billable Hours (OT/Hr)])>0))UNION select...

Help?

 

[namliam]

Have a "proper" table ready and waiting with all the possible column names and write an append query dynamicaly using the DAO to loop thru the column names dynamicaly to check if they are there or not.

Or you can open the excel file in excel and check the column names that way using some VBA code.

Then from your proper table you can run all your required reports.

 

[CazB]

I can see how that could work.. the problem is this was supposed to be a 'build it and leave them to it' database - they just run the exports from the other system, then open the database and print off the reports.... don't really want them to be having to run updates on the data in the database.
If there's no other way round it, I guess I'll just have to be there to fix it when columns appear and disappear!

 

[namliam]

Well you can use the same method to (re-write) all the query's reports to only use columns that actually exist....

Or make a "dummy" table in the form of a query that you adjust to add columns dynamicaly when they dont exist and run the reports based on the query instead of straight on the excel sheet.