query pulling records multiple times, but not all (1 Viewer)

cfp76

Registered User.
Local time
Today, 12:09
Joined
Aug 2, 2001
Messages
42
I am trying to create a query that is using 3 different tables.

Table 1 - account information
Table 2 - insurance detail tying to #1 via carrier code
Table 3 - aging buckets for accounts, tying back by carrier code. This table is initially full of empty fields that are populated during the query.

one-one-one ratio

I have a simple iif statement in the query that says this:

0-30: iif(reportdate-dischgdt>=0 AND reportdate-dischgdt<31,totacctbal,0)

I have it set up as a summation but for some reason I am getting more money populating this field than should be. Overall, I have 8 million more in the fields than should be.

I tied my account information table back to the extract I pulled in and I have the exact number of accounts and the exact number of dollars for each patient type.

What is going on? Any takers?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:09
Joined
Feb 19, 2002
Messages
42,970
The problem is that the amounts you are adding are coming from the "one" side table so they actually occur multiple times in the query. Remember, each "one" side record is matched with each "many" side record, so, if you have 3 details for an account the amount will be 3 times what it should be.

You cannot calculate totals based on the "one" side in a one-to-many query. You'll need to use a report so you can use a subreport for the "many" side records and therefore eliminate them from the main report query.
 

cfp76

Registered User.
Local time
Today, 12:09
Joined
Aug 2, 2001
Messages
42
I do not have duplicate information in any of my tables to be added multiple times. I think now my problem is that I'm trying to subtract dates instead of using the DateDiff() function. I'm fiddling with it now. Any help on this is greatly appreciated.

Crystal
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:09
Joined
Feb 19, 2002
Messages
42,970
I didn't say that you had duplicate data in the table. I said that the query was producing the duplicates. Take these two tables:

tbl1
Acct1 5.00
Acct2 20.00

tbl2
Acct1 Carrier1
Acct1 Carrier2
Acct2 Carrier1
Acct2 Carrier2
Acct2 Carrier3

When you join them the result is:
Acct1 5.00 Carrier1
Acct1 5.00 Carrier2
Acct2 20.00 Carrier1
Acct2 20.00 Carrier2
Acct2 20.00 Carrier3

If the amounts you are summing come from the "one" side, they are duplicated in the query. So, the total for Acct1 = 10.00 rather than 5.00 and Acct2 = 60.00 rather than 20.00
 

Users who are viewing this thread

Top Bottom