Append not appending at newest record

[Dnation]

Hi,

I have an issue with the append query in Access97. My issue is sometimes my append query wont append (say 100 rows) to the bottom of the table, but instead append it in the middle of the recordset table which totally ruins the format of my table. I cannot any sort feature as Im using this table to build a dimension in OLAP TM1.

This is what Im trying to acheive

table 1 has 2 columns;
Column 1 - country (eg USA, Australia)
Column 2 - city (eg. NY, LA, Sydney)

Table 2 has 2 columns in this format;
Column 1 - N ("N" denoting lowest level in OLAP dimension, "C" denotes consolidation, Null denotes a child of the consolidation)
Column 2 - data

eg of wanted table format
N Sydney
N LA
N NY
C USA
_ LA (_ denotes a null in column 1)
_ NY
C Australia
_ Sydney
C "ALL Countries"
_ USA
_ Australia

_______
This is a detailed process;
- Use a make table qry from data in table (1) to make new table (2)
- Use a loop code based on table 1 to append consolidations of my data into table 2
- Append again via append qry using the same table 1 to add into table 2.

I need this to append in order for this to work..
_______

Is there a bug in Access 97 that doesnt automatically append at the bottom of the table.. This happens most times.. I have done test after test and there are times where it does append at the bottom, but others where it doesnt. Whats the go here?

Help please?

many thanks in advance,
Dave

 

[dk]

Is there a bug in Access 97 that doesnt automatically append at the bottom of the table.. This happens most times.. I have done test after test and there are times where it does append at the bottom, but others where it doesnt. Whats the go here?

Help please?

many thanks in advance,
Dave

This isn't a bug. Tables don't store data in any particular order (in any RDBMS) and you shouldn't rely on the last appended record appearing at the bottom of the table. I don't understand the format of your example table format - can you explain the logic of it and maybe someone can help you devise an alternative.

Regards,
Dan

 

[Dnation]

ok assume i have this 2 column table..

Country City
USA LA
USA NY
Aust Sydney

I need to have this format
N Sydney
N LA
N NY
C USA
_ LA (_ denotes a null in column 1)
_ NY
C Aust
_ Sydney
C "ALL Countries"
_ USA
_ Aust

The way im doing this is;

Make table 2 with query so I have;
Expr1 Expr2
N Sydney
N LA
N NY

then I have a code looking through table 1
generating a recordset of all unique countries, in this case USA and Aust
It will then loop append the 1st unique country to table 2 with a C for Expr1
ie

Expr1 Expr2
N Sydney
N LA
N NY
C USA

Then within that loop it's looking through table 1 and when it finds USA, it will append the city with a null in column 1

ie.
N Sydney
N LA
N NY
C USA
_____ NY
_____ LA

Then the code will go to next unique country and append their cities
ie.

N Sydney
N LA
N NY
C USA
_____ NY
_____ LA
C Aust
_____ Sydney

It will cycle thru table 1 until EOF for unique countries..

Step 3 is where it will append using query all unique countries

ie
N Sydney
N LA
N NY
C USA
_____ NY
_____ LA
C Aust
_____ Sydney
C ALL COUNTRIES
_____ USA
_____ Aust


I need it in this exact format, otherwise my OLAP TM1 dimension builder wont consolidate my data properly for analysis.
The easiest way is to use excel, but having this dimension exceeds excels row limitation of 65000+...

I hope this helps with what I need??

theres gotta be a way to append to tables at the bottom..

 

[Dnation]

I've figured out what its doing.

When appending, its ordering (ascending) the 2 appends. I havent specified it to order but its doing it as a default.
Is there a way to just append at the bottom (new record) without ordering the new append data?

 

[mdmilner]

Realize this is an old post, but:

One approach is to set something like an autonumber field, then append your data. In a query that you use to view/export/work with the table...set it to sort by the autonumber field.

Mike