Extract & count data from Access program

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

--
ncro
-----------------------------------------------------------------------
ncron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3118
View this thread: http://www.excelforum.com/showthread.php?threadid=50854

0
2/4/2006 8:51:14 PM
excel 39879 articles. 2 followers. Follow

1 Replies
712 Views

Similar Articles

[PageSpeed] 45

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


-- 
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21419
View this thread: http://www.excelforum.com/showthread.php?threadid=508548

0
2/4/2006 10:29:54 PM
Reply:

Similar Artilces:

HTTP Error 401.3
Hi, I hope someone can help me. CRM worked fine for a while, but suddenly everybody, except the CRMAdmin account gets the following error: HTTP Error 401.3 - Unauthorized: Access is denied due to an ACL set on the requested resource. The website is runnning on SBS2003 and i created the website myself before installing CRM. The folder where the webdata is stored is :\inetpub\MSCRM IUSR account does have the right permissions.. I cannot find any messages in the IIS website logfile I found some articles about this error, but i do not know where to start because they assume you have the w...

CollegestudentHelp me create an automated data entry program in Ex
I have detailed customer information and I want to be able to type in the customer ID and have the rest of the information fill itself in. Please help. All in Excell. use vlookup function. see help Desperate College Student <Desperate College Student@discussions.microsoft.com> wrote in message news:67CDC09B-4121-4F68-A0A6-0DCC7C09543E@microsoft.com... > I have detailed customer information and I want to be able to type in the > customer ID and have the rest of the information fill itself in. Please > help. All in Excell. As suggested you can use the VLOOKUP function...

Outlook Web Access attachment problem
We have a program that will generate a .snp file and then email it to whatever recipient it needs to go to. If they receive the email in Outlook it will allow them to open the file properly with the Snapshot viewer. However, if they go to the same email in OWA and try to save the attachment, it attempts to save it as an .mdb file. Why would OWA ignore the original extension and try to give it a different one? "Joe Giddings" <j_nospam_giddings@thehammocksource.com> wrote in news:jI9gd.11726$ta5.6952@newsread3.news.atl.earthlink.net: > We have a program that will genera...

Reformat data to vertical format
Here is what I am trying to do. http://www.totalcontrolproducts.com/totalcontrolproducts_OLD/download/images/Untitled-1.gif I have about a thousand records that I need in a vertical format with normal shared field name. Any suggestions -- Psydwaz ----------------------------------------------------------------------- Psydwaze's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=37965 see Creating a Spreadsheet from Database data (#dbdata) http://www.mvps.org/dmcritchie/excel/snakecol.htm...

URL access
Is URL access possible in a view of "account" in CRM3.0? In addition, can you hand search condition? Hello, I searched the SDK for this, but only found out about URL addressable forms. Seems to me like you have to write a custom ASP.NET web application (grid) to write this behavior. Anyone else? Best regards, Merijn van Mourik "a-kun" wrote: > Is URL access possible in a view of "account" in CRM3.0? > In addition, can you hand search condition? ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

Line Graph with two different data points
Hello, I am trying to create a line chart with numbers from my product Vs a competitor. The problem i am having is how can i make a chart that will have four data points comparing to each other? For example Product A- 1.5 1.2 1.6 1.8 Product B- 2.0 1.1 1.2 1.3 Product A- 70% 20 % 30% 67% Product B- 65% 30% 65% 55% How do i get all of these numbers on one chart? Is a line chart correct. I want to see these numbers compared?? Thank you so much Hi, Since the number don't appear related to the percentages you might plot them on the same chart but two different axes. If they were relate...

Counting 04-26-07
Hello, I am having difficulty counting numbers within a very large table based on survey results. The table is set as follows subjectname, q1a, q1b, q1c, q1d...q5e Values for each question range from 1-5 and the field could be empty if no response was given. I have tried the something similar to the following and it is not counting correctly. Select subjectname, count(iff(q1a=5 or q1b=5 ... or q5e=5, 1, 0)) from table group by subjectname My desired outcome will be to display the total 5's, Total Responses for the subject (count where the response is in 1-5), and from this I can calcu...

lotus approach queries VS access queries.
Hi, We are migrationg from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Access 2007 combobox will not "Auto expand"
I have a database converted from access 2003 to 2007. Now ALL comboxes will not allow the "autoexpand" functionality. Obviously I set this property to yes (both in the properties window AND programmatically). Most, but not all, of the comboboxes are populated by queries on ODBC coupled MSSQL tables. Again, in 2003, this worked fine. Anybody an idea ? Thanks in advance for any suggestions, Jos --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- ...

Data changes when Analyzed with Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

