How do I count unique items in field in Access query?
Try this SELECT Count(*) AS N FROM (SELECT DISTINCT Name FROM table1) AS T; Read this for more info.
ms-access
SQL Query – Using Order By in UNION
Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION. In this case SELECT * FROM ( SELECT table1.field1 FROM table1 ORDER BY table1.field1 ) DUMMY_ALIAS1 UNION ALL SELECT * FROM ( SELECT table2.field1 FROM table2 ORDER BY table2.field1 ) DUMMY_ALIAS2
Table-less UNION query in MS Access (Jet/ACE)
You didn’t overlook anything. Access’ database engine will allow a single row SELECT without a FROM data source. But if you want to UNION or UNION ALL multiple rows, you must include a FROM … even if you’re not referencing any field from that data source. I created a table with one row and added … Read more
Bang Notation and Dot Notation in VBA and MS-Access
Despite the (formerly) accepted answer to this question, the bang is not in fact a member or collection access operator. It does one simple and specific thing: The bang operator provides late-bound access to the default member of an object, by passing the literal name following the bang operator as a string argument to that … Read more
Classic ASP SQL Injection Protection
Stored Procedures and/or prepared statements: https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes? Catching SQL Injection and other Malicious Web Requests With Access DB, you can still do it, but if you’re already worried about SQL Injection, I think you need to get off Access anyway. Here’s … Read more
Code to loop through all records in MS Access
You should be able to do this with a pretty standard DAO recordset loop. You can see some examples at the following links: http://msdn.microsoft.com/en-us/library/bb243789%28v=office.12%29.aspx http://www.granite.ab.ca/access/email/recordsetloop.htm My own standard loop looks something like this: Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(“SELECT * FROM Contacts”) ‘Check to see if the recordset actually contains rows If Not … Read more
Case expressions in Access
You can use the IIF() function instead. IIF(condition, valueiftrue, valueiffalse) condition is the value that you want to test. valueiftrue is the value that is returned if condition evaluates to TRUE. valueiffalse is the value that is returned if condition evaluates to FALSE. There is also the Switch function which is easier to use and … Read more
How to simulate UNPIVOT in Access?
This query … SELECT ID, A, B, C, [Key 1] AS key_field FROM tblUnpivotSource UNION ALL SELECT ID, A, B, C, [Key 2] AS key_field FROM tblUnpivotSource UNION ALL SELECT ID, A, B, C, [Key 3] AS key_field FROM tblUnpivotSource; … returns this recordset (using your sample table values as tblUnpivotSource) … ID A B … Read more
How to get random record from MS Access database
To get different random record you can use, which would require a ID field in your table SELECT TOP 1 questionID FROM questions ORDER BY Rnd(-(100000*questionID)*Time()) A negative value passed as parameter to the Rnd-function will deliver the first random value from the generator using this parameter as start value. (A kind of defined randomize). … Read more
Microsoft Access condense multiple lines in a table
Typically you have to write a function that will allow you to create a concatenated list. Here’s what I’ve used:. Public Function GetList(SQL As String _ , Optional ColumnDelimeter As String = “, ” _ , Optional RowDelimeter As String = vbCrLf) As String ‘PURPOSE: to return a combined string from the passed query ‘ARGS: … Read more