Selecting variables automatically in GROUP BY query

[deepakkumars]

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,

SELECT EMP_ID, SUM(sales_week1) as week1, SUM(sales_week2) as week2, SUM(sales_week3) as week3
FROM table1
group by EMP_ID;

next week, when i add another variable Sales_week4. The query should update to:

SELECT EMP_ID, SUM(sales_week1) as week1, SUM(sales_week2) as week2, SUM(sales_week3) as week3, SUM(sales_week4) as week4
FROM table1
group by EMP_ID;

I'm not sure how to automate this in Access.

Any help on this is appreciated.


Regards,
-Deepak

[beacon]

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.

[NeoPa]

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).

[deepakkumars]

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??

[NeoPa]

** 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 :

DoCmd.RunSQL {SQL string value}

For a SELECT query you would need to update the QueryDef before running it.

[FishVal]

@deepakkumars

• 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!!!