|
bonkers123 wrote: Is there a way?
Multiple, and their applicability depends on the type of database that you're using. SQL Server for example has the option to "link" to another database. It also depends a bit on what you expect from the target-table. Does it have to be "up to date" at all times, or would it be sufficient to have access to yesterday's data?
To add to the pile of options; you could also use the Microsoft Synchronization Framework.I are Troll 
|
|
|
|
|
It has to be up to date at ALL times... It's a sql database...
|
|
|
|
|
So how do I link a table of one db, to a the same table on a different db ... ?
|
|
|
|
|
From the Management Studio, expand "Server Objects", right-click on "Linked Servers" and select "New Linked Server". There's several options to specify the credentials for the remote database.
E.g., if you have Server A (with a master database) and a Server B (with AdventureWorks), then you could execute queries like this;
SELECT [name]
FROM [master].[sys].[tables]
UNION ALL
SELECT CAST([VersionDate] AS VARCHAR(50))
FROM [ServerB].[AdventureWorks].[dbo].[AWBuildVersion]
More information on configuring a linked server can be found here. Hope this helps  I are Troll 
|
|
|
|
|
|
Hi all,
I've been scouring the web for information on how to access an SQL Server Compact database under WinCE, using Embedded VC++. Does anyone know where I can find examples or pointers on how to do this please?
TIA modified on Wednesday, March 10, 2010 4:39 AM
|
|
|
|
|
| Have you looked at connectionstrings.com, that is where we point most inquiries on this subject. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a MS Access database with a table INFO that has Name column (only contains First Name), since it contains a huge list, it will require very long time to queury all of them. I prefer to devide them by character groups (A,B,C ... Z) in order to reduce the queury time
I couldn't find the the SQL command that can queury table INFO with column Name starting with A character
Anyone know how to do it?
Thanks to any help 
|
|
|
|
|
WHERE Name LIKE 'A%' ORDER BY Name should do it as % is the wildcard character
|
|
|
|
|
I did try 'A%' somehow it doesn't work, but if I try with 'A*' like the latter response then it work!
Anyway, thank for help just might be syntax from me
|
|
|
|
|
When I use Microsoft.Jet.OLEDB.4.0 to work with an Access database, it takes % as the wildcard character.
|
|
|
|
|
| It's been a long time since I used Access, but IIRC, a % symbol represented a single arbitrary character, while the * meant any number of characters, much like DOS and the ?/* pair. "A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: IIRC, a % symbol represented a single arbitrary character
Not quite.
Apparently there are two standards, one using * the other % for "any string", see here[^]. I have a C#/Access application that works well with % (and not at all with *)but reading that page seems to tell me I should use *, not %.
|
|
|
|
|
Try a select statement like this:
select * from INFO where Name like "A*"
This will return all rows with column, Name, beginning with the Letter A.
|
|
|
|
|
It works beautifully
Thanks for help 
|
|
|
|
|
Hello there
I have a table which contains the following fields...
artist
title
count_played
date_title_played
I am trying to figure out how to present a top ten most played, but I can't seem to get my head around how to stop it showing duplicate count_played. At the moment I have used the following Select...
SELECT top 10 artist, title, count_played, date_title_played
FROM dbo.songlist
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESC
However this is what shows...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Koko Conley Keep Your Head Up 22 09/03/2010 10:35:07
Red Sun Compromise 21 09/03/2010 11:08:57
Cara Friend of Mine 21 09/03/2010 11:01:09
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
Patchwork Grace Boozawaffle 19 09/03/2010 10:49:01
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45
Red Sun Just To Hold You 17 09/03/2010 10:11:00
What I actually want is...
Artist Title Count Last Played
Wanted By The FBI Until the End of the World 22 09/03/2010 11:22:16
Red Sun Compromise 21 09/03/2010 11:08:57
Preet Whos going to love you live 20 09/03/2010 10:51:22
The Ambersons Oh, my Isabelle 19 09/03/2010 10:51:22
VK Lynne Find Me 18 09/03/2010 10:45:33
Clara Barker Wishing 17 09/03/2010 10:43:45
and so on.
I am not that experienced with sql queries but if someone could point me in the right direction on this it would be very much appreciated. Is it some sort of grouping that i need to use?
Regards
Ray
|
|
|
|
|
For a start I am just a little bit confused. If you wanted the top 10 most played songs, then surely it doesn't matter if 2 songs have been played the same amount of times. If you really want to do it this way, then you need to specify DISTINCT on your count_played field i.e SELECT DISTINCT countplayed, artist, title FROM ... etc Hope this helps
[EDIT]
To limit the resultset to the top 10 would depend on your database. SELECT TOP 10 ... would work with access and SQL Server, but with MySql you need to go SELECT DISTINCT count_played FROM songlist ORDER BY count_played DESC LIMIT 10modified on Tuesday, March 9, 2010 8:18 AM
|
|
|
|
|
Hi
Thank you for your help, I am using sql 2000 however if I use DISTINCT the results seem to stay the same, if I only use the count_played field then yes it shows only single rows of a particular count number but then how do I include the artist and title fields?
I may not be seeing the wood for the trees as I don't really know much sql sorry.
Regards
Ray
|
|
|
|
|
Try a sub query that finds max [Last Played] by Count.
select ...
from songlist
inner join (select count, max([last played])...
If you need more let me know.
djj
|
|
|
|
|
Sorry I was pressed for time.
The sub query should be something like:
SELECT top 10 A.artist, A.title, A.count_played, B.MaxDate
FROM dbo.songlist A
INNER JOIN (
SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist
GROUP BY count_played
) AS B
ON A.date_title_played = B.MaxDate
WHERE count_played >0
ORDER BY count_played DESC, date_title_played DESC
This most likely will not run but again as this is of the top of my head. The join condition (ON) may need work.
|
|
|
|
|
Sorry so long with this reply I also get pressed with work time unfortunately.
I tried the suggested query and have now got it running fine, for this I thank you, however I do have a small issue and can't seem to find the answer to it.
If 2 songs exist with the same name
i.e.
"only you" by the flying pickets which has 47 plays
"only you" by the platters which has 14 plays
It will show both even though 14 plays does not fit with the top 5 most played
I slightly altered you code to fit my needs as follows...
SELECT top 5 A.artist, A.title, A.count_played, B.MaxDate FROM dbo.songlist A INNER JOIN (SELECT count_played, MAX(date_title_played) AS MaxDate
FROM dbo.songlist GROUP BY count_played ) AS B ON A.date_title_played = B.MaxDate
WHERE B.count_played >0
ORDER BY B.count_played DESC, date_title_played DESC
Any ideas ?
Thanks in advance
|
|
|
|
|
Try a sub query (top 5) that is grouped by title and ordered by number of plays.
Let me think about this and hopefully get back to you soon.
djj
|
|
|
|
|
How do I update a date/time field in a Microsoft Access database using SQL ?
I am using VB .net and up to now all my sql statements work fine.
I have tried several variations of this statement but none work. They all give me a generic "Error in UPDATE statement" message.If i try any other column of my database, the UPDATE statement works fine.It only has a problem with the TIME field which is declared in the database as DATE/TIME.
For instance the following will NOT work.
UPDATE tablename SET timecolumn='12:34' where id=82
|
|
|
|
|
Never mind.I figured it out.
I needed brackets for the time field.
So it should be :
UPDATE tablename SET [time]='12:34' where id=80
|
|
|
|
|
Hi all,
I am having a table in which one field data type is text and the data stored in it is characters + numbers for example SD01TU9,SD01TU8,SD01TU10.
Now the problem is i want the max from it.As in the above example the max should show SD01TU10 but when i use max function on that field it shows SD01TU9.
Please any one can solve it.
Thanks in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
