Big problem that shouldn't be

[ramprat]

Hi Everyone,

I seem to be having a problem with something that should be quite simple. I have a table (AADT_History) with the following fields:

PCID
AADT
UPDATE_YEAR
OLD_PCID

There can be multiple records for any PCID as it gets a new record added to the table each year. What I am trying to do is to select the most recent record (highest update_year) for each PCID but only where the OLD_PCID field is not null. I have other criteria to further limit the PCIDs to those specific to a certain city (DMA_NUM). The code I'm using is below but I'm finding that it often picks the record with the max Update_Year even though the OLD_PCID is blank. For example I could have a record with an Update_Year of 2007 with the Old_Pcid field populated but my query will return the record with an Update_Year of 2008 even though the Old_Pcid field for this record is blank. I've tried changing the criteria for the old_Pcid to be > 0 in case my field wasn't really blank but that didn't help. Any ideas on what I'm doing wrong?

SELECT Max(AADT_History.UPDATE_YEAR) AS MaxOfUPDATE_YEAR, First(AADT_History.PCID) AS FirstOfPCID
FROM Counties INNER JOIN (AADT_History INNER JOIN Count_Stations ON AADT_History.PCID = Count_Stations.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS
WHERE (((Counties.DMA_NUM)=[forms]![login_form]![dma_textbox]) AND ((AADT_History.OLD_PCID) Is Not Null));

[puppydogbuddy]

Try this, assuming that [dma_textbox] returns text data type, and that this is code behind a form event procedure.

If Not IsNull(OLD_PCID) Then
       "SELECT Max(AADT_History.OLD_PCID) AS MaxOfUPDATE_YEAR, First(AADT_History.PCID) AS FirstOfPCID 
FROM Counties INNER JOIN (AADT_History INNER JOIN Count_Stations ON AADT_History.PCID = Count_Stations.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS 
WHERE (((Counties.DMA_NUM)=[forms]![login_form]![dma_textbox])";  

Else 
      "SELECT Max(AADT_History.UPDATE_YEAR) AS MaxOfUPDATE_YEAR, First(AADT_History.PCID) AS FirstOfPCID 
FROM Counties INNER JOIN (AADT_History INNER JOIN Count_Stations ON AADT_History.PCID = Count_Stations.PCID) ON Counties.CNTY_FIPS = Count_Stations.CNTY_FIPS 
WHERE (((Counties.DMA_NUM)=[forms]![login_form]![dma_textbox])";  
End If

[ramprat]

Thanks I'll try it and see if it works.