By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,903 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,903 IT Pros & Developers. It's quick & easy.

Only displaying latest date

P: 20
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
Share this Question
Share on Google+
8 Replies


Jim Doherty
Expert 100+
P: 897
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

P: 20
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
Expert 100+
P: 897
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

P: 20
My Mistake, it works as you said

regards
Nov 12 '07 #5

P: 20
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
Expert 100+
P: 897
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

P: 20
Thanks Jim much appreciated
Nov 28 '07 #8

Jim Doherty
Expert 100+
P: 897
Thanks Jim much appreciated
You're very welcome

Jim :)
Nov 28 '07 #9

Post your reply

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