Link to home
Start Free TrialLog in
Avatar of Altaf Patni
Altaf PatniFlag for India

asked on

Query for Unmatch amount

Hi,
I am trying to get InvoiceNo from Table(InvoDtl)  But with condition.
and condition is if Amount from Table(MatchInvo) is not equal to InvoDtl.Amount.

i have three table
1 - BILLDTL
2 - RECEIPT
3 - MATCHBILL


Fields from BILLDTL
*BillNo - BillDate -  CompName - Amount
10001 - 01-01-12 - EPSON TECH - 10,000.00
10002 - 01-02-12 - EPSON TECH -   5,000.00
10003 - 05-03-12 - EPSON TECH -   3,000.00

Fields from RECEIPT
*RcptNo - RcptDate - CompName  - Amount
101       - 01-03-12 - EPSON TECH - 6,000.00
102       - 05-04-12 - EPSON TECH - 6,000.00

Fields from MATCHBILL
*UniqueNo - BillNo - BillAmount - RcvdAmount - RcptNo - CompName - BalAmt
221             - 10001 -  10,000.00   -   4000.00        - 101      - EPSON TECH - 6,000.00
222             - 10002 -    5,000.00   -   2000.00        - 101      - EPSON TECH - 3000.00
223             - 10001 -  10,000.00   -   6000.00        - 102      - EPSON TECH - 0000.00

This is the situation.
Now what i want is
i want those bill no. which are not matched with amount
From above example desired Result will be
10002
10003

because bill no. 10001 amount 10,000.00 is paid . UniqueNo 221 and 223

so which query will be....?
Avatar of Mike McCracken
Mike McCracken

So you want to see all bills (BILLDTL) that haven't been fully paid.

mlmcc
Avatar of Altaf Patni

ASKER

correct all billNo that havent been fully paid.
i tried this
i am not sure it is right qury or not..
Please note that..there is not even single record in MatchBill right now.
Before insert detail i want BillDtl.BillNo. which are not paid or not fully paid.

Run Time Error
The Multi-Part Identifier "MatchBill.CompName" Could Not Be BOUND.
RsBilNoList.Open "SELECT BillDtl.BillNo From BillDtl, MatchBill WHERE NOT (BillDtl.TtlAmt) IN (SELECT Sum(MatchBill.RcvdAmt) FROM MatchBill) AND BillDtl.CompName = '" & Text2(0).Text & "' and BillDtl.CompName = MatchBill.CompName ", Con, adOpenKeyset, adLockOptimistic

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I tried following query as you suggested..
But I am not getting any BillNo. ( In MatchBill Table There is no any Records for Particular CompName ) In this case it supposed to be give me BillNo from BillDtl table related to Particular CompName

SELECT BillDtl.BillNo From BillDtl WHERE Not(BillDtl.BillNo in (SELECT MatchBill.BillNo FROM MatchBill WHERE MatchBill.BalAmt = 0)) AND BillDtl.CompName= '" & Text2(0).Text & "'", Con_Main, adOpenKeyset, adLockOptimistic
If you eliminate the company name filter do you get records?

mlmcc
yes i am getting bill no without companyname
Are the company names perhaps padded left or right with blanks?

mlmcc
so where to put company name in query.?
You have it in the correct place.  I suspect the database field may have leading or trailing blanks or the Capitalization may matter

SELECT BillDtl.BillNo From BillDtl WHERE Not(BillDtl.BillNo in (SELECT MatchBill.BillNo FROM MatchBill WHERE MatchBill.BalAmt = 0)) AND Trim(BillDtl.CompName) = '" & Trim(Text2(0).Text) & "'", Con_Main, adOpenKeyset, adLockOptimistic

mlmcc