Difficult Query Problem

[John Daily]

Running Access 2k

I have 3 table I need to pull data from. They are all linked by [acct_num], it is primary in 2 tables but not the third because the third table has multiple [trans_code]s for each [acct_num]. I have a patient info table, a payment info table, and a denial codes table, again all are connected by the acct number.
When I do a normal query with all 3 tables linked I get multiple acct numbers, multiple payment information ([total_charges] and [acct_bal]), but will get unique denial code information ([trans_code] and [trans_date]). Here is an example:

acct_num trans_date trans_code total_charges acct_bal
000000005 10/1/03 T5000 $1,500.00 $1,500.00
000000005 10/1/03 T5005 $1,500.00 $1,500.00
000000005 10/15/03 T5010 $1,500.00 $ 450.00

The total_charges never really change. The person can have the same acct # w/ multiple trans codes on multiple dates. The total charges stay the same. The acct bal usually changes though.
The payment information table is unique that the acct num is primary key, the same with the patient information table. The denial code table is the one I am having problems with because it is pulling up the same charges for the same acct num withn all of the trans dates and trans codes.
What I need is just 1 acct_num, 1 total_charges, multiple trans_code, multiple trans_date for my reports
Example:

0000000005 $1,500.00 $450.00 10/1/03 T5000
10/1/03 T5005
10/15/03 T5010

I can do this on my forms but just can't generate the data for a summary report and detail report.

Any suggestions?

Thank you in advance!

John

 

[Jeff Boyce]

John

When you query, you get all the fields. When you build a report, you can
tell Access to not display a repeating field (?your Acct#).

Good luck

Jeff Boyce
<Access MVP>

 

[John Daily]

I am using the reports as summary reports so the totals would be skewed because they would count the total_charges multiple times. Is there syntax in SQL that I can do a maybe a Union Query so that I get only one total_charge for one acct_num
What about doing sub-reports and have the main report unbound so that I can pull the info from the sub-reports into the main report? Would something like that work

TIA

John

 

[Gary Walter]

I am using the reports as summary reports so the totals would be skewed because

they would count the total_charges multiple times. Is there syntax in SQL that I can
do a maybe a Union Query so that I get only one total_charge for one acct_num?

What about doing sub-reports and have the main report unbound so that I can pull

the info from the sub-reports into the main report? Would something like that work?Hi John,

In keeping with what Jeff's reply,
I might construct a query that returns

0000000005 $1,500.00 $450.00 10/1/03 T5000
0000000005 $1,500.00 $450.00 10/1/03 T5005
0000000005 $1,500.00 $450.00 10/15/03 T5010

This would be your Detail section setting hide duplicates.

Then set up a "0-height" Group Header on acct_num.

Copy and paste textboxes for acct_num, "$1500", and "$450"
from Detail into the group header.

I believe this will give you *one instance*
of these values for each acct_num.

Give them meaningful names like
txtSummingCharges
txtSummingBalances

Set these to running sum.

Then make 0 height (maybe even not visible)
as well as your group header.

In the summary, reference the 2 summing textboxes.

Please respond back if I have misunderstood.

Good luck,

Gary Walter