Creating Field Names in a query

[Gringarlow]

Hi again, I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.
Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?

 

[boblarson]

Hi again, I am hoping to create a field name in a query that will change every month. Right now the filed name is qryTechQuintileMonth-7.am_quintile. this designates that the data is for October 2012. There are 6 other fields named similarly for Nov 2012 through April 2013.
Is there a way to name these fields with the proper month-year (mmm-yy) so they automatically update each month?

You would need to build the SQL in VBA to do this. Do you want to go there? If so, I'll post code to do it.

 

[Pat Hartman]

Your problem is that you are attempting to use Access as a spreadsheet. Relational databases do not work like spreadsheets. The similar appearance in table view is deceiving but they are quite different.

Rather than having colums for each month (ever changing ones at that), you should have rows. Once your table is normalized so that it has a row for each month, criteria can be used to retrieve the set of data you need and if you really want to see it as you did in Excel, you can use the crosstab query wizard to build a view for you.

Before you proceed any further, stop and do some reading on normalization. it will ultimately save you a whole lot of work if you get your table design correct from the start.

 

[Gringarlow]

Ok, i'll read up on Normalizing. Thanks

 

[Gringarlow]

You would need to build the SQL in VBA to do this. Do you want to go there? If so, I'll post code to do it.

Yes, I would appreciate that. Thanks