SQL Help

[Ann]

I tried this query gives me
Syntax error converting the varchar value '0.25' to a
column of data type int.

Please let me know

Ann

>-----Original Message-----
>This should do the trick:
>
>UPDATE VarReport
>SET [DATE] = DATEADD(mi, MyTime/100*60 + MyTime%100,

CONVERT(datetime,

>MYDATE, 112))
>
>You can check out the DATEADD and CONVERT functions in

Books online.

>
>
>"Ann" <annub_76@yahoo.com> wrote in message
>news:25f801c33f2d$0535dc80$a601280a@phx.gbl...

>> My table looks something like this
>> CREATE TABLE [dbo].[VarReport] (
>> [EMPLOYEE_NO] [nvarchar] (255) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [SEQUENCE] [float] NULL ,
>> [DATE] [datetime] NULL ,
>> [MYTIME] [varchar] (4) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [MYDATE] [varchar] (14) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [TIME] [float] NULL ,
>> [TYPE] [nvarchar] (255) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [total_hrs] [int] NULL
>> ) ON [PRIMARY]
>> GO
>>
>> I need to get in and out time for each employee when

they

>> scan their id's. Data i get in txt date = 20030120 and
>> time - 1514 in this formate. To change in right formate

i

>> want to update DATE (Datetime) col with MYTIME and

MYDATE

>> col. I am writting this query, but gives me error "

Syntax

>> error converting datetime from character string. " My
>> Query is
>>
>> update varreport set
>> tc03_date = mydate + ' '+ substring(right(('000' +
>> mytime),4), 1,2)+ ':' + substring(right(('000' +
>> mytime),4), 3,2)
>>
>> update varreport set tc03_date = mydate + ' '
>> + substring(right(('000' + mytime),4), 1,2)+ ':' +
>> substring(right(('000' + mytime),4), 3,2)
>>
>> Thanks
>> Ann
>>

>
>
>.
>

[Jacco Schalkwijk]

Then you have to cast MyTime as an INT first:

UPDATE VarReport
SET [DATE] = DATEADD(mi, CAST(MyTime AS INT)/100*60 + CAST(MyTime AS
INT)%100, CONVERT(datetime, MYDATE, 112))

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Ann" <annub_76@yahoo.com> wrote in message
news:0c1e01c33fd7$ef6a5e70$a501280a@phx.gbl...

> I tried this query gives me
> Syntax error converting the varchar value '0.25' to a
> column of data type int.
>
> Please let me know
>
> Ann
>

> >-----Original Message-----
> >This should do the trick:
> >
> >UPDATE VarReport
> >SET [DATE] = DATEADD(mi, MyTime/100*60 + MyTime%100,

> CONVERT(datetime,

> >MYDATE, 112))
> >
> >You can check out the DATEADD and CONVERT functions in

> Books online.

> >
> >
> >"Ann" <annub_76@yahoo.com> wrote in message
> >news:25f801c33f2d$0535dc80$a601280a@phx.gbl...

