Solved

Table created by Make Table Query is not sorted. Why?

Posted on 2003-11-05
21
344 Views
Last Modified: 2008-03-06
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
Comment
Question by:shiraya
  • 8
  • 7
  • 3
  • +2
21 Comments
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 9685434
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
 

Author Comment

by:shiraya
ID: 9685474
Thanks for the quick answer, but it doesn't help me.

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

Regards,

shiraya
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685484
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685495
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9685504
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
 

Author Comment

by:shiraya
ID: 9685546
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685582
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
 

Author Comment

by:shiraya
ID: 9685600
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685621
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
 
LVL 2

Expert Comment

by:wellilein
ID: 9685685
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9685705
I agree with wellilein; you need to post your SQL.

Pete



0
 
LVL 2

Expert Comment

by:wellilein
ID: 9685712
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
 

Author Comment

by:shiraya
ID: 9685725
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
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 200 total points
ID: 9685768
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
 

Author Comment

by:shiraya
ID: 9685769
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685791
Another thing you can do,

Make a select query.  Then, proceed using that query in a make table query with date sorted.
0
 

Author Comment

by:shiraya
ID: 9685800
Hi Mike,

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

shiraya
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685820
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
 
LVL 2

Expert Comment

by:wellilein
ID: 9685856
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
 

Author Comment

by:shiraya
ID: 9685940
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9685948
Glad to help.

Mike
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now