Select Query

J

Joel

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
 
J

Joel

Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

Ofer said:
Change the query to a group by query with max on the date Field


Joel said:
I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
 
F

FSt1

posst the sql

FSt1

Joel said:
Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

Ofer said:
Change the query to a group by query with max on the date Field


Joel said:
I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


FSt1 said:
posst the sql

FSt1

Joel said:
Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

Ofer said:
Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
F

FSt1

hi,
i do see where you selected
Max(InspectionHistory.Date) AS MaxOfDate,
but i don't see where you grouped by the MaxOfDate.
there should be only one maxofdate to group by.

regards

FSt1

Joel said:
ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


FSt1 said:
posst the sql

FSt1

Joel said:
Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

:

Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

When you run the query, check the values in the fields in two rows that you
think that should have been only one, and see which field doesn't repeat
itself.
In this field you have to decide which value you want to keep ( first, last,
max, min)

Joel said:
ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


FSt1 said:
posst the sql

FSt1

Joel said:
Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

:

Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

I added the group MaxofDate and it changed it to Expr2: [MaxofDate] and I set
it to grouped by and when I run the query i get a msgbox:

Enter parameter value:
MaxofDate


FSt1 said:
hi,
i do see where you selected
Max(InspectionHistory.Date) AS MaxOfDate,
but i don't see where you grouped by the MaxOfDate.
there should be only one maxofdate to group by.

regards

FSt1

Joel said:
ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


FSt1 said:
posst the sql

FSt1

:

Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

:

Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

I checked that and the field that isnt the same is the Date field.
I want to keep the most resent date from my report. I have tried to use max
date but it still returns both, I think it might have something to do with my
relationships but Im not sure on that.

Ofer said:
When you run the query, check the values in the fields in two rows that you
think that should have been only one, and see which field doesn't repeat
itself.
In this field you have to decide which value you want to keep ( first, last,
max, min)

Joel said:
ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


FSt1 said:
posst the sql

FSt1

:

Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

:

Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

The sql look OK, are you sure that the field NextInspectionDate is the same
in both records, or the formula with the dateadd

Joel said:
I checked that and the field that isnt the same is the Date field.
I want to keep the most resent date from my report. I have tried to use max
date but it still returns both, I think it might have something to do with my
relationships but Im not sure on that.

Ofer said:
When you run the query, check the values in the fields in two rows that you
think that should have been only one, and see which field doesn't repeat
itself.
In this field you have to decide which value you want to keep ( first, last,
max, min)

Joel said:
ok here is the novel =P

SELECT MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
Max(MainUnitData.InspectionDate) AS MaxOfInspectionDate,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, Max(InspectionHistory.Date) AS MaxOfDate,
InspectionHistory.InspectionTypeName, InspectionHistory.InspectionTypeID,
InspectionHistory.CGAName, InspectionHistory.CGAID,
InspectionHistory.AgentName, InspectionHistory.AgentProjectID,
InspectionHistory.BPWorkOrderID, InspectionHistory.InspectionID,
InspectionHistory.StructureID, InspectionHistory.Good,
InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]) AS Expr1

FROM MainAreaData INNER JOIN ((InspectionFrequency INNER JOIN MainUnitData
ON InspectionFrequency.InspectionFrequencyID =
MainUnitData.InspectionFrequencyID) INNER JOIN InspectionHistory ON
MainUnitData.StructureID = InspectionHistory.StructureID) ON
MainAreaData.UnitID = MainUnitData.UnitID

GROUP BY MainAreaData.FacilityOwner, MainAreaData.FacilityOwnerID,
MainAreaData.FacilityName, MainAreaData.FacilityID, MainAreaData.AreaName,
MainAreaData.AreaID, MainAreaData.UnitName, MainAreaData.UnitID,
MainUnitData.UnitID, MainUnitData.InspectionFrequencyID,
MainUnitData.InspectionFrequencyName, MainUnitData.StructureTypeID,
MainUnitData.StructureTypeName, MainUnitData.YearInstalled,
MainUnitData.StructureStatusID, MainUnitData.StructureStatusName,
MainUnitData.StructureMaterialID, MainUnitData.StructureMaterialName,
MainUnitData.StructureUsageID, MainUnitData.StructureUsageName,
MainUnitData.UnitName, MainUnitData.StructureID, MainUnitData.Description1,
MainUnitData.Description2, MainUnitData.Remarks, MainUnitData.NoActionReq,
MainUnitData.ImplementMaintenanceRepairs,
MainUnitData.PreformAdditionalInspections,
MainUnitData.PreformEngineeringAnalysis,
MainUnitData.DevelopRemedialDesignDetails,
MainUnitData.FurtherInvestigationReq, MainUnitData.[Adj Inspection Freq
Increase], MainUnitData.[Adj Inspection Freq Decrease],
MainUnitData.OtherExplain, MainUnitData.Explain,
MainUnitData.InspectionTypeName, MainUnitData.InspectionTypeID,
MainUnitData.NextInspectionDate, MainUnitData.PeriodType,
MainUnitData.Frequency, MainUnitData.NextInspectionType,
MainUnitData.NextInspectionTypeID, MainUnitData.GeneralDiscription,
MainUnitData.EstManHours, InspectionHistory.InspectionTypeName,
InspectionHistory.InspectionTypeID, InspectionHistory.CGAName,
InspectionHistory.CGAID, InspectionHistory.AgentName,
InspectionHistory.AgentProjectID, InspectionHistory.BPWorkOrderID,
InspectionHistory.InspectionID, InspectionHistory.StructureID,
InspectionHistory.Good, InspectionHistory.Fair, InspectionHistory.Poor,
InspectionHistory.Indeterminate, InspectionFrequency.InspectionFrequency,
InspectionFrequency.InspectionFrequencyID, InspectionFrequency.PeriodType,
InspectionFrequency.Frequency,
DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[Date]);


:

posst the sql

FSt1

:

Thank you for your guys responces but MAX date doesnt work for this query.

It still returns all records.

I can post the SQL statement if you like.

:

Change the query to a group by query with max on the date Field


:

I have a query that joins 4 tables together for a report for all inspections.
One of these tables stores the history of Inspections. (buildingID,date,
ect..)
This works great except that if the same building was inspected twice, it
shows up twice on the report.
I need a way to only display the most recent date for that building.
I have tried using a SELECT DISTINCT command but I cant get it to just
select the [buildingID] and [date] fields.

Anyone know how I can accomplish this?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

Let me clarify my situation a little. (just so you guys can get a better
picture of what im trying to do)

Lets say there is 2 inspections of a structure, lets call it structure 0001.

Now, there can be multipule inspections for this structure, each are dated.

so in my query i get 2 lines, structure 0001 date 1/5/2000
and structure 0001 date 2/38/2006

I need to just display the lastest inspection date for my report.
 
Top