MS Access- TOP / Offset? I Need To Order Results As Pages
Feb 18, 2005
Hi,
Sorry I have to post this here, but its sort of related to MS SQL anyway
I'm running a PHP system with MS Access. I need to order results in pages. (For those who are familiar with MySqL and Postgresql - I need the equivalent to LIMIT/OFFSET in MS Access).
I know I can "SELECT TOP 50" in my sql - so that solves the number of results per page.
But what about page 2,3,4..etc.... how do I select results from an offset?
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
I am experiencing some quirks when exporting to PDF. I have read all the other posts about page sizes, margins, etc and my situation seems to be a bit different.
I have a report that contains a bar chart (Height = 15cm, Width = 19cm) and below that a rectangle that contains two text boxes with totals information (Left = 11.6cm, Width = 7.4cm, Height 0.5cm)
Now most of the time it exports to PDF perfectly - all on one page as would be expected
If however the chart has more than say 20 items on the y-axis then the rectangle gets pushed onto an new page.
For extra information, but not to confuse the issue I also have a page header and footer that have items lined up right to the edge (left + width = 19cm) and they all display fine on the one page. It is just this rectangle that is having the problem.
When I move the rectangle to the left a bit (11.4cm) so 0.2cm away from the right hand edge then it works fine.
It is just very strange because I cant see how it is getting shifted only when the chart contains a lot of items.
Hi - I am trying to add paging to my stored procedure. The stored procedure successfully executes a full-text search. Unfortunately, the paging routine below the full-text search operates on the articles table after the search has been conducted. This means that it utilizes the row numbers from the entire table rather than the row numbers from the result set. I somehow need to the paging routine starting at "WITH tempArticles AS" to operate on the search results rather than the articles table. I am too new to SQL and can't figure out how to populate a temporary table storing text search results to use in this paging routine. Can anyone help me with this? Thanks! Peter dbo.Search_Articles @searchText varchar(150), @PageSize int, @PageNumber int AS Declare @RowStart int Declare @RowEnd int if @PageNumber > 0 Begin SET @PageNumber = @PageNumber -1; SET @RowStart = @PageSize * @PageNumber + 1; SET @RowEnd = @RowStart + @PageSize - 1 ; CREATE TABLE #results (ArticleID int) INSERT INTO #results SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText) /*this returns all matching records from the text search*/ SELECT * FROM articles, #results WHERE #results.ArticleID=articles.ArticleID; WITH tempArticles AS ( SELECT Title, PostDate, UserID, City, Country, Tags, StoryID, Approved, ROW_NUMBER() OVER (order by PostDate) as RowNumber FROM articles WHERE Approved = 1) SELECT * FROM tempArticles WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd; END
Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages. for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on... is there any way to achieve it using an SQL query? the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway. any suggestions? Thanks.
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
Dear All,I have developed a Data Access Page with Access as a project. It isconnected to a SQL-Server database. I try to edit the data in the table andam not able to do so. I might be missing some permissions. I have changeda number. What am I missing?Thanks in advance.Jeff Magouirk
I am new to internet development and would like some advice on the technology used to access a SQL database that sits on a network behind a firewall.
** ASP .NET Page ** -> ** Web Server ** -> ** FIREWALL ** -> ** SQL **
So to give an example; from an ASP .NET page on the internet, I would like to populate a DataGrid with the contents of a single table from a SQL database. The SQL database is sitting on our company network behind a firewall.
Could someone please explain / point me in the right direction in how the ASP .NET page / Web Server can securely access the SQL database.
I have a question about loading data on the page lode event. The question is more conceptual then how to.
Using C#, I would like to make a call to a SQL 2000 Server, with the use of a stored procedure return one row with eleven fields. Then use the data to fill five different controls. There is no manipulation of the data it is just presented as information. With a DataReader and the GetValues method using the resultant array I can fill the controls (or can I), or with a DataSet and the Load method do the same thing. Now this code can be placed in the code behind page or it can be implemented in a class. This is the only page that uses this combination of fields and controls; however I would think a more generic call to the data could be made then pick and chose the fields as needed. What process makes the most sense? Is what I just described even possible (as yet I haven’t tried it)?
As always thanks in advance for any thoughts, comments, and suggestions.
I am new to asp.net programing and am trying to put a basic secured login page on out IIS server to test, etc... Using the wbe matrix tool I was able to sucessfully login, and access avrious database tables without a problem, however on the IIS box I receive the following error when i attempt to access pages that use the database.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
Source Error:
Line 48: Line 49: Dim ds As New DataSet() Line 50: myCommand.Fill(ds) Line 51: Line 52: MasterGrid.DataSource = ds
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032
All asp & aspx pages that do not reference a database work fine. I have ready many articles about simialr problems, and it appears that I either need to cerate a aspnet user or add the nt authority as a database user. Can someone point me in the irght direction, and how to get this working?
I have the following query and I am wanting to get the results to be in order. Right now, it shows me the results by date, but the dates are out of order. How can I get it to give me the results by date in date order???
SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0) UNION SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)
HiI want to get the dateName of everything but I don't know how to sort them now. Like I have this:DateName(month,TimeDateStamp) As TimeDateStampWhat gets the dateName now I want to sort these so that they are in order.How do I do this?
i want to create a report so that a list of the top 30 records are returned to the report user. In the report i want to have the records position in the list shown (ie the first row should have 1. and the second should be 2. right on down to the 30th having 30.)
Hi there,I'm a little bit confused here. I the TOP 1 function with ORDER BY DESC to get the last id in my table but it doesn't seems to work.Here's an example:SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESCSELECT ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASCSo basically the first SELECT should return the last id value of the second query but it doesn't.The first query gives me that: 60721And the second one gives me that:60680606816068360684606856068660718607196072060721610506112261124So as you can see my TOP 1 ID ORDER BY ID DESC should gave me this result: 61124Am I missing something or... ?Please help me this is aleready in function so I have to fix it ASAP.Thank you, Regards,OR-THO
I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table: 1- the exact match for the search string come first from the two table. 2- and the partial match comes last after the exact match.
this is my DDL for the two tables :
USE [Northwind] GO /****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Person]( [PersonID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
second table: USE [Northwind] GO /****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Members]( [MemberID] [int] NULL, [Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF
and this my search query I have it in a stored Proc.
select *
from
(
SELECT PersonID, Type, Name, email
FROM Person WHERE (Name LIKE '%'@Name'%') union all
in microsoft doc there is written on the topic of BP Extensions with SSD's in SQL Server 2014: only clean pages are written to disk... does this mean data pages that have not been modified yet? or also those data pages that have already been modified, and where log has finished writing and the transaction has been marked as commited??
why are there clean data pages being written to L2 cache to make space for other not modified pages? I mean, shoudnt they be modified first, before letting other unmodified data pages into the Cache? I mean they have still to be modified..that makes no sense to me to page them out and page them in again just for other data pages...
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2 oct 3 sep 13 sep 21 sep 22 sep 30 aug 3 aug 5 aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
I am trying to Concatenate resulting classification names (X_DSC_CD_ABR below) in the following order (if the classification exists), separated by '/': WC/STD/LTD/FMLA/State/Military/Paid/Corporate/PFL/Other
Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane
I've read all the posts... set the security policy (for the service account running SQL Server) to lock pages in memory... ran gpupdate... still same error... HELP !!!!
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive HallP6309 N Olive <---- grouped with the C record because they have the same address HallE5488 W Catalina <---- back to alphabetical by first name HallJ7222 N Cocopas
This one has been stumping me for several days. I can run a query thatreturns several different items from several different manufacturers,each with a ranking score. Each manufacturer can have any number ofitems:Item_Name Manufacturer rankItem 1 Manu_A 82Item 2 Manu_A 65Item 3 Manu_A 41Item 4 Manu_B 32Item 5 Manu_C 21Item 6 Manu_B 19However, I would like the records to be ordered so that the highestranking item is shown first, then the next highest item from adifferent manufacturer is shown second, then the next highest item froma third manufacturer is show, etc.:Item 1 Manu_A 82Item 4 Manu_B 32Item 5 Manu_C 21Item 2 Manu_A 65Item 6 Manu_B 19Item 3 Manu_A 41Does anyone have any thoughts on how to order the results in thisfashion?thanks,Matt Weiner
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field. Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below: SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2], Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2], TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName FROM Ticket INNER JOIN TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID WHERE (Ticket.CreatedDate >= @StartDate) ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.
A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.
' where @SortString = 'd' and @Test is a temp Table
BEGIN
Insert into @Test
Select CompanyName,ContactId, CompanyId
from vContacts where CompanyName like @SortString +'%'
Order by CompanyName
Insert into @Test
Select CompanyName,ContactId, CompanyId
from vContacts where CompanyName not like @SortString +'%'
I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.
Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.
I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.
SELECT * FROM SAMPLE WHERE ret_ref_no in ( SELECT ret_ref_no FROM SAMPLE GROUP BY ret_ref_no HAVING COUNT(*) > 1 )
Hi! Has anyone experienced this problem? Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values. For example, the following query: SELECT columnA, columnB, columnC, columnD FROM table WHERE columnD = 'I' AND columnA IN (1,2,3,11,19) go
returns a different result than this query: SELECT columnA, columnB, columnC, columnD FROM table WHERE columnD = 'I' AND columnA IN (1,3,11,2,19) go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest: AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments... Thanks!
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable. INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in -- descending order
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
I have a query which can return multiple rows. I want to select only the nth row. In other db languages, this is achieved using the OFFSET command, but in SQL Server this command does not exist. I saw that you can hack it using ROWCOUNT and an ORDER BY clause, but I don't have anything to order by. Is there some other way to do this? My query is
Code: SELECT S.SystemName FROM Projects AS P INNER JOIN ProjectSystems AS PS ON PS.ProjectId = P.ProjectId LEFT JOIN Systems AS S ON S.SystemId = PS.SystemID WHERE P.ProjectId = @ProjectID
