Help creating crosstab query

[DubboPete]

Hi all,

I'm probably going about this the wrong way, but I want to know if it
is possible to use a crosstab query to display what I need.

Scenario: People pay into a lotto syndicate each fortnight. The
amount is $5. So in effect, they pay today and they next need to pay
on 11th April 07.

However, some rich people want to pay more than one period at a time,
and obviously some people fall behind in their payments. So we could
have Rich Joe who has now paid until 25th April by paying $10, and
Poor Mary who pays $20 to catch up to today's deadline, and she next
needs to pay on 11th April.

This is the sort of stuff I need to see in the crosstab:

28-Mar-07 | 11-Apr-07 | 25-Apr-07

Joe 5.00 | 5.00 |
Mary 5.00 | |

Basically what I want it to do is to add $5 each fortnight, depending
on how much they enter. It would be simple if they all paid $5 on
the due date, but they don't!

Is anything like this possible in a query? Or should I use code on
the form to split any monies paid into $5 lots, and add it on a
fortnightly basis?

Any help greatly appreciated!

DubboPete

 

[Michel Walsh]

The problem can be seen as being the table design, no the crosstab, at
least, to me And not its static aspect, but how you 'manage' it, how it
evolves, how you correct mistake (anyone does that stuff, so it should be
part of the plan), and so on.

Sure, the easy approach, easy for me at least, would be to have one record
per week and if Mary has not contributed for a given week, there is no
record mentioning her, for that week_date_stamp:


Employee, DateStamp ' fields name


and if you added Joe, by error, for a given week, then, correct the mistake
by deleting the record (or add it if he did pay but no record was ever
inserted). If Joe pay for the next 3 weeks, insert 3 records, with the
proper weeks stamp. If some employee can have more than one 'participation',
for a given week, add a third field, Amount. THe crosstab is thus:


TRANSFORM Nz(LAST(Amount),0)
SELECT Employee
FROM tableName
GROUP BY Employee
PIVOT DateStamp


or, if you don't have the field amount,

TRANSFORM Format(5*Nz(COUNT(*), 0), "currency")
SELECT Employee
FROM tableName
GROUP BY Employee
PIVOT DateStamp



as example.


Hoping it may help,
Vanderghast, Access MVP