qry for forward balances

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to show a Previous Balance for a particular Vendor in a new
record?
I have a form that's based on a query which is based on 2 tables. tblVendor
and tblWeekDetails.
Each week a user enters payments for a Vendor. I want to show any balance
due last week from that particular Vendor, on this week's record when the
user opens the form and pulls that Vendor name from the combobox. I've been
reading posts for 2 weeks and am at a loss. Here is sql. Any help in the
right direction would be appreciated. Hope I sent all pertinent info as I'm
a somewhat new Access user.

SELECT tblWeekDetails.DetailsID, tblVendor.Vendor,
tblWeekDetails.WeekEnding, tblWeekDetails.Agents, tblWeekDetails.ComAmt,
tblWeekDetails.ProcFee, tblWeekDetails.VPayFee, tblWeekDetails.OtherFee,
tblWeekDetails.PayAmt, tblWeekDetails.PayDate, tblWeekDetails.PayType,
tblWeekDetails.Processed, tblWeekDetails.Pulled, tblWeekDetails.Comments,
tblWeekDetails.MiscDed, tblWeekDetails.MiscAddi, [TotalDue]-[PayAmt] AS
Balance, [Balance]-[MiscDed]+[MiscAddi] AS FinalTotal,
tblWeekDetails.PreviousBalance, tblWeekDetails.Extended,
tblWeekDetails.Todays, [ComAmt]+[ProcFee]+[VPayFee]+[OtherFee] AS SubTotal,
[ComAmt]+[ProcFee]+[VPayFee]+[OtherFee] AS TotalDue
FROM tblWeekDetails LEFT JOIN tblVendor ON
tblWeekDetails.VendorID=tblVendor.VendorID
WHERE (((tblWeekDetails.PreviousBalance)=[FinalTotal]));
 
opps - here is correct sql
SELECT tblVendor.Vendor, tblWeekDetails.WeekEnding,
tblWeekDetails.DetailsID, tblWeekDetails.VendorID AS tblWeekDetails_VendorID,
tblWeekDetails.Agents, tblWeekDetails.ComAmt, tblWeekDetails.ProcFee,
tblWeekDetails.VPayFee, tblWeekDetails.OtherFee, tblWeekDetails.ToDue,
tblWeekDetails.PayAmt, tblWeekDetails.PayDate, tblWeekDetails.PayType,
tblWeekDetails.Processed, tblWeekDetails.Pulled, tblWeekDetails.Comments,
[ComAmt]+[ProcFee]+[VPayFee]+[OtherFee] AS TotalDue, [TotalDue]-[PayAmt] AS
Balance, tblWeekDetails.MiscDed, tblWeekDetails.MiscAddi,
[Balance]-[MiscDed]+[MiscAddi] AS FinalTot
FROM tblVendor RIGHT JOIN tblWeekDetails ON tblVendor.Vendor =
tblWeekDetails.VendorID;
 
Back
Top