Overdue Query help needed

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

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
 
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
Click to expand...

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)
 
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
Click to expand...

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
 
Bob Vance said:
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
Click to expand...

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
Click to expand...
Actually I suppose I just need a query if a OwnerID owes $600.00
and this months Invoice is $200.00 and the month before was $300 and the
month before that was $100.00....The query would show me $200.00 1 month
over $100.00 2 Months over
What do you think :\ , thats at the first of the month...Thanks Bob
 
Back
Top