Query Problem

F

FJ Questioner

The results of the following query allow the Invoices.GrossDiscFee,
Invoices.RebatePayable and Invoices.ConsFee fields to be amended.

SELECT Invoices.INVOICEID, ClosedHow.CLOSEDHOWID, Currencies.Currency,
Invoices.FeeAndRebateAdded, Assignors.CompanyName, Debtors.DebtorName,
[Assignment Requests].ASSIGNORID, Invoices.GrossDiscFee,
Invoices.RebatePayable, Invoices.ConsFee, Invoices.AssignorInvoiceNumber,
Invoices.InvoiceDate, Invoices.InvoiceAmount, [Assignment Requests].CURRENCYID
FROM Currencies INNER JOIN (ClosedHow INNER JOIN (Assignors INNER JOIN
([Assignment Requests] INNER JOIN (Debtors INNER JOIN Invoices ON
Debtors.DEBTORID = Invoices.DEBTORID) ON [Assignment
Requests].ASSIGNMENTREQUESTID = Invoices.ASSIGNMENTREQUESTID) ON
Assignors.ASSIGNORID = [Assignment Requests].ASSIGNORID) ON
ClosedHow.CLOSEDHOWID = Invoices.CLOSEDHOWID) ON Currencies.CURRENCYID =
[Assignment Requests].CURRENCYID
WHERE (((ClosedHow.CLOSEDHOWID)=1) AND ((Invoices.FeeAndRebateAdded)=No) AND
((Invoices.AdvancePaid)=Yes) AND ((Invoices.Closed)=Yes))
ORDER BY Assignors.CompanyName, Invoices.AssignorInvoiceNumber;



HOWEVER, I split the Invoice table into four parts, all linked by the
invoice table key INVOICEID. The three new tables are Advances,
RebatesandDiscFee and ConsFees. Now when I run the revised query below, I
get the right results but the GrossDiscFee, RebatePayable and ConsFee fields
CANNOT be amended (which I need them to be).

SELECT Invoices.INVOICEID, Invoices.AssignorInvoiceNumber,
Invoices.InvoiceDate, Invoices.InvoiceAmount, Invoices.CLOSEDHOWID,
RebatesandDiscFee.FeeAndRebateAdded, Advances.AdvancePaid,
Assignors.CompanyName, Debtors.DebtorName, [Assignment Requests].ASSIGNORID,
RebatesandDiscFee.GrossDiscFee, RebatesandDiscFee.RebatePayable,
ConsFees.ConsPaid, [Assignment Requests].CURRENCYID, Currencies.Currency
FROM (((Currencies INNER JOIN (Assignors INNER JOIN ([Assignment Requests]
INNER JOIN (Debtors RIGHT JOIN Invoices ON Debtors.DEBTORID =
Invoices.DEBTORID) ON [Assignment Requests].ASSIGNMENTREQUESTID =
Invoices.ASSIGNMENTREQUESTID) ON Assignors.ASSIGNORID = [Assignment
Requests].ASSIGNORID) ON Currencies.CURRENCYID = [Assignment
Requests].CURRENCYID) LEFT JOIN Advances ON Invoices.INVOICEID =
Advances.INVOICEID) LEFT JOIN ConsFees ON Invoices.INVOICEID =
ConsFees.INVOICEID) LEFT JOIN RebatesandDiscFee ON Invoices.INVOICEID =
RebatesandDiscFee.INVOICEID
WHERE (((Invoices.CLOSEDHOWID)=1) AND
((RebatesandDiscFee.FeeAndRebateAdded)=No) AND ((Advances.AdvancePaid)=Yes)
AND ((Invoices.Closed)=Yes))
ORDER BY Invoices.AssignorInvoiceNumber, Assignors.CompanyName;


I've tried just about every combination of join types etc.. that I can think
of. I suspect I may need to rework this query into one or more subqueries
but I'm not sure how to best do that.

Any suggestions on how this 2nd query can be arranged such that the
GrossDiscFee, RebatePayable and ConsFee fields CAN be amended in the query
result?

Thanks a lot,

FJ
 
Top