UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Querry Design Help, Any Version    
 
   
Kamulegeya
post Aug 7 2019, 04:20 AM
Post#1



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hello Members,

I have three tables.
tblInvoices (InvoiceID, InvoiceNumber,InvoiceDate, SupplierId, etc)
tblInvoiceLines(LineID, InvoiceID, CurrencyID,Amount)
tblInvoicePayments(PaymentID, InvoiceLineID(FK tblInvoiceLines),PaymentDate,Amount).
An invoice can be denominated in different currencies that why i have a seperate table for the invoice lines.

At the time of payment, an invoice can be paid partially e.g one currency portion can be paid in whole or partially .

I created a query that shows unpaid invoices lines by using a left join on tblInvoicePayments.

So when i run the query, i get the current unpaid invoices(including the partial payments).

Now i want to create a query which shows unpaid invoices at a point in time. E.g show unpaid invoices as at 30/06/2019.

I am bit stuck and i am seeking for pointers to the right direction in designing the query.

Kind regards,
Kamulegeya Ronald
Go to the top of the page
 
isladogs
post Aug 7 2019, 05:48 AM
Post#2


UtterAccess VIP
Posts: 1,603
Joined: 4-June 18
From: Somerset, UK


Possibly just modify your existing query to filter for PaymentDate <=#30/06/2019#

--------------------
Go to the top of the page
 
DanielPineault
post Aug 7 2019, 05:49 AM
Post#3


UtterAccess VIP
Posts: 6,838
Joined: 30-June 11



I start using the Date as a filter to restrict the available records to then apply your existing query against.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RJD
post Aug 7 2019, 03:40 PM
Post#4


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Hi: Hmmm ... actually, what I would do is create two new queries of the tblInvoices & tblInvoicePayments tables, each with a date criteria (make the prompt or form date reference the same name), then duplicate the existing query and replace the table names with the new queries.

This is untested, but might be worth a try ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Kamulegeya
post Aug 8 2019, 10:36 AM
Post#5



Posts: 1,857
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


The suggestion of using two date parameters(Invoice date and Payment date).

Thanks so much for the suggestions.

Ronald
Go to the top of the page
 
RJD
post Aug 8 2019, 11:21 AM
Post#6


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Hi Ronald: Is your query working now as required? Can we be of further assistance?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 03:52 AM