How do I STOP access sorting my data?

D

dave.mclachlan

Hi there gurus,

I have a link table using a named ranged from an excel sheet. This
sheet has data in a specific order (not sorted).

Ive created a query that mines other data in my database based on the
data in the link table, however, the query sorts the output even though
I have no sort fields.

Row for row I want data that lines up with my linked table as I'd like
to use 'Import external data' in excel to retrieve the data next to the
original.

am not sure ive explained this very well, i esentially just want the
data in the same order as the data in my linked table...

I know access has problems with sort orders in linked tables, so I
tried replicating the data in a simple query (which worked, the order
is unchanged) and use that as the source for my data instead of the
linked table.... the result was just the same!

Am flummuxed...

any help would be appreciated... below is the SQL if it helps
anybody...
FY07BasedataGIDs is my linked table.
R12m Flows Data is my data id like to align.

SELECT FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, Sum([R12m Flows Data].[Fiscal
R12mClosing Balance]) AS [SumOfFiscal R12mClosing Balance]

FROM ((FY07BasedataGIDs LEFT JOIN ClosedLocations ON
FY07BasedataGIDs.Account = ClosedLocations.Customer_GId) LEFT JOIN
[R12m Flows Data] ON FY07BasedataGIDs.Account = [R12m Flows
Data].Account) LEFT JOIN FY07BasedataSoldto ON [R12m Flows Data].[Sold
to Party] = FY07BasedataSoldto.Account

GROUP BY FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, [R12m Flows Data].[Generic Material]

HAVING ((([R12m Flows Data].[Generic Material])="1"));
 
D

Douglas J. Steele

The only way you can control the order of rows is to have an ORDER BY
clause.

There's no way to control the order in which the rows are returned
otherwise.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi there gurus,

I have a link table using a named ranged from an excel sheet. This
sheet has data in a specific order (not sorted).

Ive created a query that mines other data in my database based on the
data in the link table, however, the query sorts the output even though
I have no sort fields.

Row for row I want data that lines up with my linked table as I'd like
to use 'Import external data' in excel to retrieve the data next to the
original.

am not sure ive explained this very well, i esentially just want the
data in the same order as the data in my linked table...

I know access has problems with sort orders in linked tables, so I
tried replicating the data in a simple query (which worked, the order
is unchanged) and use that as the source for my data instead of the
linked table.... the result was just the same!

Am flummuxed...

any help would be appreciated... below is the SQL if it helps
anybody...
FY07BasedataGIDs is my linked table.
R12m Flows Data is my data id like to align.

SELECT FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, Sum([R12m Flows Data].[Fiscal
R12mClosing Balance]) AS [SumOfFiscal R12mClosing Balance]

FROM ((FY07BasedataGIDs LEFT JOIN ClosedLocations ON
FY07BasedataGIDs.Account = ClosedLocations.Customer_GId) LEFT JOIN
[R12m Flows Data] ON FY07BasedataGIDs.Account = [R12m Flows
Data].Account) LEFT JOIN FY07BasedataSoldto ON [R12m Flows Data].[Sold
to Party] = FY07BasedataSoldto.Account

GROUP BY FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, [R12m Flows Data].[Generic Material]

HAVING ((([R12m Flows Data].[Generic Material])="1"));
Click to expand...
 
D

dave.mclachlan

Thanks for your comments Douglas,

This is my point though. I dont want to control the order or do any
sorting of any kind. I want the data output in the same order that the
query is fed.


The only way you can control the order of rows is to have an ORDER BY
clause.

There's no way to control the order in which the rows are returned
otherwise.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi there gurus,

I have a link table using a named ranged from an excel sheet. This
sheet has data in a specific order (not sorted).

Ive created a query that mines other data in my database based on the
data in the link table, however, the query sorts the output even though
I have no sort fields.

Row for row I want data that lines up with my linked table as I'd like
to use 'Import external data' in excel to retrieve the data next to the
original.

am not sure ive explained this very well, i esentially just want the
data in the same order as the data in my linked table...

I know access has problems with sort orders in linked tables, so I
tried replicating the data in a simple query (which worked, the order
is unchanged) and use that as the source for my data instead of the
linked table.... the result was just the same!

Am flummuxed...

any help would be appreciated... below is the SQL if it helps
anybody...
FY07BasedataGIDs is my linked table.
R12m Flows Data is my data id like to align.

SELECT FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, Sum([R12m Flows Data].[Fiscal
R12mClosing Balance]) AS [SumOfFiscal R12mClosing Balance]

FROM ((FY07BasedataGIDs LEFT JOIN ClosedLocations ON
FY07BasedataGIDs.Account = ClosedLocations.Customer_GId) LEFT JOIN
[R12m Flows Data] ON FY07BasedataGIDs.Account = [R12m Flows
Data].Account) LEFT JOIN FY07BasedataSoldto ON [R12m Flows Data].[Sold
to Party] = FY07BasedataSoldto.Account

GROUP BY FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, [R12m Flows Data].[Generic Material]

HAVING ((([R12m Flows Data].[Generic Material])="1"));
Click to expand...
Click to expand...
 
J

John Spencer

The point is that there is no way to get an order unless you impose an
order. For instance, your group by clause is imposing an order as Access
will impose an order so it can do the grouping efficiently. Just the
simple joining of the tables may cause Access to impose some kind of order
on the records.

