Need help Grouping by date using multiple date guidelines

[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!!!!!

 

[KARL DEWEY]

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

 

[cwoelfe]

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...

 

[KARL DEWEY]

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));