[AccessD] Flattening out a child table

On 30 Sep 2004 at 9:40, Colby, John wrote:

> I have a data export that I need to do.  An "Advise to Pay" (ATP) tells the
> insurer to pay on a claim, how much and for how long.  Part of that
> information will be Offsets to the payment.  Pay this amount gross, but
> deduct this amount and this amount and this amount.  The offsets consist of
> a code (type of offset), offset from date, offset to date and amount of the
> offset.  Since the offsets are in a child table to ATP, any advise to pay
> can have from zero to unlimited offsets.
> 
> I am taking this data - Claim, claimant, ATP and Offset - and inserting it
> into a single record where there are repeating fields (denormalized
> obviously) for things like the offsets.  The export format gives me 16 sets
> of fields (code / from date / to date / amount) into which I can insert up
> to 16 offsets, which realistically is more than I need.
> 
> My question is, can this be done only in SQL or am I going to need to build
> a temporary table to put this in and run a program to pull the data from the
> offset records and jam it into sequential offset "slots" in the temp table?
> I can't for the life of me envision SQL able to flatten out n to 16 offsets
> into repeating field groups in this manner.  For one record, yea, for N ATP
> records...  It seems this is a crosstab kind of thing but my brain just
> isn't kicking out the solution this AM.
> 

Trouble with crosstabs is you can only have one Value field and you are 
looking at four.   I would probably do this in code. Build a function which 
pulls the offsets for a specific claim from a recordset and structures the 
output appropriately. Depending on what you are exporting to, you can 
possibly then just write the data directly to your export file without 
building any temp table.




-- 
Stuart