If you want to get the return from the query in the row order of the Excel
table, you probably need to add a new column to the Excel table that
contains a sequence that will put the rows in that order. You can then use
that "row number" in your query.

Thanks for your comments Douglas,

This is my point though. I dont want to control the order or do any
sorting of any kind. I want the data output in the same order that the
query is fed.


The only way you can control the order of rows is to have an ORDER BY
clause.

There's no way to control the order in which the rows are returned
otherwise.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi there gurus,

I have a link table using a named ranged from an excel sheet. This
sheet has data in a specific order (not sorted).

Ive created a query that mines other data in my database based on the
data in the link table, however, the query sorts the output even though
I have no sort fields.

Row for row I want data that lines up with my linked table as I'd like
to use 'Import external data' in excel to retrieve the data next to the
original.

am not sure ive explained this very well, i esentially just want the
data in the same order as the data in my linked table...

I know access has problems with sort orders in linked tables, so I
tried replicating the data in a simple query (which worked, the order
is unchanged) and use that as the source for my data instead of the
linked table.... the result was just the same!

Am flummuxed...

any help would be appreciated... below is the SQL if it helps
anybody...
FY07BasedataGIDs is my linked table.
R12m Flows Data is my data id like to align.

SELECT FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, Sum([R12m Flows Data].[Fiscal
R12mClosing Balance]) AS [SumOfFiscal R12mClosing Balance]

FROM ((FY07BasedataGIDs LEFT JOIN ClosedLocations ON
FY07BasedataGIDs.Account = ClosedLocations.Customer_GId) LEFT JOIN
[R12m Flows Data] ON FY07BasedataGIDs.Account = [R12m Flows
Data].Account) LEFT JOIN FY07BasedataSoldto ON [R12m Flows Data].[Sold
to Party] = FY07BasedataSoldto.Account

GROUP BY FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, [R12m Flows Data].[Generic Material]

HAVING ((([R12m Flows Data].[Generic Material])="1"));
Click to expand...
Click to expand...
Click to expand...
 
D

dave.mclachlan

Thanks for all your insite chaps.

John, this is precicely what I had resorted to.

thnx


John said:
The point is that there is no way to get an order unless you impose an
order. For instance, your group by clause is imposing an order as Access
will impose an order so it can do the grouping efficiently. Just the
simple joining of the tables may cause Access to impose some kind of order
on the records.

If you want to get the return from the query in the row order of the Excel
table, you probably need to add a new column to the Excel table that
contains a sequence that will put the rows in that order. You can then use
that "row number" in your query.

Thanks for your comments Douglas,

This is my point though. I dont want to control the order or do any
sorting of any kind. I want the data output in the same order that the
query is fed.


The only way you can control the order of rows is to have an ORDER BY
clause.

There's no way to control the order in which the rows are returned
otherwise.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi there gurus,

I have a link table using a named ranged from an excel sheet. This
sheet has data in a specific order (not sorted).

Ive created a query that mines other data in my database based on the
data in the link table, however, the query sorts the output even though
I have no sort fields.

Row for row I want data that lines up with my linked table as I'd like
to use 'Import external data' in excel to retrieve the data next to the
original.

am not sure ive explained this very well, i esentially just want the
data in the same order as the data in my linked table...

I know access has problems with sort orders in linked tables, so I
tried replicating the data in a simple query (which worked, the order
is unchanged) and use that as the source for my data instead of the
linked table.... the result was just the same!

Am flummuxed...

any help would be appreciated... below is the SQL if it helps
anybody...
FY07BasedataGIDs is my linked table.
R12m Flows Data is my data id like to align.

SELECT FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, Sum([R12m Flows Data].[Fiscal
R12mClosing Balance]) AS [SumOfFiscal R12mClosing Balance]

FROM ((FY07BasedataGIDs LEFT JOIN ClosedLocations ON
FY07BasedataGIDs.Account = ClosedLocations.Customer_GId) LEFT JOIN
[R12m Flows Data] ON FY07BasedataGIDs.Account = [R12m Flows
Data].Account) LEFT JOIN FY07BasedataSoldto ON [R12m Flows Data].[Sold
to Party] = FY07BasedataSoldto.Account

GROUP BY FY07BasedataGIDs.Account, [R12m Flows Data].[Sold to Party],
[R12m Flows Data].[Sold to Party Name], FY07BasedataSoldto.[House and
Street Num], FY07BasedataSoldto.[City / Town],
FY07BasedataSoldto.[St/Pv/Co], FY07BasedataSoldto.[Post/Zip],
FY07BasedataSoldto.[Postcode Regions], FY07BasedataSoldto.[Primary
Exec], FY07BasedataSoldto.[Manager Position],
FY07BasedataSoldto.[Business Group], FY07BasedataSoldto.[CMS Parent],
FY07BasedataSoldto.[CMS Parent Name], FY07BasedataSoldto.[Reporting
Grandparent Name], ClosedLocations.Pending_Clos_Date,
ClosedLocations.Final_Closure, [R12m Flows Data].[Generic Material]

HAVING ((([R12m Flows Data].[Generic Material])="1"));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I filter data by blank cells in access 8
How do I stop a Pivot Table from Sorting my data (Excel 2007) 1
how do I amend data in linked access tables 2
How do I lookup values for mulitple columns in Access? 8
How do I refresh external data in Excel via linked table in Access 1
How do I rank data into deciles in Access? 2
How do I set up dependent data lists in Access 2003? 2
How do I move data in columns (XL) to rows in Access table 3

Top