Selecting variables automatically in GROUP BY query

Hi All:

I'm using Access 2003 on Windows XP.

I'm creating a table where i'll be adding a new variable every week (based on the week number). This is a numeric variable. Once the variable is inserted, i need to run a GROUP BY query based on other character variables. For instance, i'm having a table as:

EMP_ID
Sales_week1
Sales_week2
Sales_week3,

my SQL query will be,
next week, when i add another variable Sales_week4. The query should update to:
I'm not sure how to automate this in Access.

Any help on this is appreciated.


Regards,
-Deepak

re: Selecting variables automatically in GROUP BY query

I'm just curious if there's a reason why you wouldn't include another field in the table for the date at the beginning of the week instead of applying the literal "Week 1" to each week's numbers.

I'm also not sure whether your setup is ideal for what you're trying to do. Is EMP_ID the name of the table? If so, why not call this the SALES_BY_WEEK table? Then your fields would be:

Emp_ID | Sales | Week
0001 $2,000 03/01/2009
0002 $3,000 03/01/2009
0001 $1,000 03/08/2009

Then, if you really wanted to have "Week 1", you could write an expression for a field in your query that recognizes 03/01/2009 as "Week 1" and then increments the number for each subsequent week.

I could be way off from what you are wanting, so forgive me if it's not.

re: Selecting variables automatically in GROUP BY query

The difficulty you are experiencing is a good illustration of why the approach you're taking is never advised.

I suggest you consider redesigning the data organisation of you database. Here's a very useful link to help (Normalisation and Table structures).

re: Selecting variables automatically in GROUP BY query

To re-structure the table will be a problem as there are more 150,000 records in the table. So, if I add a variable as week and start appending the table, the size of the file will be really huge.

I was thinking if i could create a string using some kind of macros, the string would basically contain the SQL query and somehow run this string as query.

Is it possible??

re: Selecting variables automatically in GROUP BY query

** WARNING - This is a very bad idea **

Never say I didn't warn you. Even when things get horribly messy for you down the line.

Having said that, it is possible.

QueryDefs have a .SQL property which can be accessed via VBA code in your database. They can even be updated in the same way (using the .SQL property). A SQL string can be run as a query (a limitation is that only action queries can be run this way) by using :
For a SELECT query you would need to update the QueryDef before running it.

re: Selecting variables automatically in GROUP BY query

• Records count doesn't matter.
• File size will be of the same if not a smaller size.
• If you could create "some kind of macros", then you certainly could create "some kind of queries" to transfer existing data to a normal table schema.
• ??????
• PROFIT!!!