Limit record count in query

Hello all,
I have a query that will return over 65K records (rows).  My end goal is to 
export this to Excel via code.  Excel has a limit of 65K rows on a single 
tab.  So, 65K to tab1, the rest of the records to tab2.  I figured I would 
have query1 that would limit the results to 65K, and query2 that would start 
at 65K +1 to the end.

How can I limit query1 to 65K and query2 to start at 65K +1?


0
Utf
12/5/2007 1:39:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1568 Views

Similar Articles

[PageSpeed] 53

I'm not sure how efficient this will be but you could try:

SELECT TOP 65000 *
FROM [query that will return over 65K records]
ORDER BY PrimaryKeyField;

and
SELECT *
FROM [query that will return over 65K records]
WHERE PrimaryKeyField Not IN (SELECT TOP 65000 PrimaryKeyField
FROM [query that will return over 65K records]
ORDER BY PrimaryKeyField);


-- 
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP 
http://www.access.hookom.net/UCP/Default.htm


"David" wrote:

> Hello all,
> I have a query that will return over 65K records (rows).  My end goal is to 
> export this to Excel via code.  Excel has a limit of 65K rows on a single 
> tab.  So, 65K to tab1, the rest of the records to tab2.  I figured I would 
> have query1 that would limit the results to 65K, and query2 that would start 
> at 65K +1 to the end.
> 
> How can I limit query1 to 65K and query2 to start at 65K +1?
> 
> 
0
Utf
12/5/2007 2:54:03 PM
Reply:

Similar Artilces:

2007 accdr Confirm record changes doesn't go away
I created a runtime version of my db. In the logon form, as per MS kb229802, I added the following code: Private Sub Form_Load() Application.SetOption "Confirm Action Queries", 0 Application.SetOption "Confirm Record Changes", 0 Application.SetOption "Confirm Document deletions", 0 It works fine on my pc. When I installed it on a customer's pc, it began displaying the familiar " You are about to append ....records..." This was a accdr runtime version, so there was no way to get into the settings to change the Confirm options. When I checked t...

2 MX records to two different Mail Servers
This is the scenario so please do not change it or make recommendation for the change...all hypothetical setup. I want to have two Exchange Servers at two different location to answer Internet Mail requests. These locations are NOT linked in anyway, but WILL use the same public DNS name for mail. For example, COMPANY.COM. If I have COMPANY.COM MX records pointing to two different IP Address (the two locations - I assume by MX weight records), will the mail arrive at the destination hosting the actual user mailbox? I have UserA@COMPANY.COM. Mail is destined to him - was not found on...

Selected subform record becomes unselected after code runs
After figuring out that you can't use a Split Form in Access 2007 as a subform, I went back to the old way of adding a datasheet view subform to my subform. (main, subfrm, subsubfrm). If the user clicks on a record selector on the subsubfrm datasheet, the subfrm goes to that record. However for some reason the record highlighted in the subsubfrm datasheet is no longer highlighted - it goes back to the first record. But if I put in a break point and step through the code, when it is done the selected record is still highlighted. The code is simple, nothing else happens on ...

Font color & Conditional Formatting limitations
I want the font color to be (automatically) different depending on the month that is entered in the cell. How do I have more than 3 conditions in a cell. (3 is the limit of conditions using Conditional Formatting). thanks. This cannot be done without VBA using the Worksheet_change event. Some background on this can be found a http://www.cpearson.com/excel/events.htm. Please give more specific details on the cell ranges and colours if yo want a macro crafted for this ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet mess...

Re: "SUMIF" or "COUNTIF" to count alpha characters as .5 ?
small example, we need to count employees for SICK DAY ( S ) as 1 point, and a late or TARDY ( T ) as .5 point. A1 = S B1 = T it should total to 1.5 I tried to use: =COUNTIF(A1:B1, "S" +1) ( equals 1 ) and I tried: =COUNTIF(A1:B1, "T" + .5 ) ( equals .5 ) But neither worked.....I also tried the SUMIF but no luck..... Any Ideas??? Thanks so much, this is a great group! You can use the following formula to total the S and T values: =COUNTIF(A1:B1,"S")+COUNTIF(A1:B1,"T")*0.5 Ange...

Couple of queries re macros (end of data / delete row if...)
Hi, firstly when using the basic record a macro feature, I often want to select the entire range for sorting / formating etc. The problem is if any of the fields are blank, you can end up pressing end | down several times, and this is no good for changing data. So question is, is there a way of doing this via the record a macro screen, or is there some code I can paste into my existing macro? Secondly, how do I get a macro to check the contents of column N, and if it contains the words "Loaded at Site", delete the entire row and resort the spreadsheet. Thanks in advance. You co...

