Linked table changes order of records

[Ray C]

I have a linked table whereby I later execute a query like this:

SELECT * INTO MyLocalAccessTable FROM MyLinkedExcelTable

The strange thing is that sometimes when I look at my newly created table,
it isn't in the same order as the Excel worksheet it's linked to.

Any reasons why?
I need to keep it in the same order.

thanks

Ray

 

[pietlinden]

I have a linked table whereby I later execute a query like this:

SELECT * INTO MyLocalAccessTable FROM MyLinkedExcelTable

The strange thing is that sometimes when I look at my newly created table,
it isn't in the same order as the Excel worksheet it's linked to.

Any reasons why?
I need to keep it in the same order.

thanks

Ray

Tables are just like storage containers. You have zero control over
the order of records in a table. If you need to put them in a
specific order, use a query instead. Record order in databases is
pretty much meaningless. (At least it should be!)

 

[Larry Daugherty]

If the order is important, use a Query.

HTH

 

[Ray C]

I thought that's what I was doing.
The problem is that the data is not in any particular order (i.e.
alphabetical nor numeric). I simply need to take what's in the Excel
worksheet and import it into an Access table in the order "as is".

Ray

 

[Klatuu]

Records are not stored in a table in any specific order. You have to use a
query to retrieve the records in the order you want using the Sort row in the
query builder or the ORDER BY clause in an SQL statment.

There is no reason to have data in a table in a specific order.

 

[dan dungan]

Hi Ray,

I have a similar requirement. So I had to add a column in my excel
table to hold the sort order. I called it ItemNumber.

Then I use that in my query to put the data in the desired order.

Dan

 

[Ray C]

Maybe I'm not explaining myself clearly. I don't want the data in any
specific order. In other words, I don't want to do an ORDER BY or use a Sort
row.

I want Access to import the Excel worksheet as a table but in the same order
as the Excel worksheet itself. The worksheet has three text columns. The
second and third columns have data in every row. But the first column has
data only in every 10th row. What I need to do is fill in the blanks in
column 1, i.e. update rows 2 to 9 with what's in row 1, then populate rows 11
to 20 with what's in row 10, etc.

This is why I need to import the data in the same order as the excel
worksheet so that I can use queries to populate the missing values in column
1 in the resulting Access table.

The problem is that when I use a SELECT INTO, the first record in the Access
table has nothing in column 1 because it decided not to keep the order of the
worksheet. However, I'm guaranteed to have a value in row 1, column 1 in the
worksheet and every 10th row after that.

I hope this makes it a little clearer.

Thanks in advance,

Ray

 

[Jeanette Cunningham]

Ray,
it's amazing how difficult it can be to understand someone's question in
this newsgroup.

Type a value like xxxx into the first cell in the first column in the excel
spreadsheet.
After you've done the import, you can look for the xxxx and delete that
value for that field.



Jeanette Cunningham -- Melbourne Victoria Australia