I use a simple Append query to append data from Table A to Table B (which already has 1 line of data in the table). Strangely, a chunk of data in Table A between records 225 and 400 always get inserted in between record 24 and 25 in Table B.

So, for example, if I have 225 records in Table A, then the result in Table B is in sequential order from record 1 to 225.

However, if I have 1000 records in Table A, after appending to Table B, instead of getting records from 1 to 1000 in sequential order, the result in Table B would be records 1 to 24, then 225 to 400, then 25 to 1000.

The sequence is messed up whenever there are more than 225 records in Table A. The chunk of data (records 225 to 400) is always inserted in between records 24 and 25 no matter how many records I have in Table A (as long as it's more than 225).

Could anyone explain why this is happening? I would appreciate any suggestions in fixing the problem. Thank you.

Are you talking about the sequence of an autonumber?

Or are you talking about some record number?

If you use an autonumber, there is no assurance they will be in order.

Can you sort them the way you want them after you append?

A little more info please.

I used a function (see below) to generate a record number for each record in Table A. The function is called by an update query to write the number to Field1 in Table A. The sequence in Table A shows correct result after running the update query. It's only after running the Append query to copy data in Table A to Table B, a chunk of data gets shifted in Table B and it's always the same chunk (records 255 to 400).



If the problem is caused by the generated number, I'm curious why it's always records 255 to 400 .

I'll kind of run into problem if I sort it after Append because I need to keep first line of data already in Table B in the first row (before and after the Append), since its Field1 is also a number but not 1, it'll get mixed into appended Table A data if I sort it after Append.

I wonder if you could just change to an autonumber instead of generating your own?

OR, you might be able to do some custom sorts on a form when you display the data.

Good luck!

As Milton has already pointed out, Tables do NOT have any inherent ordering. Tables are more like buckets of water than bookshelves. You can not say that any given drop of water is the "first", or the "last". They are free to slosh around.

The order in which a sequence of records happens to appear at any given time is purely dependent on how they are sorted at that moment. What you are seeing in this post-append table is the result of some sort applied to it.

In other words, expecting that initial, sole record to remain in position one in the table is unrealistic. It's not going to happen.

Without seeing your data and the append query you use to add it, it is impossible to do more than guess as to why you SEE the sort order you see. But I'll bet money that your records are actually going into the table in some order other than the one you think it is. And that is why you see a different sort order.

You need to have some OTHER field in the table on which you can sort as needed. Make the initial record the one with the lowest sort order value in the sort field so you can apply a sort to the table that DISPLAYS it first.

Thank you for all your feedbacks.

I wanted to use autonumber, but couldn't get it to start every time on 1, hence the generated number.

I'll review the whole process and think of another way to order and combine the data.

"I wanted to use autonumber, but couldn't get it to start every time on 1"

Unfortunately, that's another common misconception--trying to use AutoNumbers for ANYTHING other than Primary Keys in a table. They simply don't behave in ways that make any other use feasible. They are not even guaranteed to be generated in increasing order--even though they do that "most" of the time.

Again, there is NO inherent order to data in a table. If you need to be able to refer to records in some sort of sequential fashion, you can create a query with your data, applying a sort order to it that reflects your requirement. You haven't indicated to us what that sort order would be, so I can only guess it might be something like transaction dates, from oldest to newest, or names of organizations, in ascending order from "AAA Architects" to "Zwicker Chairs".

Because you can use that same query ANYTIME to place the records in the desired order, using whatever sorting criteria you start out with, adding a new field to the table doesn't add anything useful at all.

Now, all of this assumes that you can pre-sort the values in some fashion. Obviously, you think you can, or you wouldn't even be attempting this. Therefore, whatever means you'd use in the first instance to order the data prior to insertion into the table is the same method you can use in your query at any time to sort it the same way.

All of that being said, I am not sure we've fully heard WHY you want to do this and how the data is delivered to you. It may well be, for example, that there IS no way to sort the data, and that this whole exercise is invalid. In any case, if you'd like to bounce some more ideas around, please post back with more details about WHY you want to do this. Maybe there is a more practical suggestion or two out there.

Thanks for your detailed feedback. Please find below more details about the process:

Table A is the result of a Make Table query of two Tables X & Y linked by OrderNumber (Long Integer) sorted in ascending order in both Tables X & Y. Other fields in Table A: RefID (text) and Amount (#.00), not in any sorting orders.

Actually, at this point, the sorting sequence is not a concern. Whatever sequence the data is in, it needs to be exported to a text file with a header and a row number for each record. The row number for the data must always start from 1. The sequence of the row number is what matters. The export file is the reason I'm combining Table A and Table B which I'll explain further.

When I created Table A with the Make Table query, I had an extra column with default 0 at the beginning of Table A for storing the row number. Then I use an update query to call the IncreaseCount function to add the row number. Once the row number is added, this is the sequence that must be kept. So far, row number of Table A "looks" as though it's in the correct sequence. Exporting Table A to a text file at this stage also shows the correct row number sequence (regardless of the number of records).

Then I created the header row in a blank Table B. Table B has the same number of columns as Table A. However, for the header row:
in place of RowNumber, it's a fixed TransID (Long);
in place of RefID, it's a fixed TransRef (text);
in place of OrderNumber, it's the Count of OrderNumber;
in place of Amount, it's the Sum of Amount.
I used the Append Query to add the Count of OrderNumber and Sum of Amount to Table B.

Next, I used Append Query to add records in Table A to Table B, then exported the resultant Table B file to a text file.

I believe the last Append Query action is the point where the row sequence is messed up whenever there are more than 255 records. If the row sequence in the correct order before the Append Query is just by luck, then I should think of another way to create the row number. Alternatively, I'm wondering if I should somehow create a text file with header, then write Table A data directly to the text file intead of doing Append then export.

Any further suggestions/ideas would be much appreciated. Thank you for your time and effort to assist.

This post has been edited by learner: Apr 5 2012, 02:05 AM

Just a quick thought, but have you tried creating a query from the results of Table A, and simply renaming the fields in the query to the required header row names. You should then be able to export the query (with the headers) and as long as the sort is on the generated number field, it should give the result I think you are looking for. It somewhat depends on what kind of text file you are exporting to, and how you are exporting to that file however. If you are using outputto, you can't include the field names but with transfertext and transferspreadsheet (csv only I think) you can.

Sorry for jumping in.

Katty

Sorry, we went from too little information to a bit of an overload here. I have to go off to work so I'm not going to have time to really sort out the question (pun intended). That said.

"Whatever sequence the data is in, it needs to be exported to a text file with a header and a row number for each record. The row number for the data must always start from 1."

The important point here is that you need to EXPORT a recordset with a field that is numbered from 1 to the max of the recordset. Otherwise, there is no internal sorting intended or required. Correct?

I think we can do what you need with a UNION query, rather than a bunch of processing steps and temp tables.

You need a Header row that contains the aggregated values and the transmittal information. That will be one element in the UNION query.
Then you need the rest of the data, unaggregated, in the other element in the union query.

To apply the "Row Number" element to the records in this query, you can use one of the commonly used techniques for that.
Here's one example. Here's another.
Of course, because we'd be using a UNION query, the actual implementation might be a little different.

If you'd like to share some sample data, we could probably help create the actual query you need. Uploaded databases need to be compressed into ZIP files.

Thanks.

You have indicated that you are using Office 97. It's too old for me.

I can't provide an mdb in that format.

However, here are two queries that ought to do the job. You'll have to adjust the field names I used to those you actually have, if they are different.
I made one assumptions: Order Number is unique across all records in the order table. If that's not the case, you will need more complex approach.

Ranking Query:


UNION Query for Export:



You should be able to export the union query to Excel with no further manipulations.

Also, you haven't mentioned any date ranges yet. If that's a factor, of course, you'll have to add in date parameters.

Thank you very much, George, for all your time and effort. Initially, I was just looking for for ideas here, didn't mean to take up so much of your time. Without troubling you any further, I'll give it a pass on your suggestion to upload sample data, you've been very helpful and have given me valuable feedbacks here to keep me going, I'll try out your suggestion with the UNION query later.

FYI, "The important point here is that you need to EXPORT a recordset with a field that is numbered from 1 to the max of the recordset. Otherwise, there is no internal sorting intended or required. Correct?" - Yes.

Your assumption that "Order Number is unique across all records in the order table." is correct. Also, date range is not a factor here.

Thank you once again.

Thanks, Katty, for your comments. Trying to rename the field names which change depending on the no. of records and sum of amount by code seems more complex to me. I intend to try out George's UNION query suggestion first. Anyway, I'll keep your suggestion in mind for future reference. Thanks.

Good luck with your project and please do post back as you make progress.

Most of the "regulars" are here because we all started out in roughly the same place with our knowledge and experience. We've received a lot of help ourselves over time and are happy to give back to as a way to say "thank you"

Continued success with your project.