SQL Statement

i have a table that holds an error number in it this error number can be one
of many. at the moment only a few of the numbers can be submitted for
deletion i could use the OR statement but the SQL could reach 20 different
OR parts to the statement and these Error Numbers total 100. Is there any
way of creating another table that could be used to create a RS? if this is
not possible then that is ok, i have created a SQL statement to do what i
want. But when this RS i viewed and agreed i would like to be able to update
a field in each of the records selected stating that this one has been added
to a deletion schedule?

Please Help My Head is Mashed!!!

On Sat, 3 Jul 2004 08:57:54 +0100, "Gareth" <gubbins (AT) hotmail (DOT) com> wrote:

i have a table that holds an error number in it this error number can be one
of many. at the moment only a few of the numbers can be submitted for
deletion i could use the OR statement but the SQL could reach 20 different
OR parts to the statement and these Error Numbers total 100. Is there any
way of creating another table that could be used to create a RS? if this is
not possible then that is ok, i have created a SQL statement to do what i
want. But when this RS i viewed and agreed i would like to be able to update
a field in each of the records selected stating that this one has been added
to a deletion schedule?

I'm not sure I understand, but how about the IN predicate?

SELECT * FROM yourtable WHERE errornumber IN (1,2,3,...,n)

If you really want to use another table:

SELECT * FROM yourtable WHERE errornumber IN (SELECT ...)


Gary

"Gary White" <reply (AT) newsgroup (DOT) please> wrote

On Sat, 3 Jul 2004 08:57:54 +0100, "Gareth" <gubbins (AT) hotmail (DOT) com> wrote:

i have a table that holds an error number in it this error number can be
one
of many. at the moment only a few of the numbers can be submitted for
deletion i could use the OR statement but the SQL could reach 20 different
OR parts to the statement and these Error Numbers total 100. Is there any
way of creating another table that could be used to create a RS? if this
is
not possible then that is ok, i have created a SQL statement to do what i
want. But when this RS i viewed and agreed i would like to be able to
update
a field in each of the records selected stating that this one has been
added
to a deletion schedule?


I'm not sure I understand, but how about the IN predicate?

SELECT * FROM yourtable WHERE errornumber IN (1,2,3,...,n)

If you really want to use another table:

SELECT * FROM yourtable WHERE errornumber IN (SELECT ...)


Gary

Thanks Gary you did indeed but do you have any idea how with this RS created
if i agree i push a button and a field is updated to show that all these
have been submitted for deletion, this is so they can be filtered and
redeleted

Cheers

Gareth

i meant to say NOT redeleted

"Gary White" <reply (AT) newsgroup (DOT) please> wrote

On Sat, 3 Jul 2004 08:57:54 +0100, "Gareth" <gubbins (AT) hotmail (DOT) com> wrote:

i have a table that holds an error number in it this error number can be
one
of many. at the moment only a few of the numbers can be submitted for
deletion i could use the OR statement but the SQL could reach 20 different
OR parts to the statement and these Error Numbers total 100. Is there any
way of creating another table that could be used to create a RS? if this
is
not possible then that is ok, i have created a SQL statement to do what i
want. But when this RS i viewed and agreed i would like to be able to
update
a field in each of the records selected stating that this one has been
added
to a deletion schedule?


I'm not sure I understand, but how about the IN predicate?

SELECT * FROM yourtable WHERE errornumber IN (1,2,3,...,n)

If you really want to use another table:

SELECT * FROM yourtable WHERE errornumber IN (SELECT ...)


Gary

"Gareth" <gubbins (AT) hotmail (DOT) com> wrote

Thanks Gary you did indeed but do you have any idea how with this RS
created
if i agree i push a button and a field is updated to show that all these
have been submitted for deletion, this is so they can be filtered and
redeleted

You'll have to tell me a little more. I was on the edge of understanding
from the first post and kinda guessed what might work. To take it further,
I'll need more of an explanation. What scripting language/database are you
using? What do you want to do with the records? Are you just marking a field
to indicate they are deleted, or are you actually going to delete them from
the table?

Gary

"Gary White" <reply (AT) newsgroup (DOT) please> wrote

"Gareth" <gubbins (AT) hotmail (DOT) com> wrote in message
news:cc6and$qaq$1 (AT) forums (DOT) macromedia.com...

Thanks Gary you did indeed but do you have any idea how with this RS
created
if i agree i push a button and a field is updated to show that all these
have been submitted for deletion, this is so they can be filtered and
redeleted


You'll have to tell me a little more. I was on the edge of understanding
from the first post and kinda guessed what might work. To take it further,
I'll need more of an explanation. What scripting language/database are you
using? What do you want to do with the records? Are you just marking a
field
to indicate they are deleted, or are you actually going to delete them
from
the table?

Gary

what happens is say error number E1049 has 20 matches each of those are
submitted then i have to wait upto four weeks for them to be deleted. But in
between now and then i might get some more errors that need to submitted so
what i think i should do is have a field that i can use to filter records
that have previously been subitted thus stopping a possible deletion of the
record when it is working again. Hope that helps.

Gareth

Access 2003
ASP/VB Script

"Gareth" <gubbins (AT) hotmail (DOT) com> wrote

what happens is say error number E1049 has 20 matches each of those are
submitted then i have to wait upto four weeks for them to be deleted. But
in
between now and then i might get some more errors that need to submitted
so
what i think i should do is have a field that i can use to filter records
that have previously been subitted thus stopping a possible deletion of
the
record when it is working again. Hope that helps.

That should work. Just add a field, maybe called "Pending" or something.
Make it a Boolean, or Yes/No or whatever Access calls it. Then, once you
have your SQL statement with an IN(1,2,...,n), you could use that same
predicate:

UPDATE yourtable SET Pending=TRUE WHERE errornumber IN (1,2,3,...,n)

Gary

thanks for that! i think i understand what you mean if not will repost!

Cheers

Gareth
"Gary White" <reply (AT) newsgroup (DOT) please> wrote

"Gareth" <gubbins (AT) hotmail (DOT) com> wrote in message
news:cc6cch$s1d$1 (AT) forums (DOT) macromedia.com...

what happens is say error number E1049 has 20 matches each of those are
submitted then i have to wait upto four weeks for them to be deleted. But
in
between now and then i might get some more errors that need to submitted
so
what i think i should do is have a field that i can use to filter records
that have previously been subitted thus stopping a possible deletion of
the
record when it is working again. Hope that helps.


That should work. Just add a field, maybe called "Pending" or something.
Make it a Boolean, or Yes/No or whatever Access calls it. Then, once you
have your SQL statement with an IN(1,2,...,n), you could use that same
predicate:

UPDATE yourtable SET Pending=TRUE WHERE errornumber IN (1,2,3,...,n)

Gary

"Gareth" <gubbins (AT) hotmail (DOT) com> wrote

thanks for that! i think i understand what you mean if not will repost!

Good luck!

Gary