Access 2010 query help needed

[Mr_P]

Hi

I have been struggling for 2 days trying to find a solution to my query problem. I am using vb.Net with access as a back-end and keep running into errors with a query I am trying run which fetches records {0} at a time so instead loading 28000 records I can load so many at a time. Now I must admit, I am a complete novice with regard to access and inherited as part of a brief and am not even sure if the query I am using makes sense. But that is what I got to work with.

I have been informed by research and forums that I need a Order by clause, but when I try to run, I keep getting an error of:

The SELECT statement includes a reserved word or argument name that is misspelled or missing.

Select TOP 4  *, Count(*) AS TOTAL From [Select TOP (17 - (2 - 1) * 4) From Postings Order By Customer ASC] Order By Customer ASC

Please someone put me out of my misery and point out my error. Many thanks

 

[plog]

Your query is all kinds of weird.

A. (17-(2-1)*4) equals 13, so why not just use 13?

B. You are using COUNT in the select portion, but you do not have a GROUP BY clause--that will blow the whole thing up.

C. Your subquery selects the top 13, but then the main query selects the top 4--why not just select the top 4 to begin with and omit the sub-query?

D. Brackets indicate a field, which means your sub-query is being treated not as a sub-query but as a fieldname.


I suggest trashing the SQL you have, iposting sample data from your table and then what results you want returned based on that sample data. Then together we can write the correct SQL to do that.

 

[Mr_P]

@Plog
Thanks for reply. As I am a novice to access, how do post a table here. Is there a way to output table in query form from access? Thanks

 

[jdraw]

see http://www.access-programmers.co.uk/forums/showthread.php?t=212124

 

[Mr_P]

Here is the sql that gives me say the first 10 records for a parametrised customer. What I need to do is create a next and previous option to load the next 10 records or the previous 10 records. A pagination. I have posted my simple sql statement and I shall find a way to post then table. Thanks

SELECT TOP 10 Postings.[Ref no], Postings.[Customer], Postings.[Dept], Postings.[Dept name], Postings.[Date], Postings.[Net]
FROM Postings
WHERE Postings.[Customer] = ?

 

[Mr_P]

Any further help with this please. Thanks

 

[plog]

I suggest trashing the SQL you have, posting sample data from your table and then what results you want returned based on that sample data. Then together we can write the correct SQL to do that.

 

[spikepl]

For OP and anyone else reading this: your issue is unlikely to be unique. 90 % of all problems raised on AWF have been dealt with by others.

Since Google still is free, google your issue. And again, And again. The time you spend learning how to find stuff on Google, how to identify the essential keywords, will save you tons of money and frustration.

http://stackoverflow.com/questions/1900635/how-do-i-implement-pagination-in-sql-for-ms-access


I used

vb.net paging access db

as search terms, and the link was the second to appear

 

[Mr_P]

spikepl, That link is where I got the code from in thr first place that everybody says dosen't work. I went to the last entry and that poster had a blog with the code I am using to get the result I posted, where people say the sql dosen't make sense. Thanks

 

[spikepl]

You have not copied his SQL literally, but left out a dot, which is why you are in the doodoo. EACH CHARACTER MATTERS!

Well - apparently neither did the author, but look at his screen shots!

 

[Pat Hartman]

To make sure your queries work, put a stop in your code and take the SQL string after it is built, and paste it into a query in SQL view in the Access database. You will get better error messages and probably be able to see the syntax errors.

Rather than allowing a user to select 28,000 rows albeit 15 at a time, I would provide better, more flexible selection criteria and drastically limit the number of rows returned. A client/server would never allow a user to select 28,000 rows except as an export and neither should a web page. If a user needs that many rows, offer an option to export to a .csv file or XML.

 

[Mr_P]

Pat, Being as I am a new user to access, how would you code a query to perform this task. Thanks

 

[Pat Hartman]

Access is no different from other RDBMS'. Just minor syntax differences. Spike gave you a link that will help build a query to select blocks of records.

 

[Mr_P]

The link the spike gave me is the original link where I found the query. However, I cannot get it to work. I posted my statement that I was using and it is not correct and I do not know how to correct it.

1) Do I need to incorporate COUNT within the select statement or it it declared on its own.

2) I do not understand the relationship with the brackets. I know that the brackets are used if your field name has a space or it is a reserved word. But not in relation to how I posted it.

I hope you can clarify for me the correct way to code this statement so I can move on with my pagination.

many thanks

 

[Pat Hartman]

What does your query look like now? I don't believe that Top can be calculated. You need to do the calculation in VBA and then build the query string including the new Top amount.

 

[Mr_P]

Pat. This is the query I am using to pull the first 100 records from the db. Is there a way to pull the next 100 and son on. Thanks

SELECT TOP 100 Postings.[Ref no], Postings.[Customer], Postings.[Dept], Postings.[Dept name], Postings.[Date], Postings.[Net]
FROM Postings
WHERE Postings.Customers = ?

 

[MSAccessRookie]

Pat. This is the query I am using to pull the first 100 records from the db. Is there a way to pull the next 100 and son on. Thanks

SELECT TOP 100 Postings.[Ref no], Postings.[Customer], Postings.[Dept], Postings.[Dept name], Postings.[Date], Postings.[Net]
FROM Postings
WHERE Postings.Customers = ?

If this is the exact SQL Code, then you have an invalid Syntax in your WHERE Clause. The WHERE Clause must have a specific value to compare to, and yours does not. Two possibilities occur to me:

1. Postings.Customers is a String, and should be compared to "?", not ?.
2. You want to accept any value for Postings.Customers, in which case the WHERE Clause would not be required.

If there is another explanation, please let us know.

-- Rookie

 

[Mr_P]

Thanks for reply. How hard can this be? All I am trying to do is pull the first 100 records based on a paramatised query using vb.net as front end and then click a button for the next 100 records. You get the idea. I have declared my vars like the code below. It is just a pagination example which I got from this a tutorial site. Now I know the sql statement is incorrect, but do not know how to correct. Many thanks

Dim Row_Per_Page As Integer = 10
    Dim TotRows As Integer
    Dim Page_Number As Integer = 2

sql = "Select TOP 100 " & Row_Per_Page.ToString & " *, Count(*) AS " & TotRows.ToString & " From [Select TOP (" & TotRows.ToString & " - ((" & Page_Number.ToString & " - 1) * " & Row_Per_Page.ToString & ") From Postings Order By Customer ASC] Order By Customer ASC"

 

[David R]

http://www.access-programmers.co.uk/forums/showthread.php?t=238593 has some ideas you can try out. Google is your friend.