> >> My table looks something like this
> >> CREATE TABLE [dbo].[VarReport] (
> >> [EMPLOYEE_NO] [nvarchar] (255) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [SEQUENCE] [float] NULL ,
> >> [DATE] [datetime] NULL ,
> >> [MYTIME] [varchar] (4) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [MYDATE] [varchar] (14) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [TIME] [float] NULL ,
> >> [TYPE] [nvarchar] (255) COLLATE
> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> [total_hrs] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> I need to get in and out time for each employee when

> they

> >> scan their id's. Data i get in txt date = 20030120 and
> >> time - 1514 in this formate. To change in right formate

> i

> >> want to update DATE (Datetime) col with MYTIME and

> MYDATE

> >> col. I am writting this query, but gives me error "

> Syntax

> >> error converting datetime from character string. " My
> >> Query is
> >>
> >> update varreport set
> >> tc03_date = mydate + ' '+ substring(right(('000' +
> >> mytime),4), 1,2)+ ':' + substring(right(('000' +
> >> mytime),4), 3,2)
> >>
> >> update varreport set tc03_date = mydate + ' '
> >> + substring(right(('000' + mytime),4), 1,2)+ ':' +
> >> substring(right(('000' + mytime),4), 3,2)
> >>
> >> Thanks
> >> Ann
> >>

> >
> >
> >.
> >

[Joe Celko]

I think your real problem is that this table is a nightmare of bad
design and needs to be redone. Consider the current design, data
elemetn by data element:

There is no key and there cannot be a key because all the columns are
NULL-able. This is not a table at all.

employee_no NVARCHAR(255) NULL,
Do you really have an employee number which uses that many foreign
phrases? That is long enough to write poetry in French! And where did
the "magic number" 255? Certainly not from careful design.

sequence FLOAT NULL,
Look up the meaning of the word "sequence" - it is integers. Also, s
sequence of what? Read ISO -11179 for help with naming data elements

date DATETIME NULL,
DATE is a reserved word in SQL-92 and it is too vague to be a data
element name.

mytime VARCHAR(4) NULL,
Why not use the temporal datatypes in SQL?

mydate VARCHAR(14) NULL,
Why not use the temporal datatypes in SQL?

time FLOAT NULL,
TIME is a reserved word in SQL-92 and it is too vague to be a data
element name. And it is not a FLOAT.

type NVARCHAR(255) NULL,
type of what??

total_hrs INTEGER NULL
Why are you storing a computed column in a table? Tom Johnston had a
nice set of articles on this kind of redundancy that you might want to
look up.

Bad schemas lead to horrible queries and kludges. And people here will
give you kludges to work around the problems you have created. It is
not a good way to program SQL, tho.

Get an employee identifier that can be verified, post start and stop
times for an event, add checks and defaults. In short, start over and
do it right this time.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Jacco Schalkwijk]

It works with the table design and the sample data you posted. There is
probably bad data in one of your columns that you need to clean out first.
As Joe pointed out, you can't expect anything to work properly if there is
such a lack of design. '0.25' is a perfectly valid entry in either the
MYTIME or MYDATE column in this design, as is 'Ann' and '&^%$' but they
don't make a valid number of minutes or a valid date.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Ann" <annub76@yahoo.com> wrote in message
news:313201c33fe2$ad7bdb00$a601280a@phx.gbl...

> I tried again... same error.. Thanks for your help
>
>

> >-----Original Message-----
> >Then you have to cast MyTime as an INT first:
> >
> >UPDATE VarReport
> >SET [DATE] = DATEADD(mi, CAST(MyTime AS INT)/100*60 + CAST

> (MyTime AS

> >INT)%100, CONVERT(datetime, MYDATE, 112))
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
> >"Ann" <annub_76@yahoo.com> wrote in message
> >news:0c1e01c33fd7$ef6a5e70$a501280a@phx.gbl...

> >> I tried this query gives me
> >> Syntax error converting the varchar value '0.25' to a
> >> column of data type int.
> >>
> >> Please let me know
> >>
> >> Ann
> >>
> >> >-----Original Message-----
> >> >This should do the trick:
> >> >
> >> >UPDATE VarReport
> >> >SET [DATE] = DATEADD(mi, MyTime/100*60 + MyTime%100,
> >> CONVERT(datetime,
> >> >MYDATE, 112))
> >> >
> >> >You can check out the DATEADD and CONVERT functions in
> >> Books online.
> >> >
> >> >
> >> >"Ann" <annub_76@yahoo.com> wrote in message
> >> >news:25f801c33f2d$0535dc80$a601280a@phx.gbl...
> >> >> My table looks something like this
> >> >> CREATE TABLE [dbo].[VarReport] (
> >> >> [EMPLOYEE_NO] [nvarchar] (255) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [SEQUENCE] [float] NULL ,
> >> >> [DATE] [datetime] NULL ,
> >> >> [MYTIME] [varchar] (4) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [MYDATE] [varchar] (14) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [TIME] [float] NULL ,
> >> >> [TYPE] [nvarchar] (255) COLLATE
> >> >> SQL_Latin1_General_CP1_CI_AS NULL ,
> >> >> [total_hrs] [int] NULL
> >> >> ) ON [PRIMARY]
> >> >> GO
> >> >>
> >> >> I need to get in and out time for each employee when
> >> they
> >> >> scan their id's. Data i get in txt date = 20030120

