Transferring records from one table to another

[Paulch]

Afternoon all.

I have a problem that I've been getting nowhere with for a while, and would appreciate your help.

I have a table in Access 2003 which holds details of projects (dates, budget, descriptions, etc).

I need to end up with a results table that shows the budgeted cost spread over the number of periods of the project - eg Start Date 1st Jan end date 31st Mar = 3 periods, if cost is £30k then £10k per period.

I'm struggling with how to create multiple copies of each line, but inserting the period and apportioned cost.

The resulting table will need to be recreated each month, and then compared to last months for differences, though I think this is a separate problem!!

I really need help with this one!

Cheers

P

 

[CBrighton]

I'd create something in VBA to do this.

First it would run a query to delete all data in the results table.
Then it would define two recordsets, one for the data table one for the results table.

I would then use a For...Next loop to work through each record in the data table. Something like DateDiff("m",[StartDateField],[EndDateField])+1 will give the number of months between the two dates (I work by trial and error, if this didn't work I'd use a couple of Month([FieldName]) functions andYear([FieldName]) functions to capture the months)which I would assign to an int variable and then use to create a second For...Next loop within the existing one to create the records in the results table.


Depending how knowledgable you are with VBA this may point you in the right direction or not help at all!

Unfortunately the longer a process being requested the harder it is to explain it.

 

[Paulch]

Thanks for the prompt reply.

I'm glad to see that I wasn't too far away myself. I have a sheet of A4 with various filters and calculations for the methods for choosing the record! My problem centres around the VBA necessary to acually transfer the details of one recordset to the other. I can see how creating the int variable would give me the number of loops, but I am struggling with the mechanics of the transfer.

Cheers

P

 

[CBrighton]

You can't transfer as such, just populate the record in the new table with the data from the fields in the data table.

It's like trying to do it with queries where you can't move data but have to use an append (copy) query and a delete query.


Assuming you have multiple recordsets open it should be as simple as:

rst1.AddNew
rst1!field = rst2!field
rst1.Update

This would Add a new record in the recordset assigned to rst1 then populate the field called "field" in the recordset you have assigned to rst2 to the field called "field" in the recordset assigned to rst2 and save the record.


You could then run a delete query to delete the records from the data table if you wanted to.

 

[Paulch]

OK, thanks very much. This is my first attempt at working directly with recordsets in VBA, so I'm not too sure where to start!

I'll give it a go, and see how far I get.

Thanks, I appreciate the help.

P