UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Order Of Data In A Table, Access 2016    
 
   
meyert
post Nov 2 2018, 03:40 PM
Post#1



Posts: 132
Joined: 22-January 08



Hello,

I have an issue and I hoping someone here can provide some guidance

I created a grouping query. to group by part number and then pull the first record

My issue is that even when I have sort on my query the "first" record is pulled based on the order the data is in the table. Not by the query sort

So I thought ok I will just use a query to append data into another table - -- query will have the sort that I want and then everything will pull correctly

But its not. It still has the order in the table in some random order

How is the data sort in the table itself controlled?
This post has been edited by meyert: Nov 2 2018, 03:41 PM
Go to the top of the page
 
DanielPineault
post Nov 2 2018, 04:03 PM
Post#2


UtterAccess VIP
Posts: 6,220
Joined: 30-June 11



You'd need to post your SQL, or even better a sample db to show us the table/data and explain the desired output and we will do our best to assist you.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
FrankRuperto
post Nov 2 2018, 04:05 PM
Post#3



Posts: 153
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Hi,

When you sort the data in a table, query, form, or report, you can save the sort order with the object. If you sort the data in one of these objects and then save the object, the sort order that is in effect when you save the object is automatically saved with the object. You can specify whether the saved sort order will be in effect when the object is next opened. For queries and reports, you can also define a default sort order. The default sort order is applied to the data in the query or report when no other sort order is specified. When you save a table, query, form, or report while the data is sorted, the sort order is automatically saved when you save the object. This is called a last-applied sort order. When you create a sort order, you are actually changing the design of the object. If you want the last-applied sort order to be automatically applied the next time that you open the object, set the Order By On Load property of the object to Yes.

hth
This post has been edited by FrankRuperto: Nov 2 2018, 04:09 PM

--------------------
Currently supporting many Pawnshops that use my Management System with Access 2010 on Windows7. Ham Radio addict since 1978.
Go to the top of the page
 
projecttoday
post Nov 2 2018, 04:24 PM
Post#4


UtterAccess VIP
Posts: 10,252
Joined: 10-February 04
From: South Charleston, WV


Maybe this will help, or this.

--------------------
Robert Crouser
Go to the top of the page
 
meyert
post Nov 2 2018, 05:26 PM
Post#5



Posts: 132
Joined: 22-January 08



Thank you for your replies. I don't have my files with me now. I understand the difference between sorting and grouping - but when I want to group by a field and then pull the first record in the group it doesn't do it


The sort criteria in the query is being ignored.

I will check into sorting the table not sure if I tried that.

So hypothetically if I sort the table will that sort be saved as data is removed and more added in?

Go to the top of the page
 
GroverParkGeorge
post Nov 2 2018, 06:07 PM
Post#6


UA Admin
Posts: 33,958
Joined: 20-June 02
From: Newcastle, WA


The ONLY way we can help is if you show us the full SQL from the query.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
FrankRuperto
post Nov 2 2018, 06:38 PM
Post#7



Posts: 153
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Check this

QUOTE
Thank you for your replies. I don't have my files with me now.


Ok, so can you at least provide us with an example of your query, off the top of your mind?

Another thing, this might sound like a silly question, did you create indexes on the fields you are sorting and grouping by?.. It could be that one or more fields that you are grouping by is not indexed, and the cost-based query optimizer is not able to utilize an index when building the query plan. As to whether setting the sort property on the table will produce your desired result, test that to see if it does the job.

How data is physically stored in Access tables is a "black box" mystery to me. I do know that in database servers like Informix and Oracle, when you create a new table and load data into them, the data will be physically stored in the order it was loaded. If you insert a new row, it will be appended at EOF (End Of File). If you delete a row, it will mark the row with a delete flag, but not physically remove the row. You can also CREATE CLUSTER INDEX on only one field, (usually the primary key), and that will physically reorder the rows in the table according to the CLUSTER INDEX.
This post has been edited by FrankRuperto: Nov 2 2018, 07:07 PM

--------------------
Currently supporting many Pawnshops that use my Management System with Access 2010 on Windows7. Ham Radio addict since 1978.
Go to the top of the page
 
nvogel
post Nov 3 2018, 04:46 AM
Post#8



Posts: 875
Joined: 26-January 14
From: London, UK


It's a principle of the relational model and SQL that rows in a table don't have any inherent order. Tables are supposed to be unordered sets of rows. I thought I would mention that, given the title of your question.

The only way to order rows is to specify the sorting you want in a query, report or when you display the data. That's why the best way to get help is to share your query here.
Go to the top of the page
 
meyert
post Nov 3 2018, 08:28 AM
Post#9



Posts: 132
Joined: 22-January 08



Thanks for your thoughts. If I get to work tomorrow I will upload a file. Otherwise it will be Monday

I can tell you that I feel 100% confident that the records in the table are not remaining in the order that they are entered. I only say that because I started with 1 table when I started having the issue. So I created an append query that sorted by the field record number. After the data was appended to the new table it was not in order by record number. Maybe 95% the records were but not all of them. Which supports what nvogel says.

As far as indexes I have had indexes and removed them as well just to try different things.

The queries all sort and perform exactly as I would expect. It is only when I build the basic query to group by that I am seeing this issue. I set the query up to group and pull the first record and that is when the sorting in the query fails me. The query pulls the first record as it shows in the table - - not based on the query sort

I will be back tomorrow with the file
This post has been edited by meyert: Nov 3 2018, 08:30 AM
Go to the top of the page
 
GroverParkGeorge
post Nov 3 2018, 09:32 AM
Post#10


UA Admin
Posts: 33,958
Joined: 20-June 02
From: Newcastle, WA


"I can tell you that I feel 100% confident that the records in the table are not remaining in the order that they are entered."

That's expected behavior and that is what nvogel tried to explain already.

Tables are "inherently unordered". That means the records in them are not in any predictable order. Think of it as a "heap" of records. Or maybe a "bucket". It's all in there, but whether a given record is on the top of the pile, on the bottom of the pile, or somewhere towards the middle is irrelevant.

YOU can only impose a desired sort order on records by retrieving them in that sort order from the unordered records in the table by using a query. Your query, therefore, needs to apply the appropriate sort order.

In order to trouble-shoot your query, we'll need to see the actual SQL of that query, and even better, some sample data to use with it.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
FrankRuperto
post Nov 3 2018, 10:24 AM
Post#11



Posts: 153
Joined: 21-September 14
From: Tampa Bay, Florida, USA


QUOTE
So I created an append query that sorted by the field record number. After the data was appended to the new table it was not in order by record number.


record number, as in an autonumber type field?.. You cant rely on them for a correct sort order, just as much as you also cant rely on sorting by a ROWID field.

You would have to create a new field and populate it with a sequence number, or if possible use one or more of the other fields in the table, like a DateTimeEntered timestamp field, to be used as a surrogate key in order to obtain the desired sort order.

We still look forward to seeing your table definition, query, and sample data.
This post has been edited by FrankRuperto: Nov 3 2018, 10:48 AM

--------------------
Currently supporting many Pawnshops that use my Management System with Access 2010 on Windows7. Ham Radio addict since 1978.
Go to the top of the page
 
FrankRuperto
post Nov 4 2018, 08:05 AM
Post#12



Posts: 153
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Addendum to my previous reply:

The only use I have for an Access autonumber field is for creating a primary key in a parent table used for joining to a foreign key in child tables, if warranted, as sometimes a different field needs to be used as primary key.

In anticipation of reviewing your query, most developers have a tendency to wanting to accomplish everything within one query when sometimes its necessasry to use more than one query to obtain the desired results, or as a workaround to limitations, bugs, etc.

Example:

CODE
SELECT a, b, c INTO TEMP tmp
FROM yourtable
ORDER BY a;

SELECT a, b, c
FROM tmp
GROUP BY a;

This post has been edited by FrankRuperto: Nov 4 2018, 08:09 AM

--------------------
Currently supporting many Pawnshops that use my Management System with Access 2010 on Windows7. Ham Radio addict since 1978.
Go to the top of the page
 
meyert
post Nov 6 2018, 07:30 AM
Post#13



Posts: 132
Joined: 22-January 08



Thank you for your reply

I completely agree with your statement that often times more than 1 query is needed to accomplish the desired result

What i have discovered is that if I remove the quantity field from the query view (just filter to return only quantities that are less than 0) then I can do the MIN on the date. This seems to be working

My concern is more when the first date actually is rather than the actual quantity anyway

I have to watch to be sure there are no surprises, but I think I may have the solution needed

Thanks for your time and comments
Go to the top of the page
 
FrankRuperto
post Nov 6 2018, 01:27 PM
Post#14



Posts: 153
Joined: 21-September 14
From: Tampa Bay, Florida, USA


QUOTE
What i have discovered is that if I remove the quantity field from the query view (just filter to return only quantities that are less than 0) then I can do the MIN on the date. This seems to be working

My concern is more when the first date actually is rather than the actual quantity anyway

I have to watch to be sure there are no surprises, but I think I may have the solution needed


IMHO, I dont feel that removing the quantity field is going to provide reliable results. The FIRST directive always works when you have a reliable sort order. If you cant use one or more existing fields like the date field as a reliable surrogate key, maybe you can create a new surrogate key field and populate it with a sequence that can guarantee the correct sort order you are seeking.
This post has been edited by FrankRuperto: Nov 6 2018, 01:37 PM

--------------------
Currently supporting many Pawnshops that use my Management System with Access 2010 on Windows7. Ham Radio addict since 1978.
Go to the top of the page
 
meyert
post Nov 6 2018, 06:35 PM
Post#15



Posts: 132
Joined: 22-January 08



I hear ya and I appreciate your input. I have changed things so much in the sort and such I need to step away from it for a little while to get a clear head pullhair.gif

I will probably revisit it again soon.. that's what I do. review review review
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2018 - 09:52 PM