select top 10 Name, Price from MyTable order by Price desc Updated: @Fionnuala pointed out that: “Access SQL selects matches, so it will select all items with the same highest prices, even if this includes more than 10 records. The work-around is to order by price and a unique field (column).” So, if you have … Read more
The easiest way to do it is to link the CSV-file into the Access database as a table. Then you can work on this table as if it was an ordinary access table, for instance by creating an appropriate query based on this table that returns exactly what you want. You can link the table … Read more
There is so much misinformation in the answers in this thread that I don’t know where to start. I just spent 4 points in reputation voting down the answers with misleading and wrong information in them. the Jet database engine (which is all that’s involved here, as the OP clarified with an edit) is by … Read more
You can easily write a bit code that will read in a file. You can either assume one sql statement per line, or assume the ; So, assuming you have a text file such as: insert into tblTest (t1) values (‘2000’); update tbltest set t1 = ‘2222’ where id = 5; insert into tblTest (t1,t2,t3) … Read more
Here are some notes that may help you towards your goal, however life would be a lot easier and a lot safer with autonumbers. This is VBA as you mention MS Access. Function NextTranNumber(ByRef FirstTran As Long, _ ByRef LastTran As Long, Optional BlockSize = 1) Dim cn As New ADODB.Connection Dim rs As New … Read more
Consider: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT “Cat” & DCount(“*”,”Data”,”ID=” & [ID] & ” AND Cat<‘” & [Cat] & “‘”)+1; Or if there is a unique record identifier field – autonumber should serve: TRANSFORM First(Data.Cat) AS FirstOfCat SELECT Data.ID, Data.Name FROM Data GROUP BY Data.ID, Data.Name PIVOT … Read more
In Access SQL we can sometimes use a self-join to produce a rank order. For example, for [table1] TID UserId TSource TText — —— ——- —– 412 homer foo bar 503 marge baz thing 777 lisa more stuff the query SELECT t1a.TID, t1a.UserId, t1a.TSource, t1a.TText, COUNT(*) AS TRank FROM table1 AS t1a INNER JOIN table1 … Read more
While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly, “Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query.” Consequently, you must select the calculated field using … Read more
My approach to security in Access is the following: Encrypt the main database using a complex password. Use a second database to log in to this encrypted database. The second database stores usernames, user salts, and an encrypted version of the main database password with the user password. This way, no user needs to have … Read more
While the Access/JET TOP keyword does not directly provide an OFFSET capability, we can use a clever combination of TOP, a subquery, and a “derived table” to obtain the same result. Here is an example for getting the 10 rows starting from offset 20 in a Person table in ORDER BY Name and Id… SELECT … Read more