Strange Problem

[crowegreg]

I’ve created a DB for a customer. I’ve executed a particular form at least 100 times on my computer and it has always executed properly. On my customer’s computer, it will not execute properly all the time. I’ve been unable to determine what is causing the problem.

Here are the details:
Table Name: tbl Export IPG Orders
Fields: Group 1 – text
Group 2 – text
Order Number – long integer
Group Type – long integer

The Group Type is either 1, 2, or 3.

Here my statement:
DoCmd.RunSQL "INSERT INTO [tbl Export IPG Orders - 2nd] ( [Group 1], [Group 2], [Order Number], [Group Type] ) SELECT [tbl Export IPG Orders].[Group 1], [tbl Export IPG Orders].[Group 2], [tbl Export IPG Orders].[Order Number], [tbl Export IPG Orders].[Group Type] FROM [tbl Export IPG Orders] ORDER BY [tbl Export IPG Orders].[Order Number], [tbl Export IPG Orders].[Group Type]"

What this is supposed to do is insert all 4 fields into a table using the Order Number as the first ascending sort and the Group Type as the second ascending sort

Here’s an example of what the table should look like:
Group1, group2, 123456,1
Group1, group2, 123456,2
Group1, group2, 123456,3
Group1, group2, 123457,1
Group1, group2, 123457,2
Group1, group2, 123457,3

My problem is sometimes the sort does not work properly. Here’s an example
Group1, group2, 123544,2


Group1, group2, 123544,3
Group1, group2, 123545,1
Group1, group2, 123545,2
Group1, group2, 123545,3
Group1, group2, 123456,1
Group1, group2, 123456,2
Group1, group2, 123456,3


I can’t figure out what is happening. It’s not consistent. Thanks for your assistance!!

[RuralGuy]

Records in Tables in Access are really just a bucket of records. You give them order with the data and a query with an OrderBy clause. You seem to feel that Access should keep track of *how* the records were added which is not the case and can not be depended on.

[crowegreg]

So even though the insert statement is sorting correctly, when the records are placed within the table, I cannot be assured of them being in the same order?

[RuralGuy]

That is correct! If order is important then use a query to view the table instead of the table directly. A query is just a string of text and some strategy and take up very little room in the db and should not be avoided.

[crowegreg]

What I'm doing is create an export file. So my logic in trouble shooting was write it to a table, then export the table. Are saying that I should use the query in the export process rather than the table?

[RuralGuy]

Exactly. I see no reason for the intermediate table in this case unless you need the historical information. Just use the 2nd part of your INSERT query as the source of your export.

[crowegreg]

Thanks!! I'll make the change that you've recommended. Any explanation as to why it's worked properly on my machine all the time?

[RuralGuy]

Coincidence? Just the luck of the draw. Often the records can be returned in PrimaryKey sequence but it is not guaranteed.

[crowegreg]

I made the change on my machine, and it worked properly. I'll update the customer tonight, and test it then. Thanks again!!

[RuralGuy]

I'm sure it will work better for you. Are you ready to use the Thread Tools at the top of the thread to mark this thread as Solved?

[crowegreg]

Yes, thanks for reminding me.