odbc connection with access comma is not showing
I have made a ODBC connection between NAVISION and Access. When I run the query a number "23" shows up as "2300000". I think the comma is not showing maybe because of setting problem. I do not know how to fix this. In the Navsion database figures are correct. sounds like a ODBC driver problem. Probably due to precision see http://dynamicsuser.net/forums/t/6072.aspx Pieter "Remko Strik" <Remko Strik@discussions.microsoft.com> wrote in message news:F83F3871-DDCB-44E7-BC50-6CAD87659284@microsoft.com... >I have made a ODBC connection between NAVISION ...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

Access violation in CDaoQueryDef::Open or CSimpleStringT::GetLength(). VC++ 6.0 app compiled in Visual C++ .NET
Hi, I have a problem using DAO in Visual C++ .net Standard edition. We have an old project created in Visual C++ 6.0 Professional. The projects compiles, links ok (with a lot of warnings telling the DAO-classes are deprecated). The compiled program executes ok until I am about to access the database. Some accesses goes ok, but some are not. It is always the same databse accesses that the program crashes on. The database that I'm using is Access 97. I also have tried converting it to 2000 and 2002. In this function the crash occurs in daocore.cpp: void CDaoQueryDef::Open(LPCTSTR lpszName...

Problems with DATE data type
Hi!!! I've got a little problem with the next macro in Excel. The problem is that I must look for some information in a data bas using as filter a specific day an hour in the year. I get the date and the hour using a form and then the macro calle "Busqueda" receives the value. I've programmed the macro using MSQuery just to see the necessary cod and the problem has appeared when I've tried to include the para called "Dia" in the SQL code. I've tried using " & Dia & " but it doesn't work and I don't know ho to include my param...

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

How should I debug or adobt better data processing for high speed
Hello! I have a program in MFC that collects data via USB, process it and display graphs. When data rate is relatively low(max. 1K bytes/Sec) the program works Ok but when data speed exceeds 10K bytes/Sec program goes crazy after a while and shows fictionally data . There is no problem for display section because it is buffered ( I have a buffered DC). The problem seems to be data processing. Because program is huge I can't post codes and I do not expect a solution but I try to explain general data processing scheme and I hope someone can advise me how to find weak points of progr...

access application
what are the applications of Microsoft Access ...

Allow read/write access to a file only from my process
Hello, I have this situation: I need to create a file and alow access (read/write) only from my application process. But I can�t use any kind of CFile::Open flags like CFile::ShareDenyRead because I need to access the file simultaneously from my app, in other words: I want to share the file but only inside my process. The previous situation could seem extrange so I will explain my app deeply: My app hosts a FlashPlayer Activex control and I have the SWF file inside the executable as a resource. As I can't load the SWF file as a stream I need to extract it to a temporary folder, but...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

Fill color in autoshape based on cell data
Dear Experts I have a range of cells with an autoshape in each cell. I would like to have the fill color of the autoshape change based on the results of an if statement in each cell. The if statment would return "G", "R", & "Y". The fill colors would be green, red, and yellow. I don't want conditional formatting for the cell fill color, only conditional formatting for the shape I was thinking a select case macro, but can't seem to get the code correct. However, I am a novice at macros Thanks for any assistance Jodi You could use the worksheet ...

excel count cell with color conditional formatting
Can i count number (sum) of cells in a column colored with conditional formatting ? thx the formula is =ISNUMBER(MATCH(D3;$AL3:$AR3;0)) column D3:D400 color red It' difficult. See http://www.xldynamic.com/source/xld.CFConditions.html -- HTH RP (remove nothere from the email address if mailing direct) "Marco" <marco.w-no-spam-please@email.it> wrote in message news:OL%23BeyG7FHA.2384@TK2MSFTNGP12.phx.gbl... > Can i count number (sum) of cells in a column colored with conditional > formatting ? > > thx > > the formula is =ISNUMBER(MATCH(D3;$AL3:...

Access
I use the "filepicker"to display pictures in my stockforms. (like in employees of North wind) I would like to display the pictures also in a report. Is that possible without loading as OLE Oject? Helmut hs4297@hotmail.com ...

Copy Data from Chart to Chart #2
I have to create separate charts for ten employees. I want to have all the data on one sheet tab. Create the chart on another tab for employee 1, format the chart the way I want and be able to add the data for employee 2, 3, 4, etc. to each tab, using the same format for chart 1 so they all match. So there should be one tab for raw data and 10 tabs for each employee. I’m using Excel 2000 Thank you Karen - Make a chart for the first employee, and make it look just the way you want it. Then right click on the sheet tab, select Move or Copy, and make a copy. Then make eight more copies....

Accessing
I have a object which is a member of my CWinApp derived App object. How do I access the member from the my view class? In essence how do I access the App object? You can use AfxGetApp()-> or there is an external variable set up called theApp where you can just say theApp.m_variable. I try not to do this too often and usually set up getters and setters for things I plan to access. Tom "MFC" <MFC@nospam.com> wrote in message news:u5xLz$YrFHA.2924@TK2MSFTNGP10.phx.gbl... >I have a object which is a member of my CWinApp derived App object. How do >I > acces...