Compare 2 tables and create new in Access

[shenay921]

Hi there,

I'm fairly new to Access 2003 and am used to Crystal. I have a table of billed accounts (Acct, DateFrom, DateThru, Rebill, DropDate) -- there are other fields but these are the main ones.

The options for Rebill are null and R2. Null means a first-time billing. This table covers several months. When an account is rebilled the exact records info gets placed into the table but on a different DropDate and it gets assigned an R2 when it is rebilled.

I need to only pull the records for an account from the latest rebill. If there is no rebill then I need the original acct records.

I couldn't figure out how to create a query to do this so I am trying to write code in VB to do this. What I have done is created two tables -- one all rebills (R2) (about 20,000 records) and one all nulls (RNone) (the nulls are about 400,000 records). I tried to compare the fields (Acct, DateFrom, DateThru) and when they matched tell the program to take the record from R2 and put into a new table RCombined. If there is no match then take the RNone record.

What I am finding is that only the RNone records are going into the new table and there is no comparison being completed to R2.

The following is the code so far:

Private Sub Form_Load()

Dim R2 As DAO.Recordset
Dim RNone As DAO.Recordset
Dim RCombined As DAO.Recordset
Dim db As DAO.Database
Dim X As Long

Set db = CurrentDb()
Set R2 = db.OpenRecordset("0106 111 and 131 R2")
Set RNone = db.OpenRecordset("0106 111 and 131 NO R2")
Set RCombined = db.OpenRecordset("0106 Blank Table 111 131")

X = 0

With RCombined

Do Until X = RNone.RecordCount

If RNone.Fields("Account").Value = R2.Fields("Account").Value And RNone.Fields("DateFrom").Value = R2.Fields("DateFrom").Value And RNone.Fields("DateThrough").Value = R2.Fields("DateThrough").Value Then
RCombined.AddNew
RCombined.Fields("Account").Value = R2.Fields("Account").Value
RCombined.Fields("DateFrom").Value = R2.Fields("DateFrom").Value
RCombined.Fields("DateThrough").Value = R2.Fields("DateThrough").Value
RCombined.Fields("Type_Bill").Value = R2.Fields("Type_Bill").Value
RCombined.Fields("DRG").Value = R2.Fields("DRG").Value
RCombined.Fields("Sex").Value = R2.Fields("Sex").Value
RCombined.Fields("F/C").Value = R2.Fields("F/C").Value
RCombined.Fields("P/T").Value = R2.Fields("P/T").Value
RCombined.Fields("Payer").Value = R2.Fields("Payer").Value
RCombined.Fields("InsNum").Value = R2.Fields("InsNum").Value
RCombined.Fields("Diagnosis").Value = R2.Fields("Diagnosis").Value
RCombined.Fields("Procdate1").Value = R2.Fields("Procdate1").Value
RCombined.Fields("ProcDate2").Value = R2.Fields("ProcDate2").Value
RCombined.Fields("ProcDate3").Value = R2.Fields("ProcDate3").Value
RCombined.Fields("Procedure1").Value = R2.Fields("Procedure1").Value
RCombined.Fields("Procedure2").Value = R2.Fields("Procedure2").Value
RCombined.Fields("Procedure3").Value = R2.Fields("Procedure3").Value
RCombined.Fields("DropDate").Value = R2.Fields("DropDate").Value
RCombined.Fields("Rebill").Value = R2.Fields("Rebill").Value
RCombined.Fields("Cycle").Value = R2.Fields("Cycle").Value
RCombined.Fields("RevCode").Value = R2.Fields("RevCode").Value
RCombined.Fields("ExtPriceOrig").Value = R2.Fields("ExtPriceOrig").Value
RCombined.Fields("ExtPrice").Value = R2.Fields("ExtPrice").Value
RCombined.Fields("hcpcs").Value = R2.Fields("hcpcs").Value
RCombined.Fields("Modifier").Value = R2.Fields("Modifier").Value
RCombined.Fields("ServiceDate").Value = R2.Fields("ServiceDate").Value
RCombined.Update

Else
RCombined.AddNew
RCombined.Fields("Account").Value = RNone.Fields("Account").Value
RCombined.Fields("DateFrom").Value = RNone.Fields("DateFrom").Value
RCombined.Fields("DateThrough").Value = RNone.Fields("DateThrough").Value
RCombined.Fields("Type_Bill").Value = RNone.Fields("Type_Bill").Value
RCombined.Fields("DRG").Value = RNone.Fields("DRG").Value
RCombined.Fields("Sex").Value = RNone.Fields("Sex").Value
RCombined.Fields("F/C").Value = RNone.Fields("F/C").Value
RCombined.Fields("P/T").Value = RNone.Fields("P/T").Value
RCombined.Fields("Payer").Value = RNone.Fields("Payer").Value
RCombined.Fields("InsNum").Value = RNone.Fields("InsNum").Value
RCombined.Fields("Diagnosis").Value = RNone.Fields("Diagnosis").Value
RCombined.Fields("Procdate1").Value = RNone.Fields("Procdate1").Value
RCombined.Fields("ProcDate2").Value = RNone.Fields("ProcDate2").Value
RCombined.Fields("ProcDate3").Value = RNone.Fields("ProcDate3").Value
RCombined.Fields("Procedure1").Value = RNone.Fields("Procedure1").Value
RCombined.Fields("Procedure2").Value = RNone.Fields("Procedure2").Value
RCombined.Fields("Procedure3").Value = RNone.Fields("Procedure3").Value
RCombined.Fields("DropDate").Value = RNone.Fields("DropDate").Value
RCombined.Fields("Rebill").Value = RNone.Fields("Rebill").Value
RCombined.Fields("Cycle").Value = RNone.Fields("Cycle").Value
RCombined.Fields("RevCode").Value = RNone.Fields("RevCode").Value
RCombined.Fields("ExtPriceOrig").Value = RNone.Fields("ExtPriceOrig").Value
RCombined.Fields("ExtPrice").Value = RNone.Fields("ExtPrice").Value
RCombined.Fields("hcpcs").Value = RNone.Fields("hcpcs").Value
RCombined.Fields("Modifier").Value = RNone.Fields("Modifier").Value
RCombined.Fields("ServiceDate").Value = RNone.Fields("ServiceDate").Value
RCombined.Update
End If

X = X + 1
RNone.MoveNext

Loop

End With

End Sub

Thanks for any ideas or insights!

-- TDA

 

[PHV]

You wanted a query like this ?
SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT Account, DateFrom, DateThrough, Max(DropDate) AS LastDate
FROM yourTable GROUP BY Account, DateFrom, DateThrough
) AS L ON A.Account=L.Account AND A.DateFrom=L.DateFrom AND A.DateThrough=L.DateThrough AND A.DropDate=L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shenay921]

Thanks for your response. I tried to load the code but it does not like the AS A INNER JOIN.