shiraya
asked on
Table created by Make Table Query is not sorted. Why?
Hello!
In my DB I want to run a Make Table Query that only includes only records between certain dates. The date is sorting criteria as well.
When I view the query all the records are sorted according to their date but after running the query the resulting table is not sorted. All records from one day are next to each other but the days are not in the right order.
The resulting table may look like this:
All records from the 01/10/2003
All records from the 02/10/2003
All records from the 11/10/2003
All records from the 14/10/2003
All records from the 25/10/2003
All records from the 03/10/2003
All records from the 12/10/2003
...
Why is it like this and how can I change the query so that the records in the reulting table are in the right order?
Thanks in advance,
shiraya
In my DB I want to run a Make Table Query that only includes only records between certain dates. The date is sorting criteria as well.
When I view the query all the records are sorted according to their date but after running the query the resulting table is not sorted. All records from one day are next to each other but the days are not in the right order.
The resulting table may look like this:
All records from the 01/10/2003
All records from the 02/10/2003
All records from the 11/10/2003
All records from the 14/10/2003
All records from the 25/10/2003
All records from the 03/10/2003
All records from the 12/10/2003
...
Why is it like this and how can I change the query so that the records in the reulting table are in the right order?
Thanks in advance,
shiraya
ASKER
Thanks for the quick answer, but it doesn't help me.
The QUERY is sorted, but the resulting TABLE is not.
Regards,
shiraya
The QUERY is sorted, but the resulting TABLE is not.
Regards,
shiraya
In your make table query add a new field with alias SortField, where:
SortField:Format([DateFiel
include this field as first field and sort it as you like.
Mike
SortField:Format([DateFiel
include this field as first field and sort it as you like.
Mike
Your other choice is to make a select query on top of your table and using above alias field force it to sort. You can check off this field if you don't want it to be displayed.
Mike
Mike
I'm sure that what ryancys says will work.
This is just a general word of caution.
Access tables have no inherent 'order' unless you apply an ordering rule.
You can apply such a rule by creating a primary key, or by sorting records when you view them, or by viewing records through a query which sorts them (and maybe other ways I can't think of at the moment).
But unless you do apply an explicit sort sequence you cannot rely on any ordering of records when you process them.
When you use a make-table query the resulting table has no ordering applied to it. If you modified a record in the new table the record order could change.
Pete
This is just a general word of caution.
Access tables have no inherent 'order' unless you apply an ordering rule.
You can apply such a rule by creating a primary key, or by sorting records when you view them, or by viewing records through a query which sorts them (and maybe other ways I can't think of at the moment).
But unless you do apply an explicit sort sequence you cannot rely on any ordering of records when you process them.
When you use a make-table query the resulting table has no ordering applied to it. If you modified a record in the new table the record order could change.
Pete
ASKER
Hi Mike!
Didn't work. The resulting table looked like this (there are only records for October):
The resulting table isn't sorted.
Any other ideas?
I am using Access 2000 on a NT machine.
shiraya
Didn't work. The resulting table looked like this (there are only records for October):
The resulting table isn't sorted.
Any other ideas?
I am using Access 2000 on a NT machine.
shiraya
How about the 2nd solution. If you want to see it sorted, make a select query on top of it. Or make a form in datasheet view. If your data field string not date/time, you need to use SortField:Format([DateFiel
Mike
Mike
ASKER
Hi Pete!
I know that tables in general are not sorted. My idea however was that if you create a "Select Query" with the date as sorting criteria and change it into a "Make Table Query" and run this query that Access takes the first record in the query and and use it as the first record in the resulting table. 2nd record in query --> 2nd record in table .... But somehow it doesn't work like this.
In the resulting table all records from one day (say 07/10/2003) are clustered together, but the different days ar not in the right order.
eg.
06/10/2003
07/10/2003
20/10/2003
12/10/2003
...
Any other ideas?
shiraya
I know that tables in general are not sorted. My idea however was that if you create a "Select Query" with the date as sorting criteria and change it into a "Make Table Query" and run this query that Access takes the first record in the query and and use it as the first record in the resulting table. 2nd record in query --> 2nd record in table .... But somehow it doesn't work like this.
In the resulting table all records from one day (say 07/10/2003) are clustered together, but the different days ar not in the right order.
eg.
06/10/2003
07/10/2003
20/10/2003
12/10/2003
...
Any other ideas?
shiraya
Hi shiraya,
I have a solution for you. Add an index to that field. You may have to posision it as first field (in the index interface).
I have a solution for you. Add an index to that field. You may have to posision it as first field (in the index interface).
Maybe you could post your statement so that we see what you intend to do. In my eyes, you want to combine a SELECT with a CREATE TABLE or something.
The query is easy and does what you want:
SELECT date, text
FROM Tabelle4
ORDER BY date;
To add this into a new table, use the INTO part:
SELECT date, text INTO Tabelle5
FROM Tabelle4
ORDER BY date;
This works for me with Access 2000, Tabelle5 is sorted initially.
The query is easy and does what you want:
SELECT date, text
FROM Tabelle4
ORDER BY date;
To add this into a new table, use the INTO part:
SELECT date, text INTO Tabelle5
FROM Tabelle4
ORDER BY date;
This works for me with Access 2000, Tabelle5 is sorted initially.
I agree with wellilein; you need to post your SQL.
Pete
Pete
Btw this also works with only a range of dates, such as (Access 2000)
SELECT date, text INTO Tabelle5
FROM Tabelle4
WHERE date >#10/05/2003# AND date < #10/13/2003#
ORDER BY date;
SELECT date, text INTO Tabelle5
FROM Tabelle4
WHERE date >#10/05/2003# AND date < #10/13/2003#
ORDER BY date;
ASKER
Hi Mike,
Can you please explain what you mean. I don't know how to create an index in the resulting table of my Make Table Query at runtime.
Thanks,
shiraya
Can you please explain what you mean. I don't know how to create an index in the resulting table of my Make Table Query at runtime.
Thanks,
shiraya
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi everybody,
here is the SQL statement:
SELECT tblOrder.OrderDateReceived
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumb
WHERE (((tblOrder.OrderDateRecei
ORDER BY tblOrder.OrderDateReceived
The idea behind this is to create a table with records between 2 dates that I select on a form.
shiraya
here is the SQL statement:
SELECT tblOrder.OrderDateReceived
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumb
WHERE (((tblOrder.OrderDateRecei
ORDER BY tblOrder.OrderDateReceived
The idea behind this is to create a table with records between 2 dates that I select on a form.
shiraya
Another thing you can do,
Make a select query. Then, proceed using that query in a make table query with date sorted.
Make a select query. Then, proceed using that query in a make table query with date sorted.
ASKER
Hi Mike,
Yes, it works. The table is sorted after I indexed it. But how to do it automatically?
shiraya
Yes, it works. The table is sorted after I indexed it. But how to do it automatically?
shiraya
Two choices available:
1. Change you query to append. Before runnig this append query, you need to use a delete query to empty its content. Index remains intace.
2. Stay with make table query. After running it, using TableDef, add index to the table.
First method is very easy. The scond method is not hard either.
Mike
1. Change you query to append. Before runnig this append query, you need to use a delete query to empty its content. Index remains intace.
2. Stay with make table query. After running it, using TableDef, add index to the table.
First method is very easy. The scond method is not hard either.
Mike
Your statement works in Access 2000.
Do you have the field type set correctly? I.e. is it type Date/Time or is it Text? Text is sorted by ASCII, which is not the same sorting Dates.
My statement was:
SELECT tblOrder.OrderDateReceived
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumb
WHERE (((tblOrder.OrderDateRecei
ORDER BY tblOrder.OrderDateReceived
I am using dates directly with #10/01/2003#, while you are using dates from a forms combobox. Maybe there's the problem (Comboboxes usually return text, not dates)
Do you have the field type set correctly? I.e. is it type Date/Time or is it Text? Text is sorted by ASCII, which is not the same sorting Dates.
My statement was:
SELECT tblOrder.OrderDateReceived
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumb
WHERE (((tblOrder.OrderDateRecei
ORDER BY tblOrder.OrderDateReceived
I am using dates directly with #10/01/2003#, while you are using dates from a forms combobox. Maybe there's the problem (Comboboxes usually return text, not dates)
ASKER
Thanks to all for your help.
The solution from eghtebas (Mike) is the one that works and that I will be using.
wellilein: I checked my combo boxes. I use a function for the values in the combo boxes that lists the last 50 days as date. Maybe it has something to do with the combo boxes but I am happy that it works now. Thanks anyway.
Mike: Thanks for your help. I appreciate it and it works just fine.
shiraya
The solution from eghtebas (Mike) is the one that works and that I will be using.
wellilein: I checked my combo boxes. I use a function for the values in the combo boxes that lists the last 50 days as date. Maybe it has something to do with the combo boxes but I am happy that it works now. Thanks anyway.
Mike: Thanks for your help. I appreciate it and it works just fine.
shiraya
Glad to help.
Mike
Mike
Select * from table1 order by fieldtosort
Add DESC add the end of statement if want to sort them descendingly.