Prev: Open filtered report
Next: Find and Replace in Access 2007
From: rebeca on 23 Mar 2010 16:53
On Feb 19, 4:38 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> rebeca wrote:
> >Thanks for sticking with me. I'm a fairly new user to Access, but I
> >pick things up quickly. It sounds like the DLOOKUP may work, I just am
> >not very familar with that command.
>
> >Here is the SQL from the Query.
>
> I'm modifying the query just to make it easier to read.  It
> produce exactly the same results.
>
> SELECT ARL.MDID, ARL.[Batch Date], ARL.Physician,
>                 Sum(ARL.Charges) AS SumOfCharges,
>                 Sum{ARL.Chg_Adj) AS SumOfChg_Adj,
>                 Sum(ARL.Payments) AS SumOfPayments,
>                 Sum(ARL.Pymt_Adj) AS SumOfPymt_Adj,
>                 Sum(ARL.Other_Adj) AS SumOfOther_Adj,
>                 Sum(ARL.Refunds) AS SumOfRefunds,
>                 Sum(ARL.Bad_Debt) AS SumOfBad_Debt,
>                 Sum(ARL.Total1) AS SumOfTotal1,
>                 BH.ARDate, BH.BeginningAR
> FROM [SN AR Log Query - By Physician] As ARL
>         INNER JOIN SNBalanceHistory As BH
>                         ON ARL.MDID = BH.Physician
> GROUP BY ARL.MDID, ARL.[Batch Date], ARL.Physician,
>                                 BH.ARDate, BH.BeginningAR
>
>
>
>
>
>
>
> >Table Names are:
> >SNBalanceHistory
> >     MDID - Unique record
> >     Physician - Physician (joined to the SNProvider Table by MDID in
> >that table)
> >     ARDate - Beginning AR date, multiple entries for each provider as
> >a new entry is made each month after adjustments, FC, etc.)
> >     BeginningAR
>
> >AR Log - Scinet is the main table the data comes from.
> >   Batch ID
> >   Physician
> >   Date Entered in Log
> >   Batch Date
> >   Batch
> >   Charges
> >   Chg_Adj
> >   Payments
> >   Pymt_Adj
> >   Refunds
> >   Other_Adj
> >   Kind_of_Adj
> >   Bad_Debt
> >   Reason for Modification
> >   WindowsLogon
> >   System
>
> I appears to me that the beginning ar amount you are trying
> to calculate would be a sum from the SNBalanceHistory table
> ( I don't see where the AR Log - Scinet table would be
> needed???
>
> The Domain Aggregate functions (e.g. DLookup, DCount, DSum,
> etc) are all easily translated from basic queries that
> return a single value.  In your case I think(?) you may want
> to use DSum something like:
>
>         If Me.[report start date] > Me.ARDATE Then
>                 startamt = 0
>         ElseIf Me.[report start date] = Me.ARDATE Then
>                 startamt = beginningAR
>         Else            'Me.[report start date] < Me.ARDATE
>                 startamt = beginningAR + _
>                                 DSum("BeginningAR", "SNBalanceHistory", _
>                                         "ARDate>=" & Format(Me.ARDATE, "\#yyyy-m-d\#") _
>                                         & " And ARDate<" & Format(Me.[report start date],
> "\#yyyy-m-d\#") _
>                                         & " AND Physician=" & Me.Physician)
>         End If
>
> Most likely, it's not that simple, but it's the best I can
> figure.  So study that to understand what it does and how it
> does it, then maybe you can correct it.  Be sure to check
> VBA Help for details about the Domain Aggegate functions.
>
> --
> Marsh
> MVP [MS Access]- Hide quoted text -
>
> - Show quoted text -

Just letting you know that I did get this completed with some
invisible text boxes, dlookup and dsum. Works beautifully now.
Thanks again for your help!
 | 
Pages: 1
Prev: Open filtered report
Next: Find and Replace in Access 2007