Need help Grouping by date using multiple date guidelines

  • Thread starter Thread starter cwoelfe
  • Start date Start date
C

cwoelfe

Ok i need to create a report that tells me what withdrawals are going to be
refunded at what percentage..

I have a field called REG_AUDIT_DATE this date is the date on which they
withdrew...

I also have a column called TERM_YYT... it is the term & semester of the
class... Each semester has different dates for the refund percentage so I
think I will have to create a different report for each semester...

Anyways, the main thing is that I need to find any withdrawals that fall in
between certain dates and group them...

IE... in term 092 the percentage dates are as follows...

If you withdraw by:

May 12 = 100% refund
May 13 = 90% refund
May 14-15 = 50% refund
May 16-19 = 25% refund


So what would the expression be to find dates in the REG_AUDIT_DATE and
group them according to those dates?

Thanks so much for any help!!!!!
 
You need to setup a refund matrix table but you did not include the class
date in your sample data.
If class date is 1 June (REG_AUDIT_DATE) then the table would look like this
--

Days_Prior Percent_Refund
20 1.00
19 0.9
18 0.5
17 0.5
16 0.25
15 0.25
14 0.25
13 0.25
 
OK... not sure what you're meaning but here are the dates for each semester
that classes start...

092 = 5/11/09
093 = 5/11/09
094 = 6/1/09
095 = 6/29/09
096 = 7/6/09


092 Refund Dates =
May 12 = 100% refund
May 13 = 90% refund
May 14-15 = 50% refund
May 16-19 = 25% refund


what would i do with the refund matrix table once I create it anyways? my
mind is simply fried from working in access for a month straight now, sorry...
 
I assumed that refunds were to be given only before class start date but your
example shows after. So Refund_Matrix is set different.
Class REG_AUDIT_DATE
092 5/11/2009
093 5/11/2009
094 6/1/2009
095 6/29/2009
096 7/6/2009
098 9/15/2009
099 9/20/2009

Use this query --
SELECT YourTable.Class, YourTable.REG_AUDIT_DATE, Refund_Matrix.Days_After,
Refund_Matrix.Percent_Refund
FROM YourTable, Refund_Matrix
WHERE (((YourTable.Class)=[Enter Class number]) AND
((Refund_Matrix.Days_After)=DateAdd("d",-[REG_AUDIT_DATE],Date()))) OR
(((YourTable.REG_AUDIT_DATE)>=Date()) AND ((Refund_Matrix.Days_After)=1 Or
(Refund_Matrix.Days_After) Is Null));
 
Back
Top