Seeking elegant SQL for filling a field with a,b,c,d ...

[MLH]

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.

I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?

[Salad]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...

MLH wrote:

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.
>
I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?
>

No.

Is this a homework assignment for school?

I suppose you could use Dcount and Mod to figure it out with a big IIF()
statement by why bother if you can do it with DAO.

Dont Let Him Waste Your Time

http://www.youtube.com/watch?v=c1oMtwmTaNQ

[MLH]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...

On Tue, 27 May 2008 08:00:33 -0700, Salad <oil@vinegar.co mwrote:

>MLH wrote:
>

>I have 27096 records in a table. I'd like to add a [Category]
>field to it and populate it with a, b, c, d then a, b, c, d again
>That would be 6774 entries of a, b, c and d.
>>
>I know how to do it by looping in DAO. But I imagine there's
>an elegant SQL solution. Any takers?
>>

>No.
>
>Is this a homework assignment for school?

No, actually, it's a self-inflicted homework assignment.

>
>I suppose you could use Dcount and Mod to figure it out with a big IIF()
>statement by why bother if you can do it with DAO.
>
>Dont Let Him Waste Your Time
>http://www.youtube.com/watch?v=c1oMtwmTaNQ

Fair question. Biggest reason being this: If you have a saved query
at your fingertips, it's easily modifed to suit specific future
needs when the occasion arises. I realize too, that a procedure
could be written to accept arg's that would make it useful to
handle tables of different names & fields of different names.
The real truth is though, I'd just like to see it.

One guy has posted some awesome SQL in this forum. His
name is John Winterbottom. I never cease to be pleasantly
surprised on reading some of his solutions.

I've already completed the task, BTW. But would still love to
see an SQL-only solution.

[paii, Ron]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...


"MLH" <CRCI@NorthStat e.netwrote in message
news:9o5o34trbn jouqcibhctdlbdo 6tcgjnrck@4ax.c om...

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.
>
I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?
>
>

If you have a counter field or can add an autonumber, the following will
return A,B,C,D.
chr(([iCounter] mod 4)- 1 + asc("A"))

[frogsteaks@yahoo.com]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...

On May 27, 1:42 pm, "paii, Ron" <n...@no.comwro te:

"MLH" <C...@NorthStat e.netwrote in message
>
news:9o5o34trbn jouqcibhctdlbdo 6tcgjnrck@4ax.c om...
>

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.

>

I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?

>
If you have a counter field or can add an autonumber, the following will
return A,B,C,D.
chr(([iCounter] mod 4)- 1 + asc("A"))

That is highly unreliable. You are assuming thinsg that are not
necessarily valid succh as the autonumber being sequential and more
important that there are no number values skipped.

[paii, Ron]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...


<frogsteaks@yah oo.comwrote in message
news:8e45610a-ebb4-4e80-b986-77a0eb344f99@s5 0g2000hsb.googl egroups.com...
On May 27, 1:42 pm, "paii, Ron" <n...@no.comwro te:

"MLH" <C...@NorthStat e.netwrote in message
>
news:9o5o34trbn jouqcibhctdlbdo 6tcgjnrck@4ax.c om...
>

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.

>

I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?

>
If you have a counter field or can add an autonumber, the following will
return A,B,C,D.
chr(([iCounter] mod 4)- 1 + asc("A"))

>That is highly unreliable. You are assuming thinsg that are not
>necessarily valid succh as the autonumber being sequential and more
>important that there are no number values skipped.

If you add a new autonumber field, it will be sequential. After the query is
run you can remove the field. I was assuming this would be a 1 time need to
fill a field with a,b,c or d.

[MLH]

Re: Seeking elegant SQL for filling a field with a,b,c,d ...

You are correct, Ron.
The SQL would be a 1-time need.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx


On Wed, 28 May 2008 15:22:47 -0500, "paii, Ron" <none@no.comwro te:

>
><frogsteaks@ya hoo.comwrote in message
>news:8e45610 a-ebb4-4e80-b986-77a0eb344f99@s5 0g2000hsb.googl egroups.com...
>On May 27, 1:42 pm, "paii, Ron" <n...@no.comwro te:

>"MLH" <C...@NorthStat e.netwrote in message
>>
>news:9o5o34trb njouqcibhctdlbd o6tcgjnrck@4ax. com...
>>

I have 27096 records in a table. I'd like to add a [Category]
field to it and populate it with a, b, c, d then a, b, c, d again
That would be 6774 entries of a, b, c and d.

>>

I know how to do it by looping in DAO. But I imagine there's
an elegant SQL solution. Any takers?

>>
>If you have a counter field or can add an autonumber, the following will
>return A,B,C,D.
>chr(([iCounter] mod 4)- 1 + asc("A"))

>

>>That is highly unreliable. You are assuming thinsg that are not
>>necessarily valid succh as the autonumber being sequential and more
>>important that there are no number values skipped.

>
>If you add a new autonumber field, it will be sequential. After the query is
>run you can remove the field. I was assuming this would be a 1 time need to
>fill a field with a,b,c or d.
>