> and

> >> >> time - 1514 in this formate. To change in right

> formate

> >> i
> >> >> want to update DATE (Datetime) col with MYTIME and
> >> MYDATE
> >> >> col. I am writting this query, but gives me error "
> >> Syntax
> >> >> error converting datetime from character string. " My
> >> >> Query is
> >> >>
> >> >> update varreport set
> >> >> tc03_date = mydate + ' '+ substring(right(('000' +
> >> >> mytime),4), 1,2)+ ':' + substring(right(('000' +
> >> >> mytime),4), 3,2)
> >> >>
> >> >> update varreport set tc03_date = mydate + ' '
> >> >> + substring(right(('000' + mytime),4), 1,2)+ ':' +
> >> >> substring(right(('000' + mytime),4), 3,2)
> >> >>
> >> >> Thanks
> >> >> Ann
> >> >>
> >> >
> >> >
> >> >.
> >> >

> >
> >
> >.
> >

[keith]

Hi

I have a table with columns/rows as follows:
tab1
abc|1|0||0
abc|||0|2
abc||3||0
abc|0|0||4
abc||0|5|0

column 1 always has abc or xyz. I need to get one row for each unique column 1.
For example, I would need one row: abc|1|2|3|4|5
Apprecitate any help on this query.
V7.2 FP7

Thanks.

[Darren Heinrich]

I need to search multiple keywords across multiple fields.

If the keywords entered are: "one" & "two" I need an SQL statement that
reads something like

Where fieldName contains "one" & "two" or fieldName2 contains "one" & "two"

Get my drift ?

Then I need to dynamically build which fields to search - I can worry about
that later.

Thanks

Darren

[Bob Barrows]

Darren Heinrich wrote:

> I need to search multiple keywords across multiple fields.
>
> If the keywords entered are: "one" & "two" I need an SQL statement
> that reads something like
>
> Where fieldName contains "one" & "two" or fieldName2 contains "one" &
> "two"
>

WHERE fieldname like '%one%two%' or ...

If you are using SQL Server, you should probably look into full text
searching.

HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Darren Heinrich]

thanks I'll give it a try

Darren


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:utRywJF7DHA.3052@TK2MSFTNGP09.phx.gbl...

> Darren Heinrich wrote:

> > I need to search multiple keywords across multiple fields.
> >
> > If the keywords entered are: "one" & "two" I need an SQL statement
> > that reads something like
> >
> > Where fieldName contains "one" & "two" or fieldName2 contains "one" &
> > "two"
> >

> WHERE fieldname like '%one%two%' or ...
>
> If you are using SQL Server, you should probably look into full text
> searching.
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Aaron Bertrand [MVP]]

keywords = trim(request.form("keywords"))
if keywords <> "" then
keywords = replace(keywords, "'", "''")
kwList = split(keywords)
for i = 0 to ubound(kwList)
postSql = postSql & " AND (column1 LIKE '%" & _
kwList(i) & "%' OR column2 LIKE '%" & _
kwList(i) & "%')"
next
end if
sql = "SELECT columns FROM table WHERE 1=1 " & postSql
response.write sql

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Darren Heinrich" <dazzjazz@ozemail.com.au> wrote in message
news:ysBUb.7$J9.1479@nnrp1.ozemail.com.au...

> I need to search multiple keywords across multiple fields.
>
> If the keywords entered are: "one" & "two" I need an SQL statement that
> reads something like
>
> Where fieldName contains "one" & "two" or fieldName2 contains "one" &

"two"

>
> Get my drift ?
>
> Then I need to dynamically build which fields to search - I can worry

about

> that later.
>
> Thanks
>
> Darren
>
>

[Bullschmidt]

<<
I need to search multiple keywords across multiple fields.

If the keywords entered are: "one" & "two" I need an SQL statement that
reads something like