How to count a coloum if two conditions are met
I want to count the number of entries in a column if its value = X but only if another column = Y, kind of like merging two countif statemnts so one countif is =COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials and the second is =countif(STATS!I:I,"ONLINE") So I want to count the number of rows with "online" in colomn I, but only on row's where coloum E = the value in B3 I have tried the following formula {=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STATS!I:I)))} as an array function, but it isn't working What...

Outlook Express Message count
Sorry for the double post. Background: By design (I think), Outlook Express 6 shows a count of unread messages on the left navigator, next to each folder name. The folders do not show the count of total or read messages next to the folder name. Thus, if all messages in a folder have been read, there won't be a numerical display next to the folder name at all. Now, if a user wants to see more message count information, and VIEW -> LAYOUT -> BASIC -> "Status Bar" is checked, the status bar at the bottom of Outlook Express window will appear, and display the total messag...

Need help with the Query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. -- Please ignore previous posted. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CO...

Mailbox Store Limit Issue Warning
Is there a way to have the warning e-mail that goes to the offending mailbox also be sent to myself? Thank you, Robin Deatherage no way native to Exchange that I am aware of... -- Susan Conkey [MVP] "Robin Deatherage" <RobinDeatherage@discussions.microsoft.com> wrote in message news:32640885-2848-4ABF-A16F-84306C4A930F@microsoft.com... > Is there a way to have the warning e-mail that goes to the offending mailbox > also be sent to myself? > > Thank you, > Robin Deatherage Ok. Thank you for the information. "Susan" wrote: > no way native ...

Excel query from Access databases
Hi, I have two separate databases in Access that have a common field of CustNo. I'll like to run a query in Excel using Get External Data... whereby I could create a report that brings in data from both the databases. Having tried this in Excel, it seems that I can only use one table at a time, is there a way to overcome the issue. Thanks, Rob Hi Rob- "I have two separate databases in Access that have a common field of CustNo." Is that accurate or do you mean 2 separate Tables in 1 Access DB file which use CustNo as the common field? AFAIK you can only query one Access...

Counting Selected/Highlighted Rows
Need to modify this maco to not count hidden rows. For example if seven rows of ten rows in question are hidden, I need the macro to count only the three visible on screen. Also, if no rows were hidden, and all ten were selected (highlighted), the macro should count ten. In other words, it should count only what is selected/visible on screen. Sub CountHighlightedRows() MsgBox "Rows Selected: " & Selection.Rows.Count End Sub Not sure if I understand, but try this: Sub fj() Dim x As Long x = Selection.SpecialCells(xlCellTypeVisible).Count MsgBox...

Mailbox not re-enabled after increasing mailbox storage limits.
There is a mailbox which exceeded its sorage limits last week. By mistake on Friday last I set the limits to around 200 KB instead of 200MB On discovering the mistake today, I have now set the mailbox limits to much bigger figures around 600 Mb upwards, but nothing I can do seems to make Exchange pay any attention to the new figures. It is still rejecting emails to the account despite having manually run Mailbox Cleanup agent and Mailbox management process. I even tried dismount and mount of the mailbox store. Why why why, are these new limits not having any effect? What do I need to d...

word count in Excel 2003
How can one get a word count in an Excel document? Traduc, If you have Word, paste the sheet into it, and use File - Properties - Statistics. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Traduc" <Traduc@discussions.microsoft.com> wrote in message news:37B364F5-2029-469A-87CA-D0BC82365718@microsoft.com... > How can one get a word count in an Excel document? Thanx "Traduc" wrote: > How can one get a word count in an Excel document? ...

Make a query pick a specific report.
How do I call up a specific report based on 2 or 3 fields in a query or table? I have 4 reports, each design somewhat similar, but a paragraph different. I would like the form fields (CustomerID and ContractID) determine which report opens to print or preview. For example: If Customer ID=1 and ContractID=1 then print Report A. If Customer ID=1 and ContractID=2 then print Report B. If Customer ID=2 and ContractID=1 then print Report A and so on. If anyone can help me with this I am greatly appreciative. Frank One way will be just as you wrote it If Customer ID=1 and ContractID=1 ...

