[AccessD] Array as source for query

Oh yea, we like double the pay plus benes.

Congratulations!

Sometimes it's hard to understand why we took so long to make the change
when we look back on things.  I am only now doubling what I made back in
1993 when I lost my last "real job", but I am a consultant now, working from
my home office every day, doing all of my work over the internet.  Because I
was able to "up and move" and bring my clients with me, my cost of living is
about probably about 25% less than it was last year this time.  I love what
I do now, and I love HOW I do it even more.

Sometimes things work out, and it sounds like they are finally working out
for you too.  Again, congratulations and keep dropping in.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Monday, January 22, 2007 1:01 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Array as source for query

Gustav/John:

Thanks for the welcomes back.  I have not really been gone since I'd never
unsubscribed.  Regrettably I've only read 1 post in 50.

There has been little time for new Access work in the past year though my
services are still required for maintenance.  I find myself in an employment
role that requires travel throughout BC, Alberta and Saskatchewan 3 weeks
out of the month, yet still allows me to be home nearly every weekend.  The
best parts about the job are that it already pays over double what I used to
make with Access with tons of benefits, like excellent medical, new vehicles
and all fuel expenses yet it gives me more time to myself.

I will dabble here a bit more again and hopefully learn something new.

Ciao
J|rgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Gustav Brock" <Gustav at cactus.dk>
>
>Hi J|rgen
>
>What a nice surprise. I thought you had "left the building".
>
>Yes, it can be done this way. Another use of a union query I haven't 
>seen before.
>
>/gustav
>
> >>> jwelz at hotmail.com 22-01-2007 01:49 >>>
>I have used arrays to populate small queries from time to time.  I 
>looped through the array generating an SQL string and used any table 
>name in the database.  Any system usys... or other conventional table, 
>whether it had records in it or not sufficed.  The string generated was
along the lines:
>
>"Select " & ar(0) & " as A From usysTable Union Select " & ar(1) & " as 
>A
> >From usysTable Union Select " & ar(2) ....
>
>The loop iterated Array as with an incrementing index counter.  
>Typically the loop would include the 'Union' in each loop and parse out 
>the initial 'Union' with a Mid$(strsql, 6) when it exited.  You have to 
>watch Query string length limitations with this approach.  Sure you 
>need a table, but any of the built in system tables works, though you 
>can define a table with a short name and lose the alias to maximise the
number of array 'records'.
>
>Ciao
>J|rgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com

_________________________________________________________________
http://ideas.live.com/programpage.aspx?versionid=b2456790-90e6-4d28-9219-5d7
207d94d45&mkt=en-ca