Where fieldName contains "one" & "two" or fieldName2 contains "one" &
"two"

Get my drift ?

Then I need to dynamically build which fields to search - I can worry
about
that later.

Thanks

Darren

>>

I just put this FAQ together:

ASP Design Tips - Search For an Expression on Multiple Fields
[url]http://www.bullschmidt.com/devtip-searchmultiplefields.asp[/url]

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bullschmidt]

<<
I need to search multiple keywords across multiple fields.

If the keywords entered are: "one" & "two" I need an SQL statement that
reads something like

Where fieldName contains "one" & "two" or fieldName2 contains "one" &
"two"

Get my drift ?

Then I need to dynamically build which fields to search - I can worry
about
that later.

Thanks

Darren

>>

I just put this FAQ together:

ASP Design Tips - Search For an Expression on Multiple Fields
[url]http://www.bullschmidt.com/devtip-searchmultiplefields.asp[/url]

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[runaway_vibrator]

Hi - I have a field in a database I'd like to increment by one my sql looks
like this at the mo

Does anyone know what the best way to do this is...i.e. complex sql statements.

"UPDATE tbl_logs SET FLD_count= (SELECT FLD_count WHERE fld_id =2)+1 WHERE
fld_id = 2"

[CMBergin]

Why complicate a simple thing? ;)
UPDATE tbl_logs SET FLD_count = FLD_count + 1 WHERE fld_id=2


"runaway_vibrator" <webforumsuser@macromedia.com> wrote in message
news:cvhjii$ir3$1@forums.macromedia.com...

> Hi - I have a field in a database I'd like to increment by one my sql

looks

> like this at the mo
>
> Does anyone know what the best way to do this is...i.e. complex sql

statements.

>
> "UPDATE tbl_logs SET FLD_count= (SELECT FLD_count WHERE fld_id =2)+1

WHERE

> fld_id = 2"
>

[zCrow]

I have two tables. One that holds institution information and another that
holds user info. If I delete an institution from that table, there are going to
be "orphaned" users in the user table. I want to build an sql statement that
will filter out only those users that no longer have a relationship to the
institution table.

This sql gives me the users that do have an institution matching those found
in the institution table. I guess I need the mirror image of this,,,,,

SELECT *
FROM User, Institution
WHERE (User.Institution_ID = Institution.Institution_ID )

Thanks for any help.

[Lionstone]

SELECT User.*
FROM User LEFT OUTER JOIN Institution ON
User.Institution_ID=Institution.Institution_ID
WHERE Institution.Institution_ID IS NULL

"zCrow" <webforumsuser@macromedia.com> wrote in message
news:d58bf1$9k7$1@forums.macromedia.com...

>I have two tables. One that holds institution information and another that
> holds user info. If I delete an institution from that table, there are
> going to
> be "orphaned" users in the user table. I want to build an sql statement
> that
> will filter out only those users that no longer have a relationship to the
> institution table.
>
> This sql gives me the users that do have an institution matching those
> found
> in the institution table. I guess I need the mirror image of this,,,,,
>
> SELECT *
> FROM User, Institution
> WHERE (User.Institution_ID = Institution.Institution_ID )
>
> Thanks for any help.
>

[Zachariah Crow]

Brilliant. Thanks Lionstone. Worked like a charm. I see I need to learn
more sql!!


"zCrow" <webforumsuser@macromedia.com> wrote in message
news:d58bf1$9k7$1@forums.macromedia.com...

>I have two tables. One that holds institution information and another that
> holds user info. If I delete an institution from that table, there are
> going to
> be "orphaned" users in the user table. I want to build an sql statement
> that
> will filter out only those users that no longer have a relationship to the
> institution table.
>
> This sql gives me the users that do have an institution matching those
> found
> in the institution table. I guess I need the mirror image of this,,,,,
>
> SELECT *
> FROM User, Institution
> WHERE (User.Institution_ID = Institution.Institution_ID )
>
> Thanks for any help.
>

[mitch]

I don't know if this is the best way to do this but I was pressed for time.

I will use your system as an example:

- create a page/form that has a dropdown list that lists the Institution,
when someone selects the Institution they want to delete they click the
DELETE form button and get sent to the confirmation page

- the confirmation page will have two recordsets, one that check the User
table to see if the Institution exists, and the other one pulls up the
Institution for use in the DELETE function

- the confirmation page can have two areas that are controlled by the
show/hide region, if the page finds the Institution in the User table it
hides the delete portions of the page and displays a message that says it
cannot be deleted, if the page does not find the Institution in the User
table then it can display the DELETE portion of the page.

I think that would work? I am sure there are better ways but I needed to get
something out ASAP.

MT


"zCrow" <webforumsuser@macromedia.com> wrote in message
news:d58bf1$9k7$1@forums.macromedia.com...

> I have two tables. One that holds institution information and another that
> holds user info. If I delete an institution from that table, there are

going to

> be "orphaned" users in the user table. I want to build an sql statement

that

> will filter out only those users that no longer have a relationship to the
> institution table.
>
> This sql gives me the users that do have an institution matching those

found

> in the institution table. I guess I need the mirror image of this,,,,,
>
> SELECT *
> FROM User, Institution
> WHERE (User.Institution_ID = Institution.Institution_ID )
>
> Thanks for any help.
>

[Zachariah Crow]

hi Mitch. Actually a great idea. I was playing around with that concept. It
is probably a more elegant idea and would clean up the db much better rather
than relying on others to remember to check another page for orphaned users.
As well, from a security point of view, those users need to be denied access
as soon as the institution no longer exists.

Thanks. I think I will play with that.

"mitch" <mitch_001@REMOVEyahoo.com> wrote in message
news:d58drj$cro$1@forums.macromedia.com...

>I don't know if this is the best way to do this but I was pressed for time.
>
> I will use your system as an example:
>
> - create a page/form that has a dropdown list that lists the Institution,
> when someone selects the Institution they want to delete they click the
> DELETE form button and get sent to the confirmation page
>
> - the confirmation page will have two recordsets, one that check the User
> table to see if the Institution exists, and the other one pulls up the
> Institution for use in the DELETE function
>
> - the confirmation page can have two areas that are controlled by the
> show/hide region, if the page finds the Institution in the User table it
> hides the delete portions of the page and displays a message that says it
> cannot be deleted, if the page does not find the Institution in the User
> table then it can display the DELETE portion of the page.
>
> I think that would work? I am sure there are better ways but I needed to
> get
> something out ASAP.
>
> MT
>
>
> "zCrow" <webforumsuser@macromedia.com> wrote in message
> news:d58bf1$9k7$1@forums.macromedia.com...

>> I have two tables. One that holds institution information and another
>> that
>> holds user info. If I delete an institution from that table, there are

> going to

>> be "orphaned" users in the user table. I want to build an sql statement

> that

>> will filter out only those users that no longer have a relationship to
>> the
>> institution table.
>>
>> This sql gives me the users that do have an institution matching those

> found

>> in the institution table. I guess I need the mirror image of this,,,,,
>>
>> SELECT *
>> FROM User, Institution
>> WHERE (User.Institution_ID = Institution.Institution_ID )
>>
>> Thanks for any help.
>>

>
>

[mitch]

Cool...glad I could help.

I also like the approach I mentioned because you can give them a detailed
message as to why they cannot delete something and if you want to get fancy
you could display the number of students that are enrolled in the particular
Institution they wanted to delete.

MT


"Zachariah Crow" <zcrow@shaw.ca> wrote in message
news:d58etv$eci$1@forums.macromedia.com...

> hi Mitch. Actually a great idea. I was playing around with that concept.

It

> is probably a more elegant idea and would clean up the db much better

rather

> than relying on others to remember to check another page for orphaned

users.

> As well, from a security point of view, those users need to be denied

access

> as soon as the institution no longer exists.
>
> Thanks. I think I will play with that.
>
> "mitch" <mitch_001@REMOVEyahoo.com> wrote in message
> news:d58drj$cro$1@forums.macromedia.com...

