value on a specific date

[robnsd]

Where I work cashiers are required to pay their shortages. I record
the shortages in a table with the shortage amount, shortage date,
payment amount and payment date. I'm talking hundreds of cashiers and
thousands of shortages overall.

I've developed a report that shows the total outstanding amount owed
by all cahsiers when the report is run. However, I need a report that
shows how much was owed on the date selected by the user. For example,
on 9/30/10 how much was owed? The problem is this report may have to
be run a week later,say 10/7/10 after additional payments were made.

I can make a report that only shows shortages that occured on or
before 9/30/10, but how do I also show only those that were not paid
on or before 9/30/10 (or whatever date the user chooses.) I'm thinking
this involves two queries.

 

[robnsd]

In other words, I need to ignore payments made after a particular
date, but not ignore the shortage if it occurred on or before the
particular date.

 

[John Spencer]

Probably something like the following query. Watch out for line wrapping.

SELECT Cashier
, Sum(Shortage Amount]) as TotalShortages
, Sum(IIF([payment Date]>#2010-09-30# Or [Payment Date] is Null, [Shortage
Amount],0)) as Unpaid
FROM Shortages
WHERE [Shortage Date] <= #2010-09-30#
GROUP BY Cashier

Assumption is that if payment date is null (no payment has been made) or if
payment date is after the cutoff you don't want to count the payment.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[a a r o n . k e m p f]

well using Access for something like this is just plain silly.

can you even secure this data if you choose Access?

move to SQL Server, and find someone that knows how to write these
queries for you.

Sorry dude.. but Access and cash control don't mix

-Aaron