|
 |
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
|
|
|
View Cart