> >I don't know if this is the best way to do this but I was pressed for

time.

> >
> > I will use your system as an example:
> >
> > - create a page/form that has a dropdown list that lists the

Institution,

> > when someone selects the Institution they want to delete they click the
> > DELETE form button and get sent to the confirmation page
> >
> > - the confirmation page will have two recordsets, one that check the

User

> > table to see if the Institution exists, and the other one pulls up the
> > Institution for use in the DELETE function
> >
> > - the confirmation page can have two areas that are controlled by the
> > show/hide region, if the page finds the Institution in the User table it
> > hides the delete portions of the page and displays a message that says

it

> > cannot be deleted, if the page does not find the Institution in the User
> > table then it can display the DELETE portion of the page.
> >
> > I think that would work? I am sure there are better ways but I needed to
> > get
> > something out ASAP.
> >
> > MT
> >
> >
> > "zCrow" <webforumsuser@macromedia.com> wrote in message
> > news:d58bf1$9k7$1@forums.macromedia.com...

> >> I have two tables. One that holds institution information and another
> >> that
> >> holds user info. If I delete an institution from that table, there are

> > going to

> >> be "orphaned" users in the user table. I want to build an sql statement

> > that

> >> will filter out only those users that no longer have a relationship to
> >> the
> >> institution table.
> >>
> >> This sql gives me the users that do have an institution matching those

> > found

> >> in the institution table. I guess I need the mirror image of this,,,,,
> >>
> >> SELECT *
> >> FROM User, Institution
> >> WHERE (User.Institution_ID = Institution.Institution_ID )
> >>
> >> Thanks for any help.
> >>

> >
> >

>
>

[Lionstone]

You can also set up a trigger in your database (see the docs for specifics -
it's not something I do often). Whenever an institution is deleted, the
trigger can automatically deactivate any accounts for that institution. No
other programming required. :)

"Zachariah Crow" <zcrow@shaw.ca> wrote in message
news:d58etv$eci$1@forums.macromedia.com...

> hi Mitch. Actually a great idea. I was playing around with that concept.
> It is probably a more elegant idea and would clean up the db much better
> rather than relying on others to remember to check another page for
> orphaned users. As well, from a security point of view, those users need
> to be denied access as soon as the institution no longer exists.
>
> Thanks. I think I will play with that.
>
> "mitch" <mitch_001@REMOVEyahoo.com> wrote in message
> news:d58drj$cro$1@forums.macromedia.com...

>>I don't know if this is the best way to do this but I was pressed for
>>time.
>>
>> I will use your system as an example:
>>
>> - create a page/form that has a dropdown list that lists the Institution,
>> when someone selects the Institution they want to delete they click the
>> DELETE form button and get sent to the confirmation page
>>
>> - the confirmation page will have two recordsets, one that check the User
>> table to see if the Institution exists, and the other one pulls up the
>> Institution for use in the DELETE function
>>
>> - the confirmation page can have two areas that are controlled by the
>> show/hide region, if the page finds the Institution in the User table it
>> hides the delete portions of the page and displays a message that says it
>> cannot be deleted, if the page does not find the Institution in the User
>> table then it can display the DELETE portion of the page.
>>
>> I think that would work? I am sure there are better ways but I needed to
>> get
>> something out ASAP.
>>
>> MT
>>
>>
>> "zCrow" <webforumsuser@macromedia.com> wrote in message
>> news:d58bf1$9k7$1@forums.macromedia.com...

>>> I have two tables. One that holds institution information and another
>>> that
>>> holds user info. If I delete an institution from that table, there are

>> going to

>>> be "orphaned" users in the user table. I want to build an sql statement

>> that

>>> will filter out only those users that no longer have a relationship to
>>> the
>>> institution table.
>>>
>>> This sql gives me the users that do have an institution matching those

>> found

>>> in the institution table. I guess I need the mirror image of this,,,,,
>>>
>>> SELECT *
>>> FROM User, Institution
>>> WHERE (User.Institution_ID = Institution.Institution_ID )
>>>
>>> Thanks for any help.
>>>

>>
>>

>
>