• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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


0
shiraya
Asked:
shiraya
  • 8
  • 7
  • 3
  • +2
1 Solution
 
Ryan ChongCommented:
Just add a "Order By" clause in order to sort your query, example:

Select * from table1 order by fieldtosort

Add DESC add the end of statement if want to sort them descendingly.
0
 
shirayaAuthor Commented:
Thanks for the quick answer, but it doesn't help me.

The QUERY is sorted, but the resulting TABLE is not.

Regards,

shiraya
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
In your make table query add a new field with alias SortField, where:

SortField:Format([DateField],"yyyymmdd")

include this field as first field and sort it as you like.  

Mike
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
peter57rCommented:
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

0
 
shirayaAuthor Commented:
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


0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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([DateField],"yyyymmdd").

Mike
0
 
shirayaAuthor Commented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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).
0
 
wellileinCommented:
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.
0
 
peter57rCommented:
I agree with wellilein; you need to post your SQL.

Pete



0
 
wellileinCommented:
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;
0
 
shirayaAuthor Commented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Just to do it manually first, after make table operation, got to design view of your table and select View/Index from menu bar.  Include DateField you are trying to sort as your 1st entry.  Save and run the table.  If it handle it then we can talk about automatig this process.

Mike
0
 
shirayaAuthor Commented:
Hi everybody,

here is the SQL statement:

SELECT tblOrder.OrderDateReceived, tblOrder.OrderID, tblCompany.CompanyName INTO tblDataForReportForGermany
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumber = tblOrder.CompanyAutonumber
WHERE (((tblOrder.OrderDateReceived) Between [Forms]![frmReportForGermany]![cboDateReportFrom] And [Forms]![frmReportForGermany]![cboDateReportTo]))
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Another thing you can do,

Make a select query.  Then, proceed using that query in a make table query with date sorted.
0
 
shirayaAuthor Commented:
Hi Mike,

Yes, it works. The table is sorted after I indexed it. But how to do it automatically?

shiraya
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
wellileinCommented:
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, tblOrder.OrderID, tblCompany.CompanyName INTO tblDataForReportForGermany
FROM tblCompany INNER JOIN tblOrder ON tblCompany.CompanyAutonumber = tblOrder.CompanyAutonumber
WHERE (((tblOrder.OrderDateReceived) Between #10/01/2003# And #10/20/2003#))
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)
0
 
shirayaAuthor Commented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Glad to help.

Mike
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now