Easy Way to Pull Forms Record Source Into Combo Box
I'm using a combo box to perform filters and sorts on a form. Is there an easy way to pull the fields in the record source into a combo box? Thanks BW Ok...just checked back in and realized how silly this question sounded. I'm wanting to populate a combo box on a form and pull the fields from the forms record source into the record source for the combo box. Thanks BW "BW" <basic8@hotmail.com> wrote in message news:ebU$dU0DIHA.1316@TK2MSFTNGP02.phx.gbl... > I'm using a combo box to perform filters and sorts on a form. Is there > an easy way ...

imported records
Can anyone tell me if there is a way that you can change the # of records that import to excel. I believe it is 63000 something. Thanks Your limit is 65,536 records per worksheet. You cannot extend that limit. The number of worksheets, however, is limited only by memory. HTH Jason Atlanta, GA >-----Original Message----- >Can anyone tell me if there is a way that you can change >the # of records that import to excel. I believe it is >63000 something. > >Thanks >. > Maybe you could use your favorite text editor and split your data into pieces--and then import...

Merging Account Records 12-19-03
I have a client who would like to merge account records together with all of the history into one record. Has anyone developed a utility to do this and ready for resale? Hi Deb, when you say merge, how are you thinking of storing/viewing this merged data? A report? On a printout? On a custom form? Showing all the history data summarized in a memo field? Mike "Deb" <anonymous@discussions.microsoft.com> wrote in message news:000301c3c64a$1870a1a0$a301280a@phx.gbl... > I have a client who would like to merge account records > together with all of the history into ...

Count Wednesdays within a calendar month
How can i count how many let's say Wednesdays have each month of a year, each year? -- sato panago see your other post -- Biff Microsoft Excel MVP "sato" <maddoctor@metro.gr> wrote in message news:A9356C1F-F17C-4BBA-BC13-CFED6A6C2D92@microsoft.com... > How can i count how many let's say Wednesdays have each month of a year, > each > year? > -- > sato panago ...

Select value in query
I have the following sample field & data in one table MyValue Value1 Value2 Value3 100 20 30 90 200 null 210 150 My goals here is to divide myvalue with either value1 or value2 or value3 whichever is higher. e.g in line one 100/90 and 200/210 in line two. how to create the expression in my query thanks On Thu, 4 Feb 2010 05:26:01 -0800, zyus <zyus@discussions.microsoft.com> wrote: select MyValue/ iif(v1 > v2, iif(v1 > v3, v1, v3),iif(v2>v3,v2,v3)) Because you have nulls, you may ...

Formula for Counting Two Characteristics in Separate Columns
I have a list of Sales People's Names in "Column B", in "Column C" I have the client name and in "Column D" I have an indication of whether the account is "Active" or "Inactive" . In a summary field I want to calculate how many "Active" accounts "Bob" has, then in another summary field show the number of "Inactive" accounts "Bob" has. I have done this before, but seem to be struggling with the formula this morning. Any help would really be appreciated. Thanks, Paul Hi, =SUMPRODUC...

Search Record
I would like to have a button on my form or a separate form that can perform searh for records. My form contains fields two of which are "Start Date" and "End Date". I would like to search for records with a specific start dates and end dates. what is the best way to do this. Thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 Hi, Try this site. http://allenbrowne.com/ser-62.html >injanib wrote: >I would like to have a button on my form or a separate form that can perform >sea...

Microsoft Query #9
following issue, I have created a Query in Excel and need to edit the query, because I have to change the filter by column to change criteria. it's working fine on my computer, but when another person tries to edit the query he gets an errormessage "This query can not be edited by the query wizzard" and gets directly into MS Query. But I need the Wizzard to make it more user-friendly. Can anybody help? thanks in advance! regards SB hi, are all users using the same version of excel? You might also think of changing the MSQ to a parameter query where the query prompts ...

Setting up Gmail Limits with IMAP or POP3
I want to setup outlook 2007 on windows 7 for my friend to access a gmail account with a large number of messages in it. My friend has been using gmail with pop3 for years with outlook 6 (The old built in email client with windows XP), and all messages are currently stored on gmails servers, ~20,000 messages in the inbox, and ~10,000 messages in the sent folder. Total messages in the account (inbox + sent) equals ~3GB. I'm trying to determine if it would be better to use IMAP or POP3 for her to now access her emails from GMail using outlook 2007 as the client. IMAP could ...

union query
Is there any limitation on the union quries? I am trying to union 15 quries and getting system resources exceeded msg, but if I union 10 quries it works. Thanks Tony ...