Newest dates based on criteria

[benjammin]

I'm not really sure how to explain myself on this one but here it goes.




In my calibration database I have enteries for calibration checks going back years, so multiple calibrations for each instrument. When I create a query it shows ALL of these records and I really only want it to show the newest ones. Some instruments need calibration every month, some are every 6 months.


In one instance, I have a well with a conductivity sensor and a water level sensor, they were recently calibrated on 04/24/2012 and before that on 10/18/2012. When I create my query it shows records for not only 04/24 but the old ones on 10/18 as well.


How can I make it show only the newest records?


Thanks

[June7]

Do you want only latest record for each equipment? Could try the TOP qualifier. Requires subquery to get TOP for each equipment. Review http://allenbrowne.com/subquery-01.html

[benjammin]

Kind of... basically i'd like to do it like this, there's equipment type and the well that equipment is in. Basically I'd like the query to recognize if there's multiple records for transducers in well XXX then pull the latest record only. I.e. water level transducer in well 0688 has been calibrated 3 times (04/12/2011, 10/18/2010, 4/10/2010) I want it to display the 04/12/2011 record only.

Right now I have a well, 0688, it has 4 records, conductivity check on 4/24/12, transducer check on 4/24/12, transducer check on 10/18/11 and conductivity check on 10/18/11. When I run the query, no matter what, using first total and ascending sort all these records show up when I just want to see the two records from 4/24/12.

I thought it would be simple using totals but it's not because if enter in current data and then enter in historical data the last function will pull the last records which are historical and not correct. The simple solution I suppose would be to make users enter in all historical data first but that doesn't seem feasible. Thanks for your help.

[orange]

Tell us more about your table--what fields are involved.

Query would have this sort of pattern:

Select * from yourTable
where Well = yourWellNumber and
EquipType = yourEquipType and
CalibrationDate = (Select Max(CalibrationDate from yourTable Well = yourWellNumber and EquipType = yourEquipType)

[benjammin]

There are my tables

I want the query to return only the newest records from tblCalLog for each type (transducer, conductivity, etc...) but not the past records and for all wells. Basically how it doesn't really work now is this: User selects a SITE from the combo box, we'll say RIFLE, it then goes through and pulls all calibration records for RIFLE but right now it's pulling old records that don't matter for our calibration schedule (i.e. we only care about the newest records since that will tell us when to go to check the instruments next) and it's nice to know the well they're in. The other possibility is the user selects ALL the sites to run a full report of what instruments have been and need to be calibrated for every site.



That's the output for site green river, this site does not have any conductivity sensors but see how it displays old records as well as the new ones, using the first/last totals it seems to just pick things at random, not the actual last dates

[orange]

It would facilitate things if you went to the query that produced the results you showed;
then went to the SQL view and copied the SQL; then posted that SQL.

At this point I don't know what a transducer sensor is from a Gillette Sensor.

You show me the query SQL, and the output you're getting now;
the output you want; and I'll try to show you how to adjust your query.

[benjammin]

SELECT tblCalLog.CalibrationID, tblSite.SiteID, tblSite.SiteName, tblModel.ModelName, tblType.EquipmentType, tblStation.StationName, tblWells.WellName, tblCalLog.CalTime AS CalTime, First(DateAdd("m",[tblType]![CalibrationInterval],[tblCalLog]![CalTime])) AS NextCalFROM tblType INNER JOIN (tblModel INNER JOIN (tblSite INNER JOIN (tblWells RIGHT JOIN (tblStation INNER JOIN (tblCalLog INNER JOIN tblEquipment ON tblCalLog.EquipmentID = tblEquipment.EquipmentID) ON tblStation.StationID = tblEquipment.StationID) ON tblWells.WellID = tblEquipment.WellID) ON tblSite.SiteID = tblEquipment.SiteID) ON tblModel.ModelID = tblEquipment.ModelID) ON tblType.TypeID = tblEquipment.TypeID
GROUP BY tblCalLog.CalibrationID, tblSite.SiteID, tblSite.SiteName, tblModel.ModelName, tblType.EquipmentType, tblStation.StationName, tblWells.WellName, tblCalLog.CalTime, tblEquipment.Active
HAVING (((tblSite.SiteID)=[Forms]![frmCalMenu].[cboSiteList]) AND ((tblEquipment.Active)=True)) OR (((First([Forms]![frmCalMenu].[cboSiteList])) Is Null));

It doesn't matter what kind or what a transducer is, just that it's different from the next thing, i.e. conductivity sensor. I want the query to output only the newest calibration dates and leaving out past ones, it's hard to explain

[orange]

Well it's hard to mock up a situation that you don't understand and for which not all data and datatypes were supplied. However, I did mock up a database along the lines of your situation. I did not include all of the tables, because your concern was getting the latest calibration info only for the equipment. So I used enough tables and data to illustrate the query set up to get only the latest calibration record for the equipment. I did not use the Active field. I made up Sites and Sitenames etc as needed
The attached jpg shows the relationships, table structures and the data values I used based on what I found in your post. Also in the jpg is the output of the query.

The SQL for the query follows:

SELECT tblEquipment.EquipmentId
, tblCalLog.CalTime
, tblEquipment.WellId
, tblWells.WellName
, tblSites.SiteId
, tblSites.SiteName
FROM tblSites INNER JOIN (tblWells INNER JOIN (tblEquipment INNER JOIN tblCalLog ON
 tblEquipment.EquipmentId = tblCalLog.EquipmentId) ON
 tblWells.WellId = tblEquipment.WellId) ON 
tblSites.SiteId = tblWells.SiteId
WHERE (((tblCalLog.CalTime)=(Select max(Caltime) from tblCalLog 
                                         where tblCallog.equipmentId = tblEquipment.Equipmentid)));

Good luck with your project.

[benjammin]

Orange, that seems so simple and works so well!

I have some records in tblCalLog that are NOT tied to an equipmentID, is it possible to display those too?

[orange]

Orange, that seems so simple and works so well!

I have some records in tblCalLog that are NOT tied to an equipmentID, is it possible to display those too?

Glad to help.
I'm confused. You said

so multiple calibrations for each instrument

How could you have a calibration that is not related to a piece of Equipment? What are you calibrating if not equipment ( the plot thickens)?

[benjammin]

I guess calibration is the wrong word but you bring up a good point, i'm not really concerned with calibration checks that aren't associated with instruments. I'm going to mark this as solved until i run into a problem. This helped me out a lot, now the calibration data can be input!

[orange]

OK but I 'd be cautious of calibrations that don't relate to equipment. It's possible that some old equipment has been discarded, but the whole essence of referential integrity seems compromised. If there isn't a valid equipment reference, there shouldn't be a calibration record for it.

Let's hope it doesn't show up as an issue.

[benjammin]

You're right, all calibrations will relate to equipment.

One problem i'm having with that query is that if the equipment doesn't have an associated wellID it won't show up. Some instruments aren't associated with a well because they're not in a well...

[orange]

benjammin,
You have to decide which question your query is addressing:

Which equipment has a calibration?

Which equipment has a calibration and is currently in a well?

They're 2 different questions.