If you create the table first, empty but with all fields including your auto-number defined, then use an Append type query to populate your table second, the auto-number will populate correctly in that process. But by making the table first without the field and then going back to add the auto-number field, you force Access to go back and revisit EVERY RECORD - because by adding the new field as a second step, you force it to rewrite every already-existing record to have a populated auto-number. Access tends to "squeeze" records together tightly with no slack space between records. Adding that auto-number, every record just got one field larger so has to be copied and repopulated. Plus, Access has to go back and delete the now-obsolete original record. That combination has to be slower than molasses in the Arctic Circle.
But I'm more interested in the reason for this. The "first query" extracts data from somewhere. Is that a table? Because it seems to me that, rather than building a "helper table", you should be running the "second query" to directly compute your counts from the original source, which would be the right answer. But you can't really do that because of structural issues in the original database that is "too big to fail." I might point out that if it is this hard to gather what you wanted, it may already be failing. Failing to support the company's needs.
I recall that you are working with a non-Access back-end from a different machine. You are grabbing data from an ODBC source, Visual Foxpro. By making a local table first, you make the "data fetch" portion of subsequent operations local rather than networked. That can certainly make a profound difference in speed so, for efficiency, this might still be your best bet.
So you have created this "helper table." You run that next query that requires a suitable auto-number field. What do you do with the "helper table" when you are done with it? Particularly if it involved 1.7 million records, that is a LOT of database bloat if you are just going to delete it. Looking back through your prior threads, this is something you want to run every day. You are going to have such a bad bloat problem very quickly. Do you run frequent Compact & Repair operations? Or do you start fresh each day by copying your DB to a work area and only using the "working copy," not the "master copy"?
This thread also seems to be a near-direct continuation of another thread:
Good Afternoon Good People, I have converted a query to a table. I need to add a unique ID to each row Is there a simple way to add an auto number index field, I thought that it may ask me on creation but it did not.
www.access-programmers.co.uk
When the question changes, there are reasons for starting a new thread and that is perfectly OK. But when the question hasn't changed, you COULD continue in the same thread. I know you are struggling here with bulky data and the need to extract statistics from a complicated source. You are also fighting an environment that (based on your comments) is new to you.
I'm not trying to give you a hard time. I'm just commenting that if the topic hasn't changed, splitting the question now makes us look in more than one place to gain any sense of continuity. It's a fine line to cross and you are DEFINITELY not the first person to have faced the problem of when to start a new thread. Call this a friendly side comment that sometimes continuity is more important and sometimes a change IS needed. The trick is to recognize that "New/Continue line" when you approach it.
