Complex join problem

[Seth Schrock]

I have a situation where I'm trying to join a table back to itself to be able to get a record number within a specific month. The problem that I'm running into is when the PK field isn't in the same order as the date field. So for example, lets say that I have the following data:

AchID_pk,   EffectiveDate
3385        4/1/2016
3394        4/7/2016
23          4/14/2016
36          4/18/2016
43          4/21/2016
61          4/28/2016

I need to know I'm needing to know which record is the 6th one of that month. Normally, I use the PK field in a join to count the number of records with a lower PK value. However, I really need them in date order, but since there can be multiple records on the same date, I can't join using the date field. I tried using both the PK and the date fields in the join, but that didn't work either.

So here is what I have tried with the results (The YearMonth and EffectiveDateFormat fields format the date to be yyyymm so that I can split the count down to a specific month of a year):
Join:

ON (Main.CustId_pk = qryFileCount.CustId_fk) 
AND (Main.YearMonth = qryFileCount.EffectiveDateFormat) 
AND ((Main.AchFileId_pk >= qryFileCount.AchFileId_pk) 
AND (Main.EffectiveDate >= qryFileCount.EffectiveDate))

Result:

AchID_pk,   EffectiveDate,   FileCount
3385        4/1/2016           1
3394        4/7/2016           2
23          4/14/2016          1
36          4/18/2016          2
43          4/21/2016          3
61          4/28/2016          4

This is because it is looking for both a lower PK and a lower date.

Join:

ON (Main.CustId_pk = qryFileCount.CustId_fk) 
AND (Main.YearMonth = qryFileCount.EffectiveDateFormat) 
AND ((Main.AchFileId_pk >= qryFileCount.AchFileId_pk) 
OR (Main.EffectiveDate >= qryFileCount.EffectiveDate))

Result:

AchID_pk,   EffectiveDate,   FileCount
3385        4/1/2016           5
3394        4/7/2016           6
23          4/14/2016          3
36          4/18/2016          4
43          4/21/2016          5
61          4/28/2016          6

Trying Lower PK OR lower date doesn't work either.

I tried making a calculated field converting the EffectiveDate field to a long and then concatenating the PK value to the end

CLng(EffectiveDate) & AchId_pk

. At the time I was using the DCount() function instead of the Count() function and the query took forever to run because of all the function calls. I might try it again to see if it works now. Any other suggestions? Basically I want it to look at the EffectiveDate field and then use the lower PK value if there are multiple records with the same date.

[jforbes]

I mostly understand what you are doing and it looks like you are using a ranking query of sorts to get your records in order.

It's my understanding that you want the rank to reset for each Month and continue throughout the Month, then I don't think you want this line in the Join (It's a little hard to tell without the entire Select statement, so this is a best guess) as it can cause a reset during the Month:

ON (Main.CustId_pk = qryFileCount.CustId_fk) 
AND (Main.YearMonth = qryFileCount.EffectiveDateFormat) 
 AND ((Main.AchFileId_pk >= qryFileCount.AchFileId_pk) 
AND (Main.EffectiveDate >= qryFileCount.EffectiveDate))
 

[Seth Schrock]

You are correct that I'm going a ranking query. However, if I remove the PK field from the join, then I get the same rank when there are two records on the same day, which doesn't work for me either.

[jforbes]

Not to sidetrack you too much, but you might want to try a different approach. If you are really trying to select a record so far into a month, you can use the Top and Order by with a Subquery to attempt to pluck out a single record. I haven't used it much, but it's pretty straight forward SQL.

Basically, create a query that gets selects a recordset upto and including the record you want, then select against that recordset in reverse order and using TOP to grab just the one record. This is a pretty poor example, but it should work as an example only:

SELECT TOP 1 
  Temp.FileName
, Temp.CreateDate 
FROM (
   SELECT TOP 5 
     Files.FileName
   , Files.CreateDate 
   FROM Files 
   ORDER BY Files.FileName, Files.CreateDate
   ) AS Temp
ORDER BY Temp.FileName DESC , Temp.CreateDate DESC 

It should return the fifth item in the list.

[Seth Schrock]

I'm not wanting just the one record. I'm wanting all records with their rank. This is for a billing system and I'm generating the invoice and each record is a line item.

[Seth Schrock]

I ended up adding a field to each subquery to concatenate the long integer value of the EffectiveDate field to the AchFileId_pk field and converted it to a string. There wasn't a big performance hit since I'm doing it in a subquery and not a DCount() function.