p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: to display the latest records per project


Message #1 by karl.hammett@b... on Tue, 4 Sep 2001 11:18:28 +0100
hi 



i have a database with records that are constantly updated but the old

records

need to be kept for history.



example



programmes have many projects and each project has a milestone. (e.g

inception, feasibility etc) and the planned date to complete the task.



i can generate a query to show all the projects and milestones but i need a

query to only show the latest

milestone for each of the different projects.



i have tried to write a query to get the latest ID for each project but i

come up with nothing.

i have also tried the same on the latest date.

i have to show the details on the web and would be gratefull for any help

thanks 



karl



Message #2 by "John Ruff" <papparuff@c...> on Tue, 4 Sep 2001 06:41:46 -0700
If each milestone also has a date/time field then create a query that

with the programmer,milestone, and date/time.  Group the query on each

programmer and max date/time.  



-----Original Message-----

From: karl.hammett@b... [mailto:karl.hammett@b...] 

Sent: Tuesday, September 04, 2001 3:18 AM

To: Access

Subject: [access] to display the latest records per project





hi 



i have a database with records that are constantly updated but the old

records need to be kept for history.



example



programmes have many projects and each project has a milestone. (e.g

inception, feasibility etc) and the planned date to complete the task.



i can generate a query to show all the projects and milestones but i

need a query to only show the latest milestone for each of the different

projects.



i have tried to write a query to get the latest ID for each project but

i come up with nothing. i have also tried the same on the latest date. i

have to show the details on the web and would be gratefull for any help

thanks 



karl
Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Tue, 04 Sep 2001 07:50:24 -0700
I can think of two ways to go with this--one where you use two queries, and

one where you use a subquery.  The two-query solution is probably easiest.

Pretend with me that your table is called tblProjectMilestones, and is set

up like so:



ProjectName

MilestoneText

MilestoneDate



Let's say that each combination of ProjectName + MilestoneDate is unique in

the table.  The first query looks like so:



qryLastProjectMilestoneDate:

SELECT  ProjectName, Max(MilestoneDate) as LastMilestoneDate

FROM    tblProjectMilestones

GROUP BY ProjectName ;



Then you join this to the original table to pick out the appropriate

Milestone text:



qryLastProjectMilestone:

SELECT  t.ProjectName, MilestoneText as LastMilestone, LastMilestoneDate

FROM    tblProjectMilestones        as t INNER JOIN

        qryLastProjectMilestoneDate as q

ON      t.ProjectName   = q.ProjectName AND

        t.MilestoneDate = q.LastMilestoneDate ;



HTH,



-Roy



-----Original Message-----

From: karl.hammett@b... [mailto:karl.hammett@b...]

Sent: Tuesday, September 04, 2001 3:18 AM

To: Access

Subject: [access] to display the latest records per project





hi 



i have a database with records that are constantly updated but the old

records

need to be kept for history.



example



programmes have many projects and each project has a milestone. (e.g

inception, feasibility etc) and the planned date to complete the task.



i can generate a query to show all the projects and milestones but i need a

query to only show the latest

milestone for each of the different projects.



i have tried to write a query to get the latest ID for each project but i

come up with nothing.

i have also tried the same on the latest date.

i have to show the details on the web and would be gratefull for any help

thanks 



karl
Message #4 by karl.hammett@b... on Tue, 4 Sep 2001 16:38:52 +0100
thanks alot roy



that code worked a treat.



many thanks 



karl



-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: Tuesday, September 04, 2001 15:50

To: Access

Subject: [access] RE: to display the latest records per project





I can think of two ways to go with this--one where you use two queries, and

one where you use a subquery.  The two-query solution is probably easiest.

Pretend with me that your table is called tblProjectMilestones, and is set

up like so:



ProjectName

MilestoneText

MilestoneDate



Let's say that each combination of ProjectName + MilestoneDate is unique in

the table.  The first query looks like so:



qryLastProjectMilestoneDate:

SELECT  ProjectName, Max(MilestoneDate) as LastMilestoneDate

FROM    tblProjectMilestones

GROUP BY ProjectName ;



Then you join this to the original table to pick out the appropriate

Milestone text:



qryLastProjectMilestone:

SELECT  t.ProjectName, MilestoneText as LastMilestone, LastMilestoneDate

FROM    tblProjectMilestones        as t INNER JOIN

        qryLastProjectMilestoneDate as q

ON      t.ProjectName   = q.ProjectName AND

        t.MilestoneDate = q.LastMilestoneDate ;



HTH,



-Roy



-----Original Message-----

From: karl.hammett@b... [mailto:karl.hammett@b...]

Sent: Tuesday, September 04, 2001 3:18 AM

To: Access

Subject: [access] to display the latest records per project





hi 



i have a database with records that are constantly updated but the old

records

need to be kept for history.



example



programmes have many projects and each project has a milestone. (e.g

inception, feasibility etc) and the planned date to complete the task.



i can generate a query to show all the projects and milestones but i need a

query to only show the latest

milestone for each of the different projects.



i have tried to write a query to get the latest ID for each project but i

come up with nothing.

i have also tried the same on the latest date.

i have to show the details on the web and would be gratefull for any help

thanks 



karl

  Return to Index