Problem with select in Access

Hello!

I use the following query in a SQL server database and it works fine. My 
problem is I now need it to work with Access as well.

Select Units.UnitId, Data.Resistance
From Units
Left Outer Join Data On Data.UnitId = Units.UnitId
And Data.DataDateTime = (Select Max(DataDateTime) From Data where 
Data.UnitId = Units.UnitId)

There are many rows in the Data table for each row in Units table and I only 
want the row with the latest date!

Wher trying to run it in access i get syntax error.

Regards, Mikael
0
Mikael
1/23/2009 5:51:31 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

2 Replies
619 Views

Similar Articles

[PageSpeed] 58
Get it on Google Play
Get it on Apple App Store

Jet engine is not as capable with SQL as SQL server is

Try this version of the query

Select U.UnitId, D.Resistance
From ( Units U
Left Outer Join (Select UnitID, Max(DataDateTime)  as MaxD From Data  group by unitID ) D1
 on D1.unitID = U.UnitID )
left  Join  Data D on D1.unitID = D.UnitID and D.DataDateTime = D1.MaxD




> Hello!
> 
> I use the following query in a SQL server database and it works fine. My 
> problem is I now need it to work with Access as well.
> 
> Select Units.UnitId, Data.Resistance
> From Units
> Left Outer Join Data On Data.UnitId = Units.UnitId
> And Data.DataDateTime = (Select Max(DataDateTime) From Data where 
> Data.UnitId = Units.UnitId)
> 
> There are many rows in the Data table for each row in Units table and I only 
> want the row with the latest date!
> 
> Wher trying to run it in access i get syntax error.
> 
> Regards, Mikael
0
Brian
1/24/2009 1:18:00 AM
Thanks! It works. Just what I needed.

Mikael
0
Mikael
1/24/2009 12:40:04 PM
Reply:

Similar Artilces:

ADO Select Problem
Greetings, For years, I've used the following construct for indexed, single-row retrievals: $sh = $dbh->prepare("select col from tbl where key = ?"); $sh->execute(); @ar = $sh->fetchrow_array(); if ($#ar > -1) {... I got bit today by a problem either in ADO.pm (2.96) or ADO itself. Very small sample program to reproduce problem: #!/usr/bin/perl -w use DBI; use strict; $|++; my $wbh = DBI->connect("dbi:ADO:Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=db", 'user', 'pass', { RaiseError => 0, PrintError =&g...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

Problem on ASA with Delphi via ADO
Hello, I just installed ASA 60 days trial and I am testing with Delphi 6 and ADO, using TADODataset components. I am getting the following problems: when I post any record, trailing blanks are inserted in every TStringField (VARCHAR in ASA). When I post records with TMemoField or TBlobField (LONG VARCHAR), it raises an "Count field incorrect". I did't see this behaviour in other servers. Regards, Frank Hi, mybe it's help: //Procedure tForm1.SaveBtnClick(Sender:TObject); // //var // fs : TFileStream; // bs : TBlobStream; // //Begin //with Table1...

Complex select statement problems with Access
Hey there, I'm creating a site where I would like to be able to search for a vendor based off distance from a certain location. I have a database with zips, long, lats, town, city, etc. I also have one with all my vendor info. My friend and I have gotten this to work with SQL on his machine at work but I need to run this from a machine using Access at my job. I’m using VB.net with .NET 1.1 My db has the following info. Database: Domtrucks Table: Trucks Columns: Truckid, Company, Truckname, City, State Database: Domtrucks Table: Zipcodes Columns: ZipCode, ZipcodeTy...

Delphi 5+ ADO ASAProv Problem
Hello, I try to connect from Delphi to ASA 7.03 with ADO. Via the ODBC Provider everthing seems to work. When I try to use ASAProv I can connect, but after connect the mode property of the connection is forced to cmUnknown and any attempt to open a result set lead to the error "Object or provider is not capable of performing requested operation". This is the same if I use the Connection string dialog or if connect manually ADOConnection1.ConnectionString:='Provider=ASAProv.70;Password=sql;Persist Encrypted=True;Persist Security Info=True;User ID=dba;Data Sourc...

problem selecting from access database with WHERE = somedate
I have created a reservations page for a calendar that is checking for available dates in an Access database. The code is checking for events that are scheduled before and after the selected start/end dates/times, but everytime I run the page, the code trips up on the format of the date/time. The database is storing the start and end dates and times in a single field in this format "03/14/2005 03:02:00 PM". For the sql I have tried formatting the date as a string with single quotes, single quotes and the # sign, as a date, as a date with the # sign, as datetime.parseexact, as ...

Is there a problem accessing SQLA6 with Delphi 4?
Before we jump into SQLA 6, I would like to find out if there are any problems with SQLA6 and Delphi 4? 1- Can all tables be properly accessed (esp. blob fields)? 2- Besides ODBC can we access now SQLA with SQL native links? 3- Are there any performance improvements? TIA Hallo PKJR schrieb in Nachricht ... >Before we jump into SQLA 6, I would like to find out if there are any >problems with SQLA6 and Delphi 4? >1- Can all tables be properly accessed (esp. blob fields)? Table has problems with blob fields ( cut ~ 400 Byte) TQuery works >2- Besides ODB...

