Dropdown menu help

I have a report with 4 parameters
2 of them are dropdown menus, one contains groups that populate the other
dropdown box with computer names. The report works great but I would like to
have an ALL function in the computer name dropdown box.
From poking around here I was able to plug a UNION SELECT 'All' AS
ComputerName into the dataset that builds the computername list but I am no
sql wiz by any stretch of the imagination and it seems that is just plugging
in All to the dropdown without any meaning behind it.
Has anyone done this sort of thing before or have any examples they could
share?
Thanks!
Kevinhttp://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> I have a report with 4 parameters
> 2 of them are dropdown menus, one contains groups that populate the other
> dropdown box with computer names. The report works great but I would like
to
> have an ALL function in the computer name dropdown box.
> From poking around here I was able to plug a UNION SELECT 'All' AS
> ComputerName into the dataset that builds the computername list but I am
no
> sql wiz by any stretch of the imagination and it seems that is just
plugging
> in All to the dropdown without any meaning behind it.
> Has anyone done this sort of thing before or have any examples they could
> share?
> Thanks!
> Kevin
>|||Thanks Chris, works great!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:us4dvpleEHA.140@.TK2MSFTNGP12.phx.gbl...
> http://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
> news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> > I have a report with 4 parameters
> >
> > 2 of them are dropdown menus, one contains groups that populate the
other
> > dropdown box with computer names. The report works great but I would
like
> to
> > have an ALL function in the computer name dropdown box.
> >
> > From poking around here I was able to plug a UNION SELECT 'All' AS
> > ComputerName into the dataset that builds the computername list but I am
> no
> > sql wiz by any stretch of the imagination and it seems that is just
> plugging
> > in All to the dropdown without any meaning behind it.
> >
> > Has anyone done this sort of thing before or have any examples they
could
> > share?
> >
> > Thanks!
> >
> > Kevin
> >
> >
>

Drop tables with unknown names and unknown quantity

This is what I want to do:

1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.

For example:
(Tables in database)
Account
Account1
Account2
Binder
Binder1
Binder2
Binder3
......

I want to delete all the tables in the database with the exception
of Account and Binder.

I know that there are no wildcards in the "Drop Table tablename"
syntax. Does anyone have any suggestions on how to write this sql
statement?

Note: I am executing this statement in MS Access with the
"DoCmd.RunSQL sql_statement" command.

Thanks for any help![posted and mailed, please reply in news]

Amy (amarakunthy@.hotmail.com) writes:
> 1. Delete all tables in database with table names that ends with a
> number.
> 2. Leave all other tables in tact.
> 3. Table names are unknown.
> 4. Numbers attached to table names are unknown.
> 5. Unknown number of tables in database.

The simplest way is to say:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'

and then cut and paste and run the result. You would do this from
Query Analyzer.

If you would like to do it programmatically, because you are doing
it routinely, you could set up a cursor over sysobjects, and then
use dynamic SQL to drop the tables:

DECLARE @.tbl sysname
DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE name like '%[0-9]'
OPEN CURSOR drop_tbl_cur
WHILE 1 = 1
BEGIN
FETCH drop_tbl_cur INTO @.tbl
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP TABLE ' + @.tbl)
END
DEALLOCATE drop_tbl_cur

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I would also add ' AND xtype = 'U' ' in the where statement so that it
includes only user tables. This way it would include any object in the
statement and you would get errors when trying to execute.
it would look something like this:
SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9] and
xtype = 'U'

MC

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951EEFFFCC91AYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Amy (amarakunthy@.hotmail.com) writes:
> > 1. Delete all tables in database with table names that ends with a
> > number.
> > 2. Leave all other tables in tact.
> > 3. Table names are unknown.
> > 4. Numbers attached to table names are unknown.
> > 5. Unknown number of tables in database.
> The simplest way is to say:
> SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'
> and then cut and paste and run the result. You would do this from
> Query Analyzer.
> If you would like to do it programmatically, because you are doing
> it routinely, you could set up a cursor over sysobjects, and then
> use dynamic SQL to drop the tables:
> DECLARE @.tbl sysname
> DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
> SELECT name FROM sysobjects WHERE name like '%[0-9]'
> OPEN CURSOR drop_tbl_cur
> WHILE 1 = 1
> BEGIN
> FETCH drop_tbl_cur INTO @.tbl
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP TABLE ' + @.tbl)
> END
> DEALLOCATE drop_tbl_cur
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Drop tables where name matches a pattern

I have an arbitrary number of tables in a SQL2000 db with names matching
'MyTable_%'.
Is there a simple way of dropping these with a SQL command?
LMcPheeThere is an undocumented and unsupported way:
EXEC sp_msForEachTable 'IF ''?'' LIKE ''MyTable[_]%'' DROP TABLE ?;';
You should probably test this in a restored backup before you do it to your
production database (though this sounds like a very peculiar thing to do to
a production database, anyway).
As I mentioned yesterday, this will only work if none of these tables have
foreign keys referencing them OR if the cursor in sp_msforeachtable just
happens to process the tables in dependency order.
A safer way is to probably generate the script and execute it manually, this
will allow you to inspect the script and make any necessary changes first.
Using Results in Text in Query Analyzer, run:
SET NOCOUNT ON;
SELECT 'DROP TABLE '+TABLE_NAME +';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'MyTable[_]%';
Now you can copy the output, paste it to the top pane or a new query editor
window, and run it.
A
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:63D0D021-F83D-4DF1-ABD7-EBD2F3EC3074@.microsoft.com...
>I have an arbitrary number of tables in a SQL2000 db with names matching
> 'MyTable_%'.
> Is there a simple way of dropping these with a SQL command?
> LMcPhee|||Try,
-- non documented sp
exec sp_msforeachtable 'if parsename(''?'', 1) like ''my_table%'' drop table
?'
go
-- using a select statement to copy, paste and execute the result
select 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(13)
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'MyTable_%'
go
-- using a cursor and dynamic sql
declare @.sql nvarchar(4000)
declare my_cursor cursor
local
fast_forward
for
select 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as sql
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'MyTable_%'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
go
AMB
"lmcphee" wrote:

> I have an arbitrary number of tables in a SQL2000 db with names matching
> 'MyTable_%'.
> Is there a simple way of dropping these with a SQL command?
> LMcPhee

Drop Down Lists

I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".

Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)

**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table

Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID

would be

Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33Looks like an INSERT statement to me, possibly complicated by your user interface. What's the problem?

drilling in to subreports

hi,
i need to created a main report which in turn leads to subreport.for eg say my main report has a group name and names of the person in that group.iam would passing the groupname as a parameter in the main report.what i actually need is that when i click on person name in the groupon my main report ,then i want my subreport showing the details of that particular person.how could i achieve this.
Thanks in Advance.
regards,
:wave: jemimaI use CR 8.5 and RDC for report creation...

Try this...

Set all your subreports to Suppress = True when you first run your report. Then set Report.subReport.Suppress = False (where subReport is the name that you gave your subreport) when the user clicks on the drill down. (I apologize, I don't know exactly how to code this)