Display <> "Yes" records
experts,
I have 2 tables: tblDraws_Details1 and tblRepayment.
Joined on tblDraws_Details1.ID and tblRepayment.DrawIDrpmt (one to many)
Each record in tblRepayment is a repayment
after a repayment I choose "Yes" in tblRepayment.PaymentMadeYN
What I need to do is display records for:
tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
WHERE:
where ALL the records <>"Yes"
If a record in tblRepayment has "Yes" and "No" then display those but if all the records display "Yes" then remove that tblDraws_Details1.ID
I hope it makes sense.
it might be a lot easier than I am explaining.
below is the sql:
I have 2 tables: tblDraws_Details1 and tblRepayment.
Joined on tblDraws_Details1.ID and tblRepayment.DrawIDrpmt (one to many)
Each record in tblRepayment is a repayment
after a repayment I choose "Yes" in tblRepayment.PaymentMadeYN
What I need to do is display records for:
tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
WHERE:
where ALL the records <>"Yes"
If a record in tblRepayment has "Yes" and "No" then display those but if all the records display "Yes" then remove that tblDraws_Details1.ID
I hope it makes sense.
it might be a lot easier than I am explaining.
below is the sql:
SELECT tblRepayment.DrawIDrpmt, tblDraws_Details1.ID, tblRepayment.PaymentMadeYN
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt;
Ryan Chong
it would be easier with sample data for illustration
ASKER
Hi Ryan,
Please see attached sample db.
I paired it down.
Please refer to the query.
thank you.
EE_Yes.accdb
Please see attached sample db.
I paired it down.
Please refer to the query.
thank you.
EE_Yes.accdb
so you got a result of :
what's your expected result?
DrawIDrpmt ID Pmt Made?
12 12 Yes
13 13 Yes
12 12 Yes
13 13 Yes
16 16 Yes
17 17 Yes
18 18 Yes
19 19 Yes
17 17 Yes
17 17 Yes
76 76 Yes
77 77 Yes
79 79 Yes
79 79 Yes
79 79 Yes
79 79 Yes
80 80 Yes
80 80 Yes
77 77 Yes
84 84 Yes
86 86 Yes
84 84 Yes
87 87 Yes
85 85 Yes
85 85 No
104 104 No
114 114 No
115 115 Yes
91 91 Yes
116 116 No
117 117 No
117 117 No
104 104 No
85 85 Yeswhat's your expected result?
ASKER
I would want to display as follows:
DrawIDrpmt ID Pmt made
85 85 Yes
85 85 No
85 85 Yes
104 104 No
104 104 No
114 114 No
116 116 No
117 117 No
117 117 No
show only records where <> "Yes" or saying it another way show records where ="Yes and No" or just No
ON
tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
DrawIDrpmt ID Pmt made
85 85 Yes
85 85 No
85 85 Yes
104 104 No
104 104 No
114 114 No
116 116 No
117 117 No
117 117 No
show only records where <> "Yes" or saying it another way show records where ="Yes and No" or just No
ON
tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow that's amazing. It works. You are good.
What is that called? A nested query?
What is that called? A nested query?
ASKER
HI Ryan,
I need to add some fields from tblRepayment and tblDRawsDetails_1 and create a report.
For example, when I try to drag down the field tblRepayment.Valuedate, the report gives me an error of "the specified field could refer to more than one table in the FROM clause." FYI: I dont have an issue when I try to execute just within the query itself...its only in the report. I have tried to modify but I don't follow the query...its a little complicated for me.
greatly appreciate your expert assistance. Thank you Ryan.
I need to add some fields from tblRepayment and tblDRawsDetails_1 and create a report.
For example, when I try to drag down the field tblRepayment.Valuedate, the report gives me an error of "the specified field could refer to more than one table in the FROM clause." FYI: I dont have an issue when I try to execute just within the query itself...its only in the report. I have tried to modify but I don't follow the query...its a little complicated for me.
greatly appreciate your expert assistance. Thank you Ryan.
What is that called? A nested query?You can called that sub-query
maybe you can create a new question and we will continue from there for your another issue? seems it's not really linked to your original question here.
ASKER
ok will do
ASKER
Ryan:
here is the link to the new question:
https://www.experts-exchange.com/questions/29004814/Modify-report.html
here is the link to the new question:
https://www.experts-exchange.com/questions/29004814/Modify-report.html