Delete Table1.* From Table1 Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid … Read more
I would check the “End of File” flag: If temp_rst1.EOF Or temp_rst2.EOF Then MsgBox “null”
You need a function to do the concatenation. Microsoft Access condense multiple lines in a table Example using your data: Select T.ColumnA , GetList(“Select ColumnB From Table1 As T1 Where T1.ColumnA = ” & [T].[ColumnA],””,”, “) AS ColumnBItems From Table1 AS T Group By T.ColumnA;
Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join. SELECT * FROM AA LEFT JOIN BB ON AA.C_ID = BB.C_ID UNION SELECT * FROM AA RIGHT JOIN BB ON AA.C_ID = … Read more
I’m sure you’ll get a ton of “don’t do this” answers, and I must say, there is good reason. This isn’t an ideal solution…. That being said, I’ve gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn’t talk around it. Here are a … Read more
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
I decided to add a condition to the Where Clause that always evaluates true but allows the coder to find your comment. Select … From … Where …. And “Comment: FYI, Access doesn’t support normal comments!”<>”” The last line always evaluates to true so it doesn’t affect the data returned but allows you to leave … Read more
Try this SELECT Count(*) AS N FROM (SELECT DISTINCT Name FROM table1) AS T; Read this for more info.
It is possible to do a little with VBA. For example, here is a start on creating script for a database with local tables. Dim db As Database Dim tdf As TableDef Dim fld As DAO.Field Dim ndx As DAO.Index Dim strSQL As String Dim strFlds As String Dim strCn As String Dim fs, f … Read more
This should get you started: Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDB() For Each qdf In db.QueryDefs Debug.Print qdf.SQL Next qdf Set qdf = Nothing Set db = Nothing You can use the File System Object or the built-in VBA File I/O features to write the SQL out to a … Read more