Confused to Why Query is Tripling for One ID

[melost]

Stumped. I have a query using two tables using the following query for a report. I need the tbl_dental_claim.amount_paid. However, when there's more than one duplicate ID in the tbl_claim_line, it's returning the rows which is fine, but it's also multiplying the amount for figure in tbl_dental_claim.amount_paid. How can I avoid this? I do need all the rows, but don't want figures multiplying.

The PK on tbl_dental_claim is ID and PK on tbl_claim_line is line_number (auto).

SELECT DISTINCT tbl_dental_claim.*, tbl_dental_claim.claim_date, tbl_claim_line.*

FROM tbl_dental_claim INNER JOIN tbl_claim_line ON tbl_dental_claim.ID = tbl_claim_line.ID

WHERE (((tbl_dental_claim.claim_date) Between [Forms]![frm_Run_Reports]![txtStartingDate] And [Forms]![frm_Run_Reports]![txtEndingDate]) AND ((tbl_dental_claim.amount_paid)=0) AND ((tbl_dental_claim.form_status)<>4)) OR (((tbl_dental_claim.form_status)<>6));

 

[rac2]

When you say the figures are multiplying, I think you may mean that the amount_paid is being repeated in each row?

That is the way SQL JOINs work when one table has many rows and the other table has a single row.

Is there a column in tbl_claim_line such as line_number? If so you could display the amount_paid for line 1 and 0 for the other lines. Do this by defining an expression with an IIf() function; if this is line 1 show the amount paid, otherwise show 0.

SELECT 
            IIf( tbl_claim_line.line_number = 1,
                 tbl_dental_claim.amount_paid,
                 0 ) AS "Amount Paid"

FROM tbl_dental_claim
INNER JOIN tbl_claim_line ON tbl_dental_claim.ID = tbl_claim_line.ID

WHERE (
             (
               (tbl_dental_claim.claim_date) Between
                      [Forms]![frm_Run_Reports]![txtStartingDate]
                         And
                      [Forms]![frm_Run_Reports]![txtEndingDate]
             )
             AND
             ( (tbl_dental_claim.amount_paid) = 0 )
             AND
             ( (tbl_dental_claim.form_status) <> 4 )
    )
     OR
    (
            ( (tbl_dental_claim.form_status) <> 6 )
     );

 

[Golom]

Just a couple of observations ...

SELECT DISTINCT tbl_dental_claim.*, tbl_dental_claim.claim_date

the tbl_dental_claim.* will return all fields in the table. Why are you returning the specific field claim_date a second time?

AND ((tbl_dental_claim.form_status)<>4)) OR
   (((tbl_dental_claim.form_status)<>6));

Looks suspicious. form_status will always be "not equal" to at least one of those values no matter what value it has.

Do you mean multiplying as in "the product of some values" or are you using "multiplying" to mean "duplicating"?

 

[melost]

Thanks for the prompt responses. Yes when tbl_claim_line returns matched rows found for ID it's duplicating the amount_paid figure (which I want to avoid.) The amount_paid field only exist in the tbl_dental_claim. Would using an expression what I'm looking for or some sort of nested query?

melost

 

[rac2]

As I suggested, an expression could take you toward what you wish. Nested queries? I dont know, never used them, but within the context of SQL, I think not. Other approaches might be possible with grouping and break levels used together with the report layout application. I cant help you there. For that you may want to start a new thread in the Microsoft Access Reports Forum.

To nail down the point about SQL and what you can do with it. A SELECT query returns a set of rows. The rows have values in columns. Each row has a value in every column (sometimes the value is NULL). The recordset always looks like this

ColA ColB ColC
a 1 z
b 1 w
c 2 m

It never looks like this

ColA ColB ColC
a 1 z
b w
c 2 m

You can sometimes fudge it by using a expression to display a space instead of a 1 as in the second table above. This depends on whether you can think of a rule to display the 1 in one row and not in the others. As I suggested, if the order items table has a column of line numbers you could do that.

Hope this helps.

 

[melost]

Thanks for your help. Got the query to return the right amounts, now I gotta figure out why the 0 fields dup with values from the other rows with vlaues. Geez. Nothing's that easy. Very strange. I posted something in reports.

thx again,
melost