Table Field headers differ from report to report

[RCBricker]

Hello all. I have a set of reports that I need to import into access from excel. However, the field positions in the excel document tend to vary based on who is running the report. So field1 SalesPrice maybe in column A in November's report but in column C in December's report.

I have no control on how the report is run nor can I require the reportee to use a standardized template. Is there an easier way to get ACCESS to pull the data into the table based on field header name rather than field position?

If yes, then what about varying name? The same report may undergo maintenance by the reportee entity and a field header could change mid-year (happen in this calendar year) SalesPrice in November became Sales_Price in February. Any way to associate those two names as meaning the same field?

I am trying to put this database together so that my team can import and run reports without too much monkeying around. Their level of tech is sub-standard at best.

Thanks,

Rich

 

[Micron]

without too much monkeying around

Too late! There is already too much of that going on, or else you would not be in this position.
Respectfully, the notion that your colleagues cannot be disciplined enough to adhere to a standard does not reflect well on them. I too have had to deal with people who could not even create a shortcut on their Windows desktop, but they could be bound by the constraints of a data input form or template.

I would say you could deal with the position of the fields by linking the workbook(s) in Access (or export/import between Excel and Access) and let your queries figure out where they are. Or you could deal with the changing names by relying on code to get the values from each field(#n) regardless of their names. But to ask for both is to ask for the impossible, IMHO. Maybe your data should be entered using Access so you can exert tighter control over field names and positions as well as what's allowed to be put in and what's not allowed to be left out, since you're going to use it anyway. You could still use Excel with this data if you still need its capabilities for charting or whatever.

 

[RCBricker]

Too late! There is already too much of that going on, or else you would not be in this position.
Respectfully, the notion that your colleagues cannot be disciplined enough to adhere to a standard does not reflect well on them. I too have had to deal with people who could not even create a shortcut on their Windows desktop, but they could be bound by the constraints of a data input form or template.

I would say you could deal with the position of the fields by linking the workbook(s) in Access (or export/import between Excel and Access) and let your queries figure out where they are. Or you could deal with the changing names by relying on code to get the values from each field(#n) regardless of their names. But to ask for both is to ask for the impossible, IMHO. Maybe your data should be entered using Access so you can exert tighter control over field names and positions as well as what's allowed to be put in and what's not allowed to be left out, since you're going to use it anyway. You could still use Excel with this data if you still need its capabilities for charting or whatever.

Thanks for the reply Micron. Unfortunately we get these reports from outside our company. Which is why I have no control over those running the reports. Trust me if I could get to them, I would train them properly or...not sure maybe wear orange the rest of my life.

That said, A co-worker suggested using table look ups that would allow me to associate a standardized field name with the various field name that I am seeing coming through the reports. That way during the import process code would run an append query and change the names of the important fields to a standard name. That might work.

rich