only report grand totals from multiple queries - #Name? error Access 2013

[RCBricker]

Hi all. I have a DB that I am creating for someone. I have all the tables set and all queries and reports are working great...except one.

They want a report that is basically a YTD of costs for medical expenses and prescriptions both by patient. I can do what they want in excel...I am having difficulties figuring out how to do it in access.

the report view would have

column A
patient name for meds category

column B would be the (grand total of all records) amount billed total
column C would be the (grand total of all records) amount approved by the insurance company
column D would be the (grand total of all records) amount of member co-pay

this format would repeat for each patient for the meds category

this format would then overall repeat for the Medical claims category.

there are queries for each of these categories that show all records for the YTD I intended to create the report and then point to various queries and use an expression within the control source of the text box to present the data. However, I am getting a #name? error.

Any ideas how I can create this report?

 

[Micron]

If you're going to use multiple queries, then a report/sub report design will be required. This may introduce problems if you need overall totals including values from the subs. an

...expression within the control source of the text box to present the data

Calculated controls can only go in report footers (group/page etc.) so that may be the issue for the Name error (guessing since I can't see where you put them).

Sounds to me that an overall query that gets all the data should work if you group by type then patient id (in report design). The records would probably look like this (second set of numbers omitted - too lazy)

Meds	Billed	Approved	Co Pay
patient 1	10	5	25
patient 2	20	10	30
patient 3	30	15	35
Claims
patient 1
patient 2
patient 3

<tbody>
</tbody>

However, you don't say if you want individual records or just grand totals, so is it a totals query or select query that is the report data source? If the former, your calculated controls would go in the patient group to total by patient, and/or the type group to total by Meds or whatever. If the latter, calculated controls won't be required if the query supplies the data. Having only calculations doesn't really help when you start seeing wonky numbers and you have no clue in the report whether or not they're reasonable.

 

[RCBricker]

Thanks as always Micron.

I will apply your advice to my project and come back if I screw your solution up.

the example of how the data will look is spot on.

I pretty well suck at Access as my experience lies in excel. But this is a favor for a friend so I am trying to work all this out. I tried an overall query, but maybe I need to look at queries involving multiple tables more. When I tried to gather the meds and claims together it created a query of records for each of the claims (first table used in query) and then created multiple lines of each of the meds (by far more claims than meds so it just filled in the records with copies of the meds). I will go back and see if I can get it figured out. So much easier to do this kinda crap in excel.

As for the wonky numbers, The data for claims and medications is provided in a different report. This is a tracker for YTD costs at a glance. It will only really be used per quarter or end of year for reporting costs purposes. The other detailed reports will be used for normal monthly reporting.

 

[Micron]

Too many records? Try DISTINCT and DISTINCT ROW predicates.

 

[RCBricker]

Too many records? Try DISTINCT and DISTINCT ROW predicates.

ok I will look that up see how that works.

BTW any idea why after running my database that it needs to be re-set before it will allow me to run it with out bugging out?

Different database that uses excel it bugs when creating the first sheet on the re-run.

I close the first run by clearing excel with the following:

    With appEXCEL
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
On Error Resume Next
    Set dbF = Nothing 'my database variable
    wbRECON.Close False 'my excel workbook variable
    Set wbRECON = Nothing
    appEXCEL.Quit  'my excel application variable
    Set appEXCEL = Nothing
On Error GoTo 0

the error I get is Run-time error 91. Object variable or with block variable not set.

However, once I hit the reset button in the VBE the code runs fine.
as always I appreciate your advice

 

[Micron]

I can't recall ever getting this error by not correctly creating a With block. It has always resulted from not declaring an object variable. Based on your reset comment, I'd suspect you have a global or module level variable that you think is still instantiated but is not, and resetting causes everything to start from scratch. Based on your limited code sample, I'd guess that dbF, wbRECON or appEXCEL does not exist on the second attempt after you've set them to Nothing. You'd have to post the code for the routine for me to even have a chance to pinpoint it. However, this sounds like it should be a different thread.