repetitive work

[Jeff98]

I have a query in excess


SELECT Col1, count(id) AS [count]
FROM table1
GROUP BY col1;

I have about fourty columns in table1. and I need to run this sql statements to all columns. It is tedious to change col1 fourty times. Is there a good way to do that more efficiently?
THX

 

[Fenrirshowl]

Jeff

Are you talking about Access? (as I've not heard of excess)

Either way, given the forum you're in I assume you have VBA available to you. In which case have you read thread707-1291762 to illustrate the DoCmd.RunSQL method? You should be able to set a loop to generate your SQL string based on an integer parameter from 1 to 40.

 

[Jeff98]

In another thread, i was told Docmd.RunSQL can not handle Select, because it is not a action sql instruction. My question is how can i write a program to run several sqls (select) in one file? (I know if I wrote 40 sql with same structure with different filed. in the Acccess interface, i can do it. but it is too tedious)

Thanks

 

[stevexff]

Excess n. A proprietary combined spreadsheet and database package that never really caught on.

Cool Freudian slip, though...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]