Hello,
XPSP2
VB 2005 Express
Jet/Access database
One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?
Thanks,
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
I have a table census data containing the number of people having ages ranging from 0 to 120 years. Year 0 is in a column called F_0 and year 120 is in a column called F_120 with everything in between.
For example, when I create a script for the table the column for year 0 appears as: [F_0] [numeric](38, 8) NULL
I've already created 2 summary columns (people 0 to 18 years old and people 19 to 59 years old).
I follow the same methodology to create a summary table for people from 60 to 100 years old, but all the calculated values are NULL.
So the following is successful:
[Under_19] AS (((((((((((((((((([F_0]+[F_1])+[F_2])+[F_3])+[F_4])+[F_5])+[F_6])+[F_7])+[F_8])+[F_9])+[F_10])+[F_11])+[F_12])+[F_13])+[F_14])+[F_15])+[F_16])+[F_17])+[F_18]) PERSISTED,
But this is not:
[Over_60] AS (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((([F_60]+[F_61])+[F_62])+[F_63])+[F_64])+[F_65])+[F_66])+[F_67])+[F_68])+[F_69])+[F_70])+[F_71])+[F_72])+[F_73])+[F_74])+[F_75])+[F_76])+[F_77])+[F_78])+[F_79])+[F_80])+[F_81])+[F_82])+[F_83])+[F_84])+[F_85])+[F_86])+[F_87])+[F_88])+[F_89])+[F_90])+[F_91])+[F_92])+[F_93])+[F_94])+[F_95])+[F_96])+[F_97])+[F_98])+[F_99])+[F_100])+[F_101])+[F_102])+[F_103])+[F_104])+[F_105])+[F_106])+[F_107])+[F_108])+[F_109])+[F_110])+[F_111])+[F_112])+[F_113])+[F_114])+[F_115])+[F_116])+[F_117])+[F_118])+[F_119])+[F_120]) PERSISTED,
i need help to get the value of a column which is selected from a column of another table....
View 3 Replies View RelatedHi,I have two databases on the same server: COMU0002 and 2007DUand two tables: tecnicos on COMU0002 and telf_cli ON 2007DUBeing on COMU0002 this query doesn't work:SELECT * FROM tecnicosLEFT JOIN 2007DU.dbo.telf_cliON ...The error says: INCORRECT SYNTAX NEAR '2007'Even this query being on 2007DU fails on management studio:SELECT * FROM 2007DU.dbo.telf_clibut SELECT * FROM telf_cli works okAny ideas?Thank you!
View 2 Replies View RelatedI am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date
1 1/1/2014
1 1/5/2014
Table 2
ID date
1 1/5/2014
2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
Hello,I am passing a value to a stored procedure in vb.net to get normally aselection of the table. How could I get the complete table withoutwriting the code twice? Many thanks in advance ....
View 9 Replies View RelatedHey, I'm just trying to do select top 1 * from lnksql1.db1.dbo.Table1. This table has a column which is an xml column. It returns an error saying "
Xml data type is not supported in distributed queries. Remote object 'LNKSQL1.db1.dbo.table1' has xml column(s).
"
What's the workaround for this? Thank you.
Hi
I can't figure out how to do this and hope you guys can give me a hint....
I want to select all columns from a table except one column:
For example,
A table has 20 columns : {1,2,3.....20}
To select all of columns in a table, i could use
select * from table;
However, I want to select only column {1....19}
I do not want to type the column names in one by one in the SQL query,
is there a way to achieve this in a short form of Select?
Thanks,
i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....
View 6 Replies View RelatedHello fellow .net developers,
In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.
Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.
So, what I need to know is: is there a sql statement that can return this type of information?
Example:
Table Names in Database: Customers, Suppliers
Columns in Customers Table: Name, Phone, Email, Address
I click on the word "Customers" in the first dropdownlist.
I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.
I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.
Thanks in advance,
Robert
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
Is there a SELECT statement that will return a the column names of a given table?
View 5 Replies View Related‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:
SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’
Hello, everyone:
I have an Excel table that has columns like,
Vendoe_Name
Level
Address
Email
Phone
I use the statemen
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]
and got output by column order,
Address
Email
Level
Phone
Vendoe_Name
How to make output by original column order like,
Vendoe_Name
Level
Address
Email
Phone
Any help will appreciated. Thanks
ZYT
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[Code]....
i want to select the data priority wise
the o/p should look like below
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records?
Â
btw I have used this statement
SET
IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â Â 1Â Â Â 1
AEDÂ Â Â BNTÂ Â Â 12Â Â Â 1
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 1Â Â Â 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â 13Â Â Â 2
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 11Â Â Â 7
AFNÂ Â Â BPCÂ Â Â 8Â Â Â 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies View RelatedHow do I select data from another database name (in the same SQL SERVER LOCATION) and its child table?
SELECT user_id
FROM [10.10.20.3].CC_host.usr_table
Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By user_id
I always create database tables with an id column with an autoincrementing number.
Does a table actually need this or can I remove this field if I don't refer to it?
thanks
Jon
Hi friends,
I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database.
When I try to put the result (single row) in a variable I get the following error message.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.".
Pls help me
Mahe
I have researched how to put a URL column in my SQl Server 2008 database and table. I have tried varchar(max) and varchar(1000) and then done what I found online by inserting into the url with ' ' around it and then I ODBC into Access and it does not look like a link. Cannot link on it and have it go where it needs to. I have also tried this in the front-end application and it does not look like a link. It does not respond either.
View 4 Replies View RelatedHi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
Hello,
I have two tables, one is a "header" and the other is a "Footer" for a customer DB I wrote. The footer has 4 records for each header.
I need a field off of the footer to do a 'status count' and I can't get it to work
for example
select A.Status,count(*) from Header a inner join Footer b on a.CustomerID = b.CustomerID where b.SalesRep='Joe'
Basically..I am trying to get an output of
Complete 2
Cancelled 1
In Progress 3
Waiting 1
Etc.
The problem is the footer table has 4 records for each ... and even if I use a distinct clause... It multiplies the correct result by 4.
Select * from users where clue > 0
I have the following select statement within a stored procedure:
DECLARE @first_date DATETIME
select @first_date = top 1 (g_collectdate)
from ESS_Coefficients es, ProgramXRef
where es.Program_Id = ProgramXRef.Program_Id
AND @In_Program = ProgramXRef.Program_Name
AND es.Scope LIKE @In_Scope
order by g_collectdate asc
The syntax check tells me "Error 156: Incorrect syntax near keyword 'top'".
I took out the @first_date = portion, substituted in values and ran the query from SQL query analyzer and it worked fine.
select top 1 (g_collectdate)
from ESS_Coefficients es, ProgramXRef
where es.Program_Id = ProgramXRef.Program_Id
AND 'ABC' = ProgramXRef.Program_Name
AND es.Scope LIKE 'abc/%'
order by g_collectdate asc
What syntax problem is causing the error?
Hi,
I have a select statement running on the client machine linking to different tables in 1 database. All with the same schema. When I ran it, i had this error. I had trial and error, removing 1 table at a time until i hit the one which is causing it. when i removed it, everything's ok. i just wonder if all the tables were using dbo schema what is causing this particular table to throw this error?
cherriesh
Hi,
I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?
I have the folowing databases DB1,DB2,DB3,D4,DB5........
I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on......)
If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.
can someone show me the script to do it?
Thanks.
how can setup constraint for the column in table in database ??
and what will sould write in the (BLOCKED EXPRESSION field ??