Combining records by date

  • Thread starter Thread starter nadesico
  • Start date Start date
N

nadesico

Hi,
I am a novice at programming so please bear with me. What I am trying to do
is combine records from a query and place them in a table. Basically, what I
want to do looks like this.

Starting Data
client id auth no start date end date units
1 2 9/16/2009 9/28/2009 13
1 3 9/29/2009 9/30/2009 2
1 4 10/1/2009 10/4/2009 5
1 5 11/17/2009 11/22/2009 6
1 6 11/23/2009 11/30/2009 3
1 7 12/1/2009 12/7/2009 8
2 8 11/20/2009 11/30/2009 11
2 9 12/1/2009 12/4/2009 4
2 10 12/29/2009 12/31/2009 3
2 11 1/1/2010 1/11/2010 11

End Data
client id auth no start date end date units
1 2, 3, 4 9/16/2009 10/4/2009 20
1 5, 6, 7 11/17/2009 12/7/2009 17
2 8, 9 11/20/2009 12/4/2009 15
2 10, 11 12/29/2009 1/11/2010 11

I don't think I can do this with a query so I posted it here. Any help
would be greatly appreciated. I am currently using access 2000

Thanks
 
On Thu, 25 Mar 2010 17:42:01 -0700, nadesico

I would do this with a recordset. However it really requires more
programming skills than can be expected of a novice.
In pseudo-code:
Open a DAO.Recordset object on the table, sorted correctly.
Loop over the records, looking for gaps in the dates. All the way
accumulating the first begin_date, last end_date, and consequtive
auth_no's.
If a gap is detected, write a record to the EndData table.

-Tom.
Microsoft Access MVP
 
Back
Top