I have a Query named qryQuery1 from which I would like to make a Table named tblTable1. I need to do this within a VBA Microsoft Access module.
Can anyone help me with the proper code?
Thank you for any help.
Jim
Hi Guys,
I have a requirement as follows:->
I have a table like below
EMPLOYEEID------- OWNS
********** ****
1-----------------car
1-----------------house
1-----------------dog
2-----------------house
3-----------------car
3-----------------bus
3-----------------shop
3-----------------hotel
3-----------------theater
3-----------------casino
Requirement:
I wanted to create another table based on the column values. For eg: I have to take the employee id and check for what value he has under owns column in the table. I take only 3 values and then these values should go to the newly created columns (owns1, owns2,owns3).
if there is no value for any of these columns it should have null values loaded in them.
The result of the modification should look like this:->
EMPLOYEEID-------OWNS1------OWNS2------OWNS3
*********** ***** ***** *
1-----------------car--------house------dog
2-----------------house------Null-------Null
3-----------------car--------Bus--------shop
Note: eventhough employeeid 3 owns more than 3 things we only take 3 of what he owns and populate to above coloumns.
In addition to it, the column OWNS will have more than 500 different values in them.
Its kind of urgent and if anyone knows how to , Can you please help me on this.
Thanks a lot.
-- Ragulan
;)
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
I have a datagrid in my file along with an Update Query.
My Update Query basically adds the numerical values in two columns
together when the page is loaded. This means whenever the page is
Refreshed the Update query is fired.
This is my Update Query (which is in an Stored Procedure):
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
I have declared my query in the 'Page_Load' part of the coding, because
I want the query to run automatically. Not manually by a button.
My main question is that how can I get the query to run only once a day, no matter how many times the page is loaded.
OK Here goes my BIG question
I've following database structure (just junk values)
[ChannelID]---[ChannelName]---[ChannelCatID]---[RSSFeedURL]
1---YouTube---1---ret---
2---GoogleVideo---1---ert---
3---MSNVideo---1---rtertert---
4---ABC---2---retert---
5---YahooVideo---1---erter---
6---CBS---2---rtr---
7---NBC---2---trtrt---
8---ESPN---2---rt---
9---FOX---2---rtrtttr---
10---AOL---1---rt---
I wanna generate following XML structure out of it
<DOC>
-<Item Type="xyz">
--<Channel id="a">
---<Name>aaaaa<Name/>
---<URL>aaaaa<URL/>
--</Channel>
--<Channel id="b">
---<Name>bbbb<Name/>
---<URL>bbbb<URL/>
--</Channel>
---
---
-</Item>
-<Item Type="lmn">
--<Channel id="x">
---<Name>xxxx<Name/>
---<URL>xxxx<URL/>
--</Channel>
--<Channel id="y">
---<Name>yyyy<Name/>
---<URL>yyyy<URL/>
--</Channel>
---
---
-</Item>
------
------
------
</DOC>
I've written this Select For XML query
SELECT ChannelCatID as '@type',
ChannelID as 'Channel/@id',
ChannelName as 'Channel/Name',
RSSFeedURL as 'Channel/URL'
FROM ChannelMaster
FOR XML PATH('Item'), ROOT('DOC')
It gives me this result
<DOC>
-<Item Type="xyz">
--<Channel id="a">
---<Name>aaaaa<Name/>
---<URL>aaaaa<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
--<Channel id="b">
---<Name>bbbb<Name/>
---<URL>bbbb<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
---
-</Item>
-<Item Type="xyz">
---
-</Item>
------
------
------
</DOC>
I want it this way :
all channels of Type xyz should come under singe <Item Type="xyz"> tag instead of seperate tags
what changes should i make, plezz help meout wit dis ??
.(][)[](][).
Hi
Im new in this forum so don't if this is the right place to post these kind of questions..
Im using vb2005 and have made a program that deals with customers and payments.
I have a table called acount. Roughly it contains these columns
Number, amount, type..
type can be a payment or a charge
I would like to have a query that gives me this result:
Number, sum(payments), sum(charges) (grouped by number)
I think it is some type of merging these to querys
select number, sum(amount) where type = 0 group by number
and
select number, sum(amount) where type <> 0 group by number
Im using it to calculate a customers acount to see if they have paid to much or to little.
Any ideas?
I have a table with about 20,000 records. So, to minimise performance issues, i try to only retrieve the top 100 records. The databaset retrieved can be paged and sorted. Below is my code, but it's taking quite a while even to only load the 100 records. Any suggestions?Private Sub BindGrid() Dim connectionString As String = "MyConnectionString" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT TOP 100 [Person].* FROM [Person] ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString()
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Try DataGrid1.DataSource = dataSet DataGrid1.Databind() Catch e As Exception DataGrid1.CurrentPageIndex = 0 End TryEnd Sub
hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:UserID Stat1 Stat2 Stat3 ...... Stat3223 0 0 0 056 0 0 0 072 0 0 0 0this is a query for one of the statistics i am currently using:SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID WHERE UserDescription.Status=1 AND UserDescription.UserType=1GROUP BY DefendantCase.UserIDif i run this query, this is what i get:UserID CountOfUserID54 2160 1052 294 427 22how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!
View 3 Replies View RelatedI need to make a backup of a table so that if I mess it up I can put the data back to where I started...
I am going to insert some new rows and update values in some existing rows. So before I do this and find out I messed it up...how do I make a copy of Table A as Table B?
I would actually like to make a copy to table B, then run my changes into B, make sure all is correct, and then transfer the revised data from B over to table A replacing its data. What approach would be best here to accomplish this?
Thanks
Hi,
I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable
declare @a smalldatetime
select @a=last_run_time from job_status
where job_des='sample'
SELECT * FROM History
WHERE CHANGE_DATE> @a
Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query.
SELECT * FROM History
WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '
History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.
will there be any difference if use dynamic sql ?
I have one query that looks like this.
DECLARE @propname
SET @propname = NULL -- can be Null or have wild card values
SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)
col1, col2, col3 are part of a clustered index
propertyname is a nonclustered index
This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?
THANKS
I have a huge question, it's for my job im doing now. I have a table with the name TWO.dbo.SVC06105. I want to be able to take the word "TWO" and put that into a variable. I know this doesn't look correct, but I want to be able to do something like this:
DECLARE @GPNAME CHAR(100)
SET @GPNAME = 'TWO'
SELECT * FROM @GPNAME.dbo.SVC06105
I know the SELECT statement isn't correct, but I hope that you guys understand what i'm trying to do through this example. BTW, I am new to this team so nice to meet you all. L8er
I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol).
I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query.
What is going wrong. Can someone please help me ?
Tom Knardahl
How can I count the rows when I make new table. Here is my code:
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID, COUNT(TO_CHAR(HIRE_DATE, 'DAY')) AS NUM_EMPLOYEES
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Monday'
GROUP BY EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID;
Here is the table: [URL] ....
I have 10 rows, so I would like under NUM_EMPLOYEES to be displayed 10 on each row.
Hello, Im a very new to SQL server etc, so please bear with me.
I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column.
I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column.
How do I do this within Server Management Studio?
Thank you.
I have below table:
IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints
--===== Create the test table with
create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))
--===== All Inserts into the IDENTITY column
INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)
and i want to have a table in below format: (query for it)
Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
Hi.
I found unpleasant issue within RS:
1. Imagine RS report with Table(with interactive sorting) whose visibility is toggled by Textbox:
2. After sorting the Table the Textbox is getting collapsed making the Table invisible. But the Table should remain visible.
Is this MS bug? Are there workarounds?
If my question is not understandable I can send simple *.rdl report with sample.
Thank you in advance for help.
Hello,
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID
Name
Location
Start
End
1
Joe
NY
2000
2001
1
Joe
CA
2002
2004
1
Joe
MA
2005
2008
2
Sue
NJ
2003
2004
2
Sue
FL
2004
2008
3
Bob
CA
1999
2000
3
Bob
WA
2001
2004
3
Bob
OR
2005
2006
3
Bob
MI
2007
2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID
Name
Location1
Start1
End1
Location2
Start2
End2
Location3
Start3
End3
Location4
Start4
End4
1
Joe
NY
2000
2001
CA
2002
2004
MA
2005
2008
2
Sue
NJ
2003
2004
FL
2004
2008
3
Bob
CA
1999
2000
WA
2001
2004
OR
2005
2006
MI
2007
2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
Thank you.
-Gumbatman
Hello,
I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.
'eligibility (dbo)' table
- Unable to create index 'PK_eligibility'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Any ideas on how to make the table change more efficient or change the timeout period. I need to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.
Thanks!
Hello, I have a table of data with the following columns:ParticipantResult (win or lose)The table has other data in, but I want to make a league to sort by the amounts of wins. For example: Participant Won PlayedA 5 10B 4 7C 3 3 I want this to show up on web page. I was hoping to do this through the source code on the web page, but I am struggling to do it.Is there a better way to do this, or a place where I can see an example of the code. Many Thanks, Oliver
View 1 Replies View Relatedwith SQL desktop engine installed where do I even begin working with it?
View 1 Replies View RelatedI hope I can explain this somewhat coherantly. Here goes..
I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style.
I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary.
SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?
Here is a sample...
Code:
SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style
FROM prods
WHERE (style='style1' or style='style2' or style='style3')
AND league='leaguex'
AND soldout=''
GROUP by style
ORDER by style
Thanks very much for any help!! I'm sure I'm going about this all wrong.
Hi,
What is the best method/model for having real time replication set up and also needing the ability to drop and recreate SP's/Tables/UDF's etc? I keep getting the error of the SP is being used in replication so it cannot be dropped...
Thanks in advance.
Adam
Hi alllI have these tables below with 3 fields each. I want to get the recordin table 1 whose field number value is same in table 2 but field number2 on both tables are different. I mean i want the record1500 800 2. Insight: Table 1 is modules ordered and table 2 is modulesdelivered. I want to get 1500 800 2.beacuse module 800 was ordered butin table 2 module 503 was delivered. can some one help me with a joinnice weekeendTable 110 5012 101600502100 502 31500800 2Table 21500503 114004000100502 10100600100502 3
View 3 Replies View RelatedPlease see the detail of tables with fields in SQL server 2000.·TableStudent .Table Good Qualities( It is a list of Students) (It is a list of Good Qualities)1-Student Id (P.Key)1- Serial No. (P. Key)2-Student Name 2- Good Quality· Table Bad Qualities .Table Future Plans(It is a list of Bad Qualities) (It is a list of Plans)1-Serial No. (P. Key) 1- Serial No. (P. Key)2-Bad Quality 2- Future Plans·Table-Personality1-Student Name/ID2-Good Qualities3-Bad Qualities4-Future PlansData will store in Personality table like thatStudent IDG.Q.No B.Q.NoF.P.No101 3 2 1101 4 5 NUll101 8 Null Null202 4 8 9202Null 3 1Required ResultNow I have to generate a report of One student andsometimes of many students comprising of their Name, G.qualities, BadQualities and Future Plan. To generate report User input will be onlystudent id.I Need help in making relationship of these tables as well as inMaking a Query. Any suggestion to get the required result. Speciallyplease look at the Personality Table because I do not understandPrimary key for it. Should I just include the Serial No. as Primarykey.Please make necessary changes to get the required result.I shall be really thankful to you for your kind favor.
View 3 Replies View RelatedHello Everyone,
I have a dimension that has the following members:
Directors
Senior Managers
Managers
Senior Associates
Associates
But they way they need to be displayed includes subtotals. Senior Managers and Managers are placed into Management and Staff is a total of everyone except Directors. They should appear on the report in something like this:
Directors 10
Senior Managers 15
Managers 9
Management 24
Senior Associates 17
Associates 40
Staff 81
Total 91
What is the best way to do this? Right now I am using a matrix because I am also showing headcounts by Period. Is a table a better way to go? Is it something that should be done in my dimension?
I can do the Total part. I am just not sure how to make the subtotals inbetween the other lines.
Thank you for the help.
-Gumbatman
how can i create a report for my database? how can i create bar graphs, etc.? im using msde and sql express. pls help. thanks.
View 3 Replies View RelatedI have been reading up on how replicating schema changes work, but I must be missing something. When I configured a database for replication, it added a rowguid column to each table. This I understand.
I want to test making a schema change and replicating that change. For example, I tried to add a column to an existing table. When I tried to save the table i get the following error:
"Error validating the default for column rowguid".
"- Unable to modify table.
It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted."
So, what am I missing? Do i need to drop the rowguid column, add the new column, and then re-add the rowguid column?
Many thanks...
Hi,
I have the following flow in which i am looping through a folder containing textfiles
Foreach loop
[
ScriptTask -> DataFlowTask -> Execute SQL
]
In my ScriptTask, i am checking the file validity. Now if the file is found incorrect, I do not want the DataFlowTask and other to execute. Instead i want the file to be moved to Error and the loop to proceed with the next file. If the file validity is correct, DFT and ExecuteSQL execute. How do i do this?
Some explanation supported by examples would help.
Lalit
hi
I am not able to create connection string through vwd express for sql express database file n getting a error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
plz tell me where is the problem n how can i resolve it
Thanks n with regardsCN
Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record. Graciaz,Nhoyti
View 3 Replies View RelatedHi
I have no idea but want to learn it how to make random names with sql server...
I have a table, called table1, for colums Firstname and Last name
I want it to make random names, so much it is possible it can get, in table2 where the colums is named Names
PLEASE HELP :)
Playing with DTS for converting text data to SQL Server table and sending email messages. Package is to be scheduled hourly.
1. Is there any easy way to force DTS to choose Success or Failure based on a SQL Task? Wish to check contents of a table for new values and branch accordingly in DTS.
2. When new records are brought in I wish to build a custom email message rather than attaching a text file. Any Idea how to build a custom message?
I am attempting to auto scan an error log on a Unix based system and email myself when errors occur. This is an exercise to test scheduling of jobs, bridging between technologies and sending out meaningful messages based on the process via email.
Thanks,
Mike Hoyt