I have 3 Tables
tblOwnerInfo = [OwnerID]
tblAccountStatus = [OwnerID]-[PaidAmount]-[BillDate]
tblInvoice = [OwnerID] -[OwnerPercentAmount] - [InvoiceDate]
What I am trying to do is get a query that will add each [OwnerID] with
[OwnerPercentAmount] -Minus 1 month and [PaidAmount] and that would give
me
a 30 day Overdue at the start of each month
Is it just me, or does this not make any sense?
The nouns "Owner" and "Invoice" make sense. What is "Account
Status"? And what are the "Paid Amount"? Looks like it should be in
a payment table or similar. Could you explain what you are trying to
do in *English* and not database speak?
If you're trying to figure out when items are overdue, you would have
something like
tblInvoice(CustomerID, InvoiceNo, DueDate, AmountDue) and then maybe
Payment(PaymentID (PK), CustomerID (FK), AmountPaid) and then you
could have junction table between Payment and Invoice.
InvoicePaymentDetails(PaymentID, InvoiceID, AmountPaidOnInvoice)
Sorry [tblAccountStatus] is payments made against [tblInvoice] they have
[OwnerID] as there customer number/code
I dont need any Due dates as i have a running total systerm, Paid Amount
is the Amount Paid!
I actually have these fields in my tables I was trying to keep it simple:
[tblAccountStatus]
BillID1 ,OwnerID ,BillDate, PaidAmount, ModeOfPayment ,CreditTypes,
PaymentID ,CreditNote
[tblInvoice]InvoiceID InvoiceNo, YearMonth, CompanyID, OwnerID, OwnerName,
OwnerAddress ,HorseID, HorseName, FatherName ,MotherName, DateOfBirth,
HorseDetailInfo, Sex, InvoiceDate, DiscountText, Discount SubTotal
,TotalAmount, OwnerPercentAmount, GSTOptionsText ,GSTOptionsValue,
GSTContentsText GSTContentsValue, OwnerPercent Remark, ClientInvoice, Help
,ChargeNumber
Thanks for the Help............Bob
