Extract & count data from Access program

[ncron]

Hello All,

I am new to advanced applications in Excel. Here is my problem.
I have an access database that I need to extract data from.
The extraction process works fine. i extract the updated data int
excel,
then I need to count down 45 rows and generate a Page Total heading an
show that page total.
The hard part is that the extracted data is always expanding. How do
extract - get the page totals - continue extracting 45 rows per pag
-total each page and show a final total at the end of the existin
data?

Can anyone please help?
Thank You

 

[Ron Coderre]

Experiment with this and see if it can work for you:

In the destination sheet
A1: Page
A2: =INT((ROW()-2)/45)+1

When you extract the data, start the destination range in Cell B1.

After the data has been extracted, copy cell A2 down through the last
row with extracted data.

That formula will show:
1 in cells A2:A46
2 in cells A47:A91
etc...changing values every 45 rows.

Then
Select from A1 through the end of the data.
Data>Subtotals>
At each change in: Page
Use Function: SUM
Add subtotal to: (select the fields to be subtotalled)
Check: Summary below data
Click the [OK] button

That will put subtotals every 45 rows of data and put a grand total at
the end.

Note: the Page calculations in Col_A will end up being a bit off AFTER
adding the subtotals, but the row counts will be correct. Just don't
include that column in the print range.

Is that something you can use?

Regards,
Ron