only pull the newest date from a table

[Voodoo223]

I have a crosstab query that I ONLY need the newest date from the table I'm pulling from reguardless of what it is for. Here is what I have:

TRANSFORM Max([Cust Count].[Active Customers]) AS [MaxOfActive Customers]
SELECT [Cust Count].Node, Sum([Cust Count].[Active Customers]) AS [Total Of Active Customers]
FROM [Cust Count]
WHERE ((([Cust Count].Services) In ("core","data","telephone")) AND (([Cust Count].ASC)="uh") AND (([Cust Count].Date)=#6/12/2005#))
GROUP BY [Cust Count].Node, [Cust Count].Date
ORDER BY [Cust Count].Node
PIVOT [Cust Count].Services;

I know this is only setup to get items from 6/12/05 but more will be added to the table and I only want the new info for this query. Thank you.

 

[The_Doc_Man]

You can get the latest date if you have a true DATE field simply by using MAX([MyDateField]) function. If you wanted to find all dates equal to the latest date, you can do "... WHERE .... AND [MyDateField] = DMAX( "[MyDateField]", "MyTable" ) ... " or something similar.

If the date is text mode (i.e. display-date, not "real" date) then you have to do something a bit trickier, like perhaps MAX( CDate([MyTextDateField])) ... which is ugly but technically correct. (If you actually have a text-date field, consider converting it to a real date field to support this sort function better.)

 

[Voodoo223]

I'm running into a problem of pulling the newest date for all nodes. All I want to pull is the newest date. If the node isn't included in the newest update it has been dropped. I don't need a history of that for the query as it is kept in the table. It is in a true date/time field.

 

[casey]

If I read this post right, I'm trying to do the same thing; I would like to show the latest value entered for each particular ID, disregarding the records that have an earlier date.

So if records show:
fldEntityID fldValue fldDate
1 Amy 6.30.05
1 Ami 7.1.05
2 Ann 6.30.05

I want the results to show:
fldEntityID fldValue fldDate
1 Ami 7.1.05
2 Ann 6.30.05

Instead, my results are giving me the right date and the wrong value showing:
fldEntityID fldValue fldDate
1 Amy 7.1.05
2 Ann 6.30.05

The query:

TRANSFORM Last(tblEntityDetail.fldValue) AS LastOffldValue
SELECT tblEntityDetail.fldEntityID, Max(tblEntityDetail.fldDate) AS MaxOffldDate
FROM tblDetail RIGHT JOIN tblEntityDetail ON tblDetail.fldDetailID=tblEntityDetail.fldDetailID
GROUP BY tblEntityDetail.fldEntityID
PIVOT tblDetail.fldDetail In ("First Name");

The problem is that I get the values for the Last(fldValue) from one line and the Max(fldDate) from another line. So I may get the latest date, but the Value I get is the Last(fldValue) which Access sees as the "longest" value, I guess.

I really don't want to do a function on fldValue at all. I just want it to show up. The Max(fldDate) is the field that I want to do the function on, but to get the results I want using TRANSFORM, I think it's required to put some function on the fldValue????

It almost works correctly when I use the "WHERE .... AND [MyDateField] = DMAX( "[MyDateField]", "MyTable" )", but this only gives the records from the table with the maximum date. I would like all record ID's, but only those that were entered last.

Any ideas?

Voodoo223,
Hope I didn't mess up your post??? Didn't want to double post on same topic?! Let me know and I can remove my post. Didn't seem to be going anywhere though.

 

[Jon K]

So if records show:
fldEntityID fldValue fldDate
1 Amy 6.30.05
1 Ami 7.1.05
2 Ann 6.30.05

I want the results to show:
fldEntityID fldValue fldDate
1 Ami 7.1.05
2 Ann 6.30.05

Casey,

Solution to this part of your question: build two queries, run the second (qryTwo).

qryOne:-
SELECT [TableName].fldEntityID, Max([TableName].fldDate) AS MaxOffldDate
FROM [TableName]
GROUP BY [TableName].fldEntityID;

qryTwo:-
SELECT [TableName].fldEntityID, [TableName].fldValue, [TableName].fldDate
FROM [TableName] INNER JOIN qryOne ON ([TableName].fldDate = qryOne.MaxOffldDate) AND ([TableName].fldEntityID = qryOne.fldEntityID);


But don't remove your post, or it will make mine unrelated to this thread.
.

 

[casey]

Jon K,

Thanks for responding.

I would like to "tweak" the crosstab query that I submitted to get the results. I'm pulling header names from another table so I need to use the TRANSFORM option.

The query gives me the latest fldDate, but not the fldValue in the same record. I think that the part of the crosstab query...
TRANSFORM Last(tblEntityDetail.fldValue) AS LastOffldValue...is giving me the problem because the TRANSFORM option needs to have a "Total" value which I set to Last. Is there a way to replace the Last() function on fldValue to a "dummy" expression giving me the same results without actually doing a calculation on fldValue? If I could somehow run this same query without specifying a "Total" value Last(fldValue), I think it would work. Crosstab queries are unfamiliar to me so I'm learning as I go.

Are you familiar with crosstab queries?

 

[EMP]

Hi Casey,

Can you post a database with just the table, query and some sample data for us to have a look at it? Preferably in Access 97 or 2K format so that more people can open it.

^

 

[casey]

EMP,

Here's a sample of the database. qryEntityTest is similar to the query I've posted. If you look at the table tblEntityDetail, shows all the dates and values. I simply would like the latest value to show up in the query results. Any help you can offer will be greatly appreciated. Thanks.

Paul

 

[EMP]

Paul,

Your tblEntityDetail table is not normalized.

fldEntityID	fldDetailID	fldValue	fldDate		     fldInactive
1		First Name	Ami		6/29/2005 12:55:54 PM	No
1		First Name	Am		7/30/2005 12:55:54 PM	No
1		First Name	Amy		7/1/2005 12:56:54 PM	No
2		First Name	Ana		6/26/2005 12:55:54 PM	No
1		Last Name	Donahue		6/26/2005 12:55:54 PM	No
2		Last Name	Kamble		6/26/2005 12:55:54 PM	No
1		Hire Date	3/18/2003	6/26/2005 12:55:54 PM	No
2		Hire Date	1/1/2002	6/26/2005 12:55:54 PM	No
1		Status		Inactive	6/26/2005 12:55:54 PM	No
2		Status		Inactive	6/26/2005 12:55:54 PM	No

In the table, multiple attributes are put in the fldValue field and a DetailID is used to tell whether each Value is a First Name, a Last Name or a Hire Date, etc.

A normalized structure would put only one attribute in one field e.g.

tblEmployees
EmployeeID   FirstName   LastName   HireDate    DOB   Status
1	     Ami	 Donahue    3/18/2003	      Active

If you normalize your table, then you don't need to try to use a crosstab query to extract normalized data from a table which is not normalized.

EMP
^

 

[casey]

EMP,

Thanks for the reply.

The data is not normalized because it tracks the history of changes made to information and other things. It may seem naive, but I've done several "normalized" databases and that's not what I'm seeking here. I'm just interested in getting the query working.

 

[EMP]

Paul,

It seems to work when applying Jon K's solution in the crosstab query.

I have added a series of three queries in your database: qryOne and qryTwo are based on Jon K's solution, qryTwo_Crosstab is your original crosstab query with the tblEntityDetail table replaced by qryTwo.

qryTwo returns:-

fldEntityID  fldDetailID   fldValue	fldDate		      fldInactive
1	     First Name	   Am		7/30/2005 12:55:54 PM	No
1	     Last Name	   Donahue	6/26/2005 12:55:54 PM	No
1	     Hire Date	   3/18/2003	6/26/2005 12:55:54 PM	No
1	     Status	   Inactive	6/26/2005 12:55:54 PM	No
2	     First Name	   Ana		6/26/2005 12:55:54 PM	No
2	     Last Name	   Kamble	6/26/2005 12:55:54 PM	No
2	     Hire Date	   1/1/2002	6/26/2005 12:55:54 PM	No
2	     Status	   Inactive	6/26/2005 12:55:54 PM	No

qryTwo_Crosstab returns:-

fldEntityID	Date			First Name	Last Name
1		7/30/2005 12:55:54 PM	Am		Donahue
2		6/26/2005 12:55:54 PM	Ana		Kamble

The crosstab query works if you discard the Date field. The Date field is somewhat misleading as the changes to the First Name and Last Name of an entity may not be made on the same date.

^

 

[casey]

EMP and Jon K,

I've tried this and it works how it needs to. I appreciate all of your help getting this going. Thanks a hundred for your assistance.