Problem with using access and ado.net
i am having a problem with this following example of usng a an access databse and desginng a vb.net front form in visual studio.net.. Below is the code everything compiles however i don't understand why i am not getting anything from the database in to the text boxes on the form.. Public Class fclsMain Inherits System.Windows.Forms.Form Private m_cnAdoNetConnection As New OleDb.OleDbConnection Private m_daDataAdapter As New OleDb.OleDbDataAdapter Private m_cbCommandBuilder As OleDb.OleDbCommandBuilder Private m_dtContacts As New DataTable Priva...

Problem accessing Outlook from Delphi 2007
I hope someone can help with this. I have a couple out of many users getting this error message when attempting to access Outlook's contact list... an outgoing call cannot be made since the application is dispatching an input synchronization Has anyone seen this ? Can you help me resolve ? Thanks JT ...

Accessing the selected value from a Select
Need to access the selected value from a Select in client script.  I can get the selectedIndex but where do I find the selected value? Will this give it to me? form1.Select1.Options(form1.Select1.SelectedIndex)   form1.Select1.options[form1.Select1.selectedIndex] Try form1.Select1.options[form1.Select1.selectedIndex].value> penny for a thought...

Problem in accessing access database
Hi  I am establishing connection with MS access. MS Access Database is placed in the same directory as the project.Connection string is " "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=testdb.mdb"   But I am getting the error  the file "testdb.mdb" not found   Can anyone please tell me what is wrong with the connection string Regards KaranRegardsKaran GuptaFor .net,Javascript and Sql server articles and interview question visithttp://guptakaran21.googlepages.com/main Try putting the DB in the APP_Data folder or changing 'testdb.mdb&#...

Problems, problems, problems
Name: Product: Firefox Summary: Problems, problems, problems Comments: I use FF all the time for work - I'm a software tester - the add-ons are invaluable. But v3.5 really has been a disappointment. It just keeps hanging all the time. And when Internet Explorer windows pop up, FF often just dies. It can't even restart itself - I have to manually kill the process every time; every update, every add-on that updates, "restart" fails - it's so frustrating. I hope v3.6 will be addressing this - PLEASE! Browser Details: Mozilla/4.0 (compatible; MSIE 7....

Problems, problems, problems
Name: Hans Halvorsen Email: capricorn_at_online.no Product: Thunderbird Summary: Problems, problems, problems Comments: Hi, I downloaded Thunderbird some time ago, and I was over-satisfied whit it and how well it worked. In fact it worked 100% PERFECTLY UNTIL I got an newer update version of it. Since then I' ve had nothing BUT trouble! I've checked adresses, notes, password, deleted accounts and made new one's, checked sign-ins and sign-outs, passwords inside and out, upside and down, so many times that my frustration now is about to turn into pure and major ...

Problems Problems Problems
So, I have OpenSUSE installed on my laptop and everything is working well, except the wireless. It almost seems that every time I run a KDE distro I can not get the internet to work. I've run YaST and used iwconfig. nothing is working for me. Please help -ZAD -- zadnik18 ------------------------------------------------------------------------ someone is going to tell you to supply some more information 'My wireless doesn't work - a primer on what I should do next - openSUSE Forums' (http://tinyurl.com/6peanc) this is one of the stick...

Web resources about - Problem with select in Access - embarcadero.delphi.ado

Is–ought problem - Wikipedia, the free encyclopedia
The is–ought problem in meta-ethics as articulated by Scottish philosopher and historian David Hume (1711–76) is that many writers make claims ...

Corporate Australia's real problem: ancient companies
Our biggest companies are pretty aged, compared to the dynamic corporate giants that power the world's biggest economy.

Report claims Samsung Galaxy S7 will fix the biggest problem with the Galaxy S6
... Samsung ditched microSD support on the Galaxy S6. Well according to a new report on Monday, Samsung will fix the bigger of those two problems ...

"So the product is being deliberately misused despite a specific warning label, and y'all are complaining ...
"Nobody was irreparably broken or even like stubbed their toe? BURN BUNCH EMS DOWN. Bomb this company back to the stone age before the hair of ...

Acampora on problems in the market
Master technician Ralph Acampora says he has some doubts about this "fractured" market.

President Obama’s Propaganda Problem
The only thing Obama understands is that the critics of America always have a point and the critics of his administration never do.

New York's anti-terror reporting app could easily cause problems
New York state is understandably more anxious than ever about terrorist threats, and it's hoping that some mobile technology will bolster its ...

Dell laptops may have a Lenovo Superfish-size security problem
New data suggests that Dell, like Lenovo, may have been shipping compromised systems for the past few months. Research on the size and scope ...

The 'Design Sprint' Approach To Solving Problems In Your Organization
Innovation is a goal for many organizations, but how do you actually get people in a room, constructively and innovatively solving problems? ...

Family evicted, building deemed unlivable: Landlord cannot fix problems, he was murdered
... A Milwaukee family has been forced to leave their apartment. Their building has been declared unlivable. The landlord was required to fix problems ...

Resources last updated: 11/28/2015 1:07:35 AM