Logo

MS Access FAQ - Tables

 Logo

Table of Contents (4 Questions)

Bullet Resetting Counter Fields
Bullet Overcomming the 255 Field Limit
Bullet Joined end-to-end
Bullet Delete Duplicate Records

Q How do I reset counter fields. During testing I've used a lot of dummy values, but I want my counters to be reset when I deliver my application.
Back to Top
A All Versions: After deleting your dummy data, compact the database, this will reset counter fields to the next sequential value, e.g. for a table with no records this will be zero but if the table still contains data then it will be 1 + the highest value in the table.

Updated: 21/12/1997 - Trevor Best

Back to Top
Q I have to import a table from a legacy system, it has 300 fields, Access only supports 255, how can I do this?
Back to Top
A You can create two tables with a common key and split the data between them. You can create a 1:1 relationship between the two tables as well.

I've heard that Access won't allow more than 255 output fields in a query either so to overcome this when printing a report, get as many as you can into the main query then you can use either sub-reports or unbound fields with DLookups in each of them.

Updated: 20/08/1996 - 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