Solved

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

Posted on 2003-11-05
21
325 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 49

Expert Comment

by:Ryan Chong
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I agree with wellilein; you need to post your SQL.

Pete



0
 
LVL 2

Expert Comment

by:wellilein
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad to help.

Mike
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

11 Experts available now in Live!

Get 1:1 Help Now