Weeks Conversion

[jake32008]

Hi everybody,

I'm a newer user of Access and am learning it in order to better manage sales & inventory for a major retailer. The retailer's uses a week system such as 201201, 201202, etc. and when I pull a report from their system that I intend to use in Access the field in Access will be 201201 POS QTY, 201202 Forecast, etc. I would like to be able to change the field names to just be the week (I know if it's the POS QTY or Forecast by the table I'm in) so that I can compare different data. I update these reports at least weekly if not daily and I would like to not have to manually change the field name each time for 52+ weeks. So my question is, how can I change the field names on these tables en masse and not have to do this every time I update the tables?

Thank you for your help,



Jake

[alansidman]

Are you importing data into tables and then having to change the names of the fields? If this is the case, create your table with the names of the fields you wish to have. Import your data to a temp table and run an update query to place your new data in the template table that contains the proper field names.

http://www.databasedev.co.uk/update_query.html

[June7]

I am glad you recognize that having multiple similar named fields, especially with a date component, is not appropriate design. Having to modify db design as time marches on is a maintenance nightmare.

Looks like this is an import that the Wizard and TransferText might not handle well and a customized import procedure could be needed. Common topic, search forum or Google: Access VBA import text file open for input

http://answers.microsoft.com/en-us/o...b-64a8e146a7e9
http://forums.aspfree.com/microsoft-...le-350209.html

[jake32008]

Alansidman, I'll give this a shot and let you know. Thank you!

June7, I still have a lot to learn about importing and database management in general so I appreciate the resources!