Logo

MS Access FAQ - Queries

 Logo

Table of Contents (8 Questions)

Bullet Query Too Complex
Bullet The Confirmation Dialog
Bullet Sequence numbers on the fly (counter emulation)
Bullet Non matching records
Bullet Finding Duplicate records
Bullet Highest Of
Bullet Joined end-to-end
Bullet Delete Duplicate Records

Q When I attempt to generate a certain report, I get the message "Query Too Complex" but when I run the query the report is based on, the query runs OK. What does this mean?
Back to Top
A Access 2.0: Access has just exposed one of it's limitations, it's 16 bit and therefore stores data into 64K segments, you've just filled up one of those segments. Consider making your query a Make-table query or an append query to a temp table then base the report on the table, this will split the processing up a bit and as the query and report are no longer one operation, Access will no longer try to store both into the same 64K segment.

Access 97: I've not yet seen this message in Access 97

Updated: 21/12/1997 - Trevor Best

Back to Top
Q How do I stop the message box warnings issued whenever I run an action query with DoCmd RunSQL or DoCmd OpenQuery or from a macro?
Back to Top
A In a macro, SetWarnings: Off
In Code, "DoCmd SetWarnings False".

It's worth noting that you should turn the warnings back on afterwards as this applies to just about everything, even saving a form in design mode so it won't let you abandon it if you wanted to.

Another thing in Code, you can use the .Execute method on a QueryDef object or DataBase Object, this has the advantages of being able to run inside a transaction and so execute faster, "DoCmd" starts another session of Jet (the database engine in Access) so will execute outside of the current default workspace, therefore running independantly of any transactions and you can't rollback from them.

Updated: 21/07/1996 - Trevor Best

Back to Top
Q How can I generate a sequence of numbers (like record numbers) in a query.
Back to Top
A In a module:
(declarations)
Option Explicit
Dim mlngCounter As Long

Function ResetCounter() 
  mlngCounter = 0
End Function

Function GetNextCounter(pvar As Variant) As Long
  mlngCounter = mlngCounter + 1
  GetNextCounter = mlngCounter
End Function

In the Query:
Select [feilds...], ResetCounter(), GetNextCounter([a valid column]) From table....

How this works:
ResetCounter() takes no parameters so Access (being as efficient as ever:) only calls the function once before returning any rows as it thinks the return value will never change, hence the counter only gets reset once.
GetNextCounter() must be sent a valid column as a parameter, Access will realise that it must call this function for every row as each value sent can be different, so it increases the counter on each row.

Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.

Updated: 04/11/1996 - Trevor Best

Back to Top
Q How do I find all the records that are in one table but do not have matches in another table
Back to Top
A Use the "Find Unmatched" query wizard

Updated: 11/08/1996 - Trevor Best

Back to Top
Q How can I find duplicate records in a table
Back to Top
A Use the "Find Duplicates" query wizard

Updated: 11/08/1996 - Trevor Best

Back to Top
Q I want to show the highest paid employee per department, if I use a totals query I can get the highest salary using the Max() function yet I can't get the corresponding (lucky) employee who gets that wage
Back to Top
A You can show this quite easily in a report, just take the grouping off the query (so it's no longer a totals query), now in the report, Sort on Department with a group footer and Sort on Salary Ascending. Now make the detail section 0 cm in height (for you Americans out there, that's 0 inches :-) and simply place the data fields into the group footer.

If you really need this information in the query itself then it gets rather messy, I can of two ways:

1) Use Max() to obtain the high salary, the do a DLookup() on the table using DepartmentID and Salary as criteria to obtain the name.

2) Use Max() to obtain the high salary, Create a new query that joins the original onto the base table, join them on DepartmentID And Salary.

This may prove futile as there may be more than one person on that salary.

Updated: 16/11/1997 - Trevor Best

Back to Top
Q Is there a way to output two tables (or queries) end-to-end, the two tables have the same number of fields.
Back to Top
A Use a Union Query, e.g.

Select field1, field2 from Table1 union select field1, field2 from Table2;

Another way, if you're sure the fields are the same in each, you can use:

Table Table1 Union Table Table2;

Table1 and Table2 can also be queries.

Please note that union queries are not updatable.

I once had five very complex queries to output like this, the union query gave me the "Query Too Complex" error message, I'm not surprised with these ones, I'm suprised they didn't bomb out individually, anyhow I ended up making 5 append queries and one dleete query and a temp table, I first delete the data in the temp table, then ran all 5 append queries so they appended to the temp table.

Updated: 16/11/1997 - Trevor Best

Back to Top
Q How can I delete duplicates from a table
Back to Top
A Imagine you've just used the Find Duplicates query wiard on tblSupplier, and used the field "Supplier" as the dupe key, run the following sub.

Sub GoodRiddence()
  Dim db As Database, rst As Recordset
  Dim strDupName As String, strSaveName As String

  Set db = dbengine(0)(0)
  Set rst = db.openrecordset("Find Duplicates For tblSupplier")
  rst.MoveFirst
  do until rst.eof
    strDupName = rst!Supplier
    if strDupName = strSaveName Then
      rst.delete
    end if
    strSaveName = rst!SupplierName
    rst.MoveNext
  loop
  rst.close
  set rst=nothing
  set db=nothing
End Sub


Now prevent this from happening again with the following query:
create unique index idxSupplier on tblSupplier (Supplier)

Updated: 09/05/1998 - Trevor Best

Back to Top