|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with Insert statemnt selecting from Access databaseHi, I need to select and later Update a database with information I select
from a Access 2000 database. In the following T-SQL, I want to select only one record for each VName not already exists in my database. Since each VName have many records in the table, I'm only looking with the record that has the most recenst date-time, d_DateTime. I keep getting error with this though. Appreciate it if someone can help me out here. Thanks, Alpha Oops, forgot to past the script.
INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading ) SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime], [Data].[d_OdometerTenths] FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) and ([Data].[d_DateTime]=select Max([Data].[d_DateTime]) from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA) ORDER BY [d_RemoteName] Show quote "Alpha" wrote: > Hi, I need to select and later Update a database with information I select > from a Access 2000 database. In the following T-SQL, I want to select only > one record for each VName not already exists in my database. Since each > VName have many records in the table, I'm only looking with the record that > has the most recenst date-time, d_DateTime. I keep getting error with this > though. Appreciate it if someone can help me out here. > > Thanks, Alpha Why not just use DISTINCT and remove the datetime criteria after NOT IN?
HTH Jerry Show quote "Alpha" <Al***@discussions.microsoft.com> wrote in message news:4ACAE646-A41C-432D-9C65-3B28C3446C6C@microsoft.com... > Oops, forgot to past the script. > > INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading ) > SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime], > [Data].[d_OdometerTenths] > FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA > WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) > and ([Data].[d_DateTime]=select Max([Data].[d_DateTime]) > from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA) > ORDER BY [d_RemoteName] > > "Alpha" wrote: > >> Hi, I need to select and later Update a database with information I >> select >> from a Access 2000 database. In the following T-SQL, I want to select >> only >> one record for each VName not already exists in my database. Since each >> VName have many records in the table, I'm only looking with the record >> that >> has the most recenst date-time, d_DateTime. I keep getting error with >> this >> though. Appreciate it if someone can help me out here. >> >> Thanks, Alpha Alpha,
Try: SELECT VName, MAX(d_DateTime) FROM yourtable GROUP BY VName HTH Jerry Show quote "Alpha" <Al***@discussions.microsoft.com> wrote in message news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... > Hi, I need to select and later Update a database with information I select > from a Access 2000 database. In the following T-SQL, I want to select > only > one record for each VName not already exists in my database. Since each > VName have many records in the table, I'm only looking with the record > that > has the most recenst date-time, d_DateTime. I keep getting error with > this > though. Appreciate it if someone can help me out here. > > Thanks, Alpha I got error message that it doesn't like the database in "From". Do you know
how to specify a tabe from Access database? SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]), [Data].[d_OdometerTenths] FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) GROUP BY [Data].[d_RemoteName] Thank you, Alpha Show quote "Jerry Spivey" wrote: > Alpha, > > Try: > > SELECT VName, MAX(d_DateTime) > FROM yourtable > GROUP BY VName > > HTH > > Jerry > "Alpha" <Al***@discussions.microsoft.com> wrote in message > news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... > > Hi, I need to select and later Update a database with information I select > > from a Access 2000 database. In the following T-SQL, I want to select > > only > > one record for each VName not already exists in my database. Since each > > VName have many records in the table, I'm only looking with the record > > that > > has the most recenst date-time, d_DateTime. I keep getting error with > > this > > though. Appreciate it if someone can help me out here. > > > > Thanks, Alpha > > > I usually add a linked server definition for the Access database. Take a
look at sp_addlinkedserver in the SQL Server Books Online - there is an example of how to create one there. HTH JErry Show quote "Alpha" <Al***@discussions.microsoft.com> wrote in message news:C57EA186-1559-4E8F-B85D-3634AEC858A6@microsoft.com... >I got error message that it doesn't like the database in "From". Do you >know > how to specify a tabe from Access database? > SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]), > [Data].[d_OdometerTenths] > FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA > WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) > GROUP BY [Data].[d_RemoteName] > > Thank you, > Alpha > > "Jerry Spivey" wrote: > >> Alpha, >> >> Try: >> >> SELECT VName, MAX(d_DateTime) >> FROM yourtable >> GROUP BY VName >> >> HTH >> >> Jerry >> "Alpha" <Al***@discussions.microsoft.com> wrote in message >> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... >> > Hi, I need to select and later Update a database with information I >> > select >> > from a Access 2000 database. In the following T-SQL, I want to select >> > only >> > one record for each VName not already exists in my database. Since >> > each >> > VName have many records in the table, I'm only looking with the record >> > that >> > has the most recenst date-time, d_DateTime. I keep getting error with >> > this >> > though. Appreciate it if someone can help me out here. >> > >> > Thanks, Alpha >> >> >> Is link server the only option to get the Access data? My application looks
in a directory where each day a new Access file is created with file name inlcuding the date. I use the most recent file each time the user start my application and needs to update my database. So you see, the link server won't work for me because it needs to specify the file location. Unless, I would delete the link server and create a new one each time my application starts. But that seems odd and is there even a way to delete the linkedserver? Thanks, Alpha. Show quote "Jerry Spivey" wrote: > I usually add a linked server definition for the Access database. Take a > look at sp_addlinkedserver in the SQL Server Books Online - there is an > example of how to create one there. > > HTH > > JErry > "Alpha" <Al***@discussions.microsoft.com> wrote in message > news:C57EA186-1559-4E8F-B85D-3634AEC858A6@microsoft.com... > >I got error message that it doesn't like the database in "From". Do you > >know > > how to specify a tabe from Access database? > > SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]), > > [Data].[d_OdometerTenths] > > FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA > > WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) > > GROUP BY [Data].[d_RemoteName] > > > > Thank you, > > Alpha > > > > "Jerry Spivey" wrote: > > > >> Alpha, > >> > >> Try: > >> > >> SELECT VName, MAX(d_DateTime) > >> FROM yourtable > >> GROUP BY VName > >> > >> HTH > >> > >> Jerry > >> "Alpha" <Al***@discussions.microsoft.com> wrote in message > >> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... > >> > Hi, I need to select and later Update a database with information I > >> > select > >> > from a Access 2000 database. In the following T-SQL, I want to select > >> > only > >> > one record for each VName not already exists in my database. Since > >> > each > >> > VName have many records in the table, I'm only looking with the record > >> > that > >> > has the most recenst date-time, d_DateTime. I keep getting error with > >> > this > >> > though. Appreciate it if someone can help me out here. > >> > > >> > Thanks, Alpha > >> > >> > >> > > > Alpha,
Try OPENROWSET. From SQL Server BOL: C. Use the Microsoft OLE DB Provider for Jet This example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet. Note This example assumes that Access is installed. USE pubs GO SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS a GO Show quote HTHJerry"Alpha" <Al***@discussions.microsoft.com> wrote in message [attached file: note.gif]news:1876CCCD-5CC0-4185-85FA-A755936B7C62@microsoft.com... > Is link server the only option to get the Access data? My application > looks > in a directory where each day a new Access file is created with file name > inlcuding the date. I use the most recent file each time the user start > my > application and needs to update my database. So you see, the link server > won't work for me because it needs to specify the file location. Unless, > I > would delete the link server and create a new one each time my application > starts. But that seems odd and is there even a way to delete the > linkedserver? > > Thanks, Alpha. > > "Jerry Spivey" wrote: > >> I usually add a linked server definition for the Access database. Take a >> look at sp_addlinkedserver in the SQL Server Books Online - there is an >> example of how to create one there. >> >> HTH >> >> JErry >> "Alpha" <Al***@discussions.microsoft.com> wrote in message >> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@microsoft.com... >> >I got error message that it doesn't like the database in "From". Do you >> >know >> > how to specify a tabe from Access database? >> > SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]), >> > [Data].[d_OdometerTenths] >> > FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA >> > WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) >> > GROUP BY [Data].[d_RemoteName] >> > >> > Thank you, >> > Alpha >> > >> > "Jerry Spivey" wrote: >> > >> >> Alpha, >> >> >> >> Try: >> >> >> >> SELECT VName, MAX(d_DateTime) >> >> FROM yourtable >> >> GROUP BY VName >> >> >> >> HTH >> >> >> >> Jerry >> >> "Alpha" <Al***@discussions.microsoft.com> wrote in message >> >> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... >> >> > Hi, I need to select and later Update a database with information I >> >> > select >> >> > from a Access 2000 database. In the following T-SQL, I want to >> >> > select >> >> > only >> >> > one record for each VName not already exists in my database. Since >> >> > each >> >> > VName have many records in the table, I'm only looking with the >> >> > record >> >> > that >> >> > has the most recenst date-time, d_DateTime. I keep getting error >> >> > with >> >> > this >> >> > though. Appreciate it if someone can help me out here. >> >> > >> >> > Thanks, Alpha >> >> >> >> >> >> >> >> >> Great, that works........ Except that I don't think it's getting me the
records that I want. I need to select for oen distinct VNAME record that has the most recent d_DateTime. I think the script I have below just select a distinct VName and then plug in the MAx DATE and then the Max Odometer which each can come from different records with the same VNAME. Anyway to do what I want. Thanks a lot, Alpha Show quote "Jerry Spivey" wrote: > Alpha, > > Try OPENROWSET. From SQL Server BOL: > > C. Use the Microsoft OLE DB Provider for Jet > This example accesses the orders table in the Microsoft Access Northwind > database through the Microsoft OLE DB Provider for Jet. > > > > Note This example assumes that Access is installed. > > > USE pubs > GO > SELECT a.* > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', > 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) > AS a > GO > HTHJerry"Alpha" <Al***@discussions.microsoft.com> wrote in message > news:1876CCCD-5CC0-4185-85FA-A755936B7C62@microsoft.com... > > Is link server the only option to get the Access data? My application > > looks > > in a directory where each day a new Access file is created with file name > > inlcuding the date. I use the most recent file each time the user start > > my > > application and needs to update my database. So you see, the link server > > won't work for me because it needs to specify the file location. Unless, > > I > > would delete the link server and create a new one each time my application > > starts. But that seems odd and is there even a way to delete the > > linkedserver? > > > > Thanks, Alpha. > > > > "Jerry Spivey" wrote: > > > >> I usually add a linked server definition for the Access database. Take a > >> look at sp_addlinkedserver in the SQL Server Books Online - there is an > >> example of how to create one there. > >> > >> HTH > >> > >> JErry > >> "Alpha" <Al***@discussions.microsoft.com> wrote in message > >> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@microsoft.com... > >> >I got error message that it doesn't like the database in "From". Do you > >> >know > >> > how to specify a tabe from Access database? > >> > SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]), > >> > [Data].[d_OdometerTenths] > >> > FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830Db.mdb;].DATA > >> > WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail) > >> > GROUP BY [Data].[d_RemoteName] > >> > > >> > Thank you, > >> > Alpha > >> > > >> > "Jerry Spivey" wrote: > >> > > >> >> Alpha, > >> >> > >> >> Try: > >> >> > >> >> SELECT VName, MAX(d_DateTime) > >> >> FROM yourtable > >> >> GROUP BY VName > >> >> > >> >> HTH > >> >> > >> >> Jerry > >> >> "Alpha" <Al***@discussions.microsoft.com> wrote in message > >> >> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@microsoft.com... > >> >> > Hi, I need to select and later Update a database with information I > >> >> > select > >> >> > from a Access 2000 database. In the following T-SQL, I want to > >> >> > select > >> >> > only > >> >> > one record for each VName not already exists in my database. Since > >> >> > each > >> >> > VName have many records in the table, I'm only looking with the > >> >> > record > >> >> > that > >> >> > has the most recenst date-time, d_DateTime. I keep getting error > >> >> > with > >> >> > this > >> >> > though. Appreciate it if someone can help me out here. > >> >> > > >> >> > Thanks, Alpha > >> >> > >> >> > >> >> > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics