473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Only displaying latest date

Hello,

I have a table that has five categories: Unit No, Date, P1, P2, P3.
I have a query that only extracts data from the table that is over 40 for P1, P2 or P3 but I only want the data with the latest date for that particular Unit No to come up. Any Ideas?
Nov 8 '07 #1
8 2083
Jim Doherty
897 Expert 512MB
Hello,

I have a table that has five categories: Unit No, Date, P1, P2, P3.
I have a query that only extracts data from the table that is over 40 for P1, P2 or P3 but I only want the data with the latest date for that particular Unit No to come up. Any Ideas?

Assuming you have a table called MyTable with the fields you described

The following SQL will return all data for the maximum date for each Unit No. (You can add your 'extra' criteria for greater than 40 or whatever directly into this when you see it in design) Obviously replace the MyTable reference to whatever your tablename is

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT MyTable.[Unit No], MyTable.Date, MyTable.P1, MyTable.P2, MyTable.P3
  3. FROM MyTable
  4. WHERE (((MyTable.Date)=(SELECT Max([Date]) FROM MyTable MyMaxTable WHERE [Unit No]=MyTable.[Unit No])))
  5. ORDER BY MyTable.[Unit No];
  6.  
  7.  
Regards

Jim :)
Nov 8 '07 #2
Thanks Jim,

I have a couple of questions though

Why does the query when sorting the date out only read the first number.
i.e. if 01/10/2007 and 02/09/2007 it will display 02/09/2007even though it is of a earlier date.
Any ideas?

Also is there any way of only displaying the data with latest date and not displaying any other date of the same unit.
i.e. unit 250 is in the database on two seperate occasions 01/10/2007 and the 02/10/2007 then only the 02/10/2007 will be displayed.

thanks again
Nov 9 '07 #3
Jim Doherty
897 Expert 512MB
Thanks Jim,

I have a couple of questions though

Why does the query when sorting the date out only read the first number.
i.e. if 01/10/2007 and 02/09/2007 it will display 02/09/2007even though it is of a earlier date.
Any ideas?

Also is there any way of only displaying the data with latest date and not displaying any other date of the same unit.
i.e. unit 250 is in the database on two seperate occasions 01/10/2007 and the 02/10/2007 then only the 02/10/2007 will be displayed.

thanks again

Reference this:
Why does the query when sorting the date out only read the first number.
i.e. if 01/10/2007 and 02/09/2007 it will display 02/09/2007even though it is of a earlier date

I have no idea what you mean. The SQL displayed returns the maximum date for the column data for each unit as shown if the structure is as per that table mentioned in the SQL syntax

Reference this
Also is there any way of only displaying the data with latest date and not displaying any other date of the same unit.
i.e. unit 250 is in the database on two seperate occasions 01/10/2007 and the 02/10/2007 then only the 02/10/2007 will be displayed.

once again, the SQL returns the maximum date for the column data for each unit as shown if structure is how I mentioned so that is what it should be doing

Jim :)
Nov 9 '07 #4
My Mistake, it works as you said

regards
Nov 12 '07 #5
Hello,

I have a new problem based on the same table and query. For some reason the query returns numbers that are single digits (2, 3, 4) yet I only want numbers >40?

This is the SQL code that is used

SELECT Table1.[Unit No], Table1.[Date Tested], Table1.Pt1, Table1.Pt2, Table1.Pt3
FROM Table1
WHERE (((Table1.[Date Tested])=(SELECT Max([Date Tested]) FROM Table1 MyMaxTable WHERE [Unit No]= Table1.[Unit No])) AND (((Table1.Pt1)>"40") OR ((Table1.Pt2)>"40") OR ((Table1.Pt3)>"40"));
Nov 27 '07 #6
Jim Doherty
897 Expert 512MB
Hello,

I have a new problem based on the same table and query. For some reason the query returns numbers that are single digits (2, 3, 4) yet I only want numbers >40?

This is the SQL code that is used

SELECT Table1.[Unit No], Table1.[Date Tested], Table1.Pt1, Table1.Pt2, Table1.Pt3
FROM Table1
WHERE (((Table1.[Date Tested])=(SELECT Max([Date Tested]) FROM Table1 MyMaxTable WHERE [Unit No]= Table1.[Unit No])) AND (((Table1.Pt1)>"40") OR ((Table1.Pt2)>"40") OR ((Table1.Pt3)>"40"));

Its not the same table I worked on is it? fieldnames are different as is the table name. Yours has spaces in fieldnames whereas mine didn't.

Look at your criteria for starters in the sub query can you really be asking for Pt1 or Pt2 or Pt3 greater than the apparent numeric value of 40 if the criteria being applied is a text based comparison? (look at your speechmarks?) Check out your pt1,pt2,pt3 field datatypes in your table firstly. If you managed to run your query as is, then you most certainly have them as TEXT datatypes (had they been numeric Access would have thrown you a 'data mismatch' error where you then went on to ask to return records >"40".)

Look at your subquery syntax in how it is applying itself to the criteria for returning the maximum date...... your ANDS and OR are out of step you ask for the maximum date for DateTested where it has a pt1 value of >"40" but then ask for an OR where Pt2 >"40" but do NOT have an AND for Date Used where Pt2 >"40". The same lacking applies itself to pt3

Jim :)
Nov 27 '07 #7
Thanks Jim much appreciated
Nov 28 '07 #8
Jim Doherty
897 Expert 512MB
Thanks Jim much appreciated
You're very welcome

Jim :)
Nov 28 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Dave Posh | last post by:
I seem to be having a problem displaying time stored in mysql. The format stored in the database is 13:15:05. The database data type is time. I'm using asp vbscript and sql to retrieve the time...
3
by: jas | last post by:
> This is what I want to do with the data table below. > > I only want it to show one id for each issue. ( ie, 4001 only once, > 4002 only once, etc. But only the one with the latest date and...
1
by: maxhauser | last post by:
I have quick little page that I need to create but its sounds like the concept is not so quick. I have a folder with eight or ten files that can be displayed in a browser. I need to display the...
3
by: Fatz | last post by:
I have a table with a Date Field. This field is populated with the date and time an entry was made to the table. I am looking to create a query that pulls only the most recent record. I am...
1
by: asad | last post by:
hello how ru all, i have some problem in displaying xml file in asp.net, i successfully create an xml file but when want to display it through ASP.NET code it throwing following error. ...
4
by: John Suru | last post by:
I am loading up a combobox with data from an access database. The field I am loading is a date/time datatype. The data in the field is a short date(ex. 01/22/2004). When I load the combobox with the...
7
by: Scott Frankel | last post by:
Still too new to SQL to have run across this yet ... How does one return the latest row from a table, given multiple entries of varying data? i.e.: given a table that looks like this: color...
5
by: alanspamenglefield | last post by:
Hello group, I have an SQL statement which pulls data from a table as follows: " SELECT tblSites.sites_siteno, " & _ " tblSites.sites_sitename, " & _ " Sum(tblStockResults.stkr_result) AS...
24
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.