Query to return latest dates

[David Ball]

Hi Forum,
I have a query that returns equipment parts and the ?Arrived on Site? (AOS) dates for these parts.
There is also a column for the equipment number (Code) that each part belongs to. The idea is that when all parts of a piece of equipment have arrived, the equipment is considered to have arrived. This allows for scheduling the assembly of the equipment.
I would like to create a modified version of this query that shows the status of equipment without showing all the parts. I can do this by grouping by the Code column and selecting ?Max? in the Total row of the AOS column (to show the date of the last part received).
The problem is those items of equipment for which some parts have already arrived but some have not.
In these cases my query returns the latest date of the parts that have arrived, giving the false impression that all parts have arrived.
In cases where not all parts have arrived I need the query to show, for that item of equipment, no date in the AOS field.
Can I set my query up to do this? If so, how?

Thanks very much for any help with this.
Dave

 

[John W. Vinson]

Hi Forum,
I have a query that returns equipment parts and the ?Arrived on Site? (AOS) dates for these parts.
There is also a column for the equipment number (Code) that each part belongs to. The idea is that when all parts of a piece of equipment have arrived, the equipment is considered to have arrived. This allows for scheduling the assembly of the equipment.
I would like to create a modified version of this query that shows the status of equipment without showing all the parts. I can do this by grouping by the Code column and selecting ?Max? in the Total row of the AOS column (to show the date of the last part received).
The problem is those items of equipment for which some parts have already arrived but some have not.
In these cases my query returns the latest date of the parts that have arrived, giving the false impression that all parts have arrived.
In cases where not all parts have arrived I need the query to show, for that item of equipment, no date in the AOS field.
Can I set my query up to do this? If so, how?

Thanks very much for any help with this.
Dave

Please post the SQL of your query and the relationships between your tables.
We can't see them from here!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David Ball]

Hi,

SQL:

SELECT DISTINCT tblCODE.CODE, tblCODE.Area, tblCODE.EquipType, tblDescriptions.Description, tblTagNo.FOS, tblTagNo.AOS, tblIntermediateP6.ActivityID
FROM tblDescriptions LEFT JOIN ((tblTagNo RIGHT JOIN tblCODE ON tblTagNo.[Tag No] = tblCODE.[Tag No]) LEFT JOIN tblIntermediateP6 ON tblCODE.CODE = tblIntermediateP6.CODE) ON tblDescriptions.CODE = tblCODE.CODE
WHERE (((tblDescriptions.Description) Like [Forms]![frmSearchDesc]![Text0]));

I have tried to paste in the table relationships but they just don't appear???

Thanks very much.

Dave

 

[Bob Barrows]

Hi,

SQL:

SELECT DISTINCT tblCODE.CODE, tblCODE.Area, tblCODE.EquipType,
tblDescriptions.Description, tblTagNo.FOS, tblTagNo.AOS,
tblIntermediateP6.ActivityID
FROM tblDescriptions LEFT JOIN ((tblTagNo RIGHT JOIN tblCODE ON
tblTagNo.[Tag No] = tblCODE.[Tag No]) LEFT JOIN tblIntermediateP6 ON
tblCODE.CODE = tblIntermediateP6.CODE) ON tblDescriptions.CODE =
tblCODE.CODE WHERE (((tblDescriptions.Description) Like
[Forms]![frmSearchDesc]![Text0]));

I have tried to paste in the table relationships but they just don't
appear???

Script them in SSMS and post the scripts.

 

[Bob Barrows]

Hi,

SQL:

SELECT DISTINCT tblCODE.CODE, tblCODE.Area, tblCODE.EquipType,
tblDescriptions.Description, tblTagNo.FOS, tblTagNo.AOS,
tblIntermediateP6.ActivityID
FROM tblDescriptions LEFT JOIN ((tblTagNo RIGHT JOIN tblCODE ON
tblTagNo.[Tag No] = tblCODE.[Tag No]) LEFT JOIN tblIntermediateP6 ON
tblCODE.CODE = tblIntermediateP6.CODE) ON tblDescriptions.CODE =
tblCODE.CODE WHERE (((tblDescriptions.Description) Like
[Forms]![frmSearchDesc]![Text0]));

I have tried to paste in the table relationships but they just don't
appear???

Oops, disregard my prior reply - I thought I was still in the SQL Server
group.

Describe the relationships rather than attempting to post the picture. Also,
reply to your original post instead of starting a new thread (with a new
subject). It makes it easier to follow if we don't have to switch back and
forth between threads. Also, quote some of the message you are replying to
in order to provide context.

 

[Bob Barrows]

Hi Forum,
I have a query that returns equipment parts and the ?Arrived on Site?
(AOS) dates for these parts.
There is also a column for the equipment number (Code) that each part
belongs to. The idea is that when all parts of a piece of equipment
have arrived, the equipment is considered to have arrived. This
allows for scheduling the assembly of the equipment.
I would like to create a modified version of this query that shows
the status of equipment without showing all the parts. I can do this
by grouping by the Code column and selecting ?Max? in the Total row
of the AOS column (to show the date of the last part received). The
problem is those items of equipment for which some parts have already
arrived but some have not.
In these cases my query returns the latest date of the parts that
have arrived, giving the false impression that all parts have
arrived.
In cases where not all parts have arrived I need the query to show,
for that item of equipment, no date in the AOS field.
Can I set my query up to do this? If so, how?

Can you provide a few rows of sample data in tabular format (relevant
columns only) to illustrate the problem, followed by the results you wish to
obtain with your query, again in tabular format?