Using 2 recordsets

[vicissitude]

Is it possible to open 2 recordsets. Manipulate the data from the first and use it to fill an 'empty' recordset so i could have the data formatted in a way that i could use it as the record source of a report?

If so is there any good material out there to read up on this.

Many thanks.

 

[Galaxiom]

Yes but that could also be done in a query.

The recordset technique involves a loop that reads from one recordset and writes to the other before advancing to the next record.

 

[smig]

you can also use an array to copy the data from one recordset to the other

 

[vicissitude]

 

[vicissitude]

Is this something along the right lines?

1. Bring query into code using recordsets

2. Scan recordset line by line and sort by category/date/cost into an array (Do i need to put into array first?).

3. Link a new recordset to a temporary table and then fill recordset fields sorting by month(date) and category

4. Close recordset (does this automatically save to table?)

5. Run the report off a query of the temporary table

Thanks.

 

[smig]

what do you need in the second table ?
if you need the totals of the cost use a grouped query - grouped by month and category

no need to export, not even need any recordsets or temp table

you don't even need to create any query and you can group it in the report level

 

[vicissitude]

Thanks smig for heading me off before i went off on some totally unnecessary direction!

I think i got you now.

Use a query and use the grouping function to 'squish' the values down.

Create fields in the query like this...

JanCosts: IIF(Month[InvDate] = 1, [Cost])
FebCosts: IIF(Month[InvDate] = 2, [Cost])

and then group by sum on each of these so that i only get one row of the one supplier and the costs are all summed on the appropriate months.

Thanks!

 

[smig]

use:
DateMonth = format([Date], "yyyymm")
group by DateMonth and Category, and Sum Cost

I suggest you avoid using Date as field name in your db. these kind of names can cause a lot of troubles.

 

[vicissitude]

Yea i no longer use date, the post was a slip.

I have a function call to filter the query for the tax year first and then i divide the costs by a field per month because i want to be able to get the query down to either only 1 row if i am summing all the cost values per month for a particular category or just have a row per category supplier. There may be many records of the same supplier per category.

Thanks for the help.

 

[smig]

use the wizard to create a Crosstab query. it will give you what you need.
play with it a bit till you learn how to create this kind of query.