Ordered query (1 Viewer)

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
observations.jpg

Hi guys,

I am trying to sort out this issue where I need the last row to be inserted at the right location distance wise. This is a query from 2 tables: Observation and Inspection. Observation table contains the foreign key of the Inspection table. This query only contains the observations of inspection with Key 104. Sometimes in the inspection process of a pipe, the operator miss observations and I would like to add them and place them in the right order ( distance wise). I know the last record is repeated. I am just using it as an example. So the last record would be placed after record with observationID 915.

Thank you in advance,

Sac
 

Isaac

Lifelong Learner
Local time
Today, 00:36
Joined
Mar 14, 2017
Messages
2,738
Databases do not guarantee any specific record order of how data is stored on disk, and that line of thinking should not be leveraged in any way. ORDER BY clauses in queries (etc) is what renders the desired order/sort.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:36
Joined
Feb 19, 2013
Messages
12,586
As isaac says, just order by (or sort) on the inspection and distance fields and the record will automatically appear in the correct location.

Where you have multiple instances with the same distance, these will appear together, but within that little group, they will be random. If this matters you will need a third column you will need to order by
 

arnelgp

error reading drive A:
Local time
Today, 16:36
Joined
May 7, 2009
Messages
10,859
physically re-arranging the table is not ideal specially on multiuser system.
it can be done, but you will need to wait till the db become Exlusively available.

instead, you can "order" it using a query with Order by Clause.
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:36
Joined
Aug 11, 2003
Messages
11,680
physically re-arranging the table is not ideal specially
"not ideal" you mean NOT POSIBLE.
Any query by its nature is a random fetch from the table(s), it will 99% of all times return the order data was entered in the table, assuming only one table is used, with joined tables all bets are off....
however even with 1 table, it is no guarantee to any order.... unless specified in a ORDER BY clause.
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Jan 23, 2006
Messages
13,401
You need to use ORDER BY Distance if you want the Distance column to be displayed in ascending Distance values. And as CJ said the record with ObservationID 937 could be before or after 915 since they both have Distance 4.3.
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Hi All,

As usual, thanks for all the use full comments. I am so sorry that I forgot to mention that it is the result from the sql statement shown below. I sorted by observation and distance. I did that because the code values need to be in the right sequence (Start of inspection, Line deviates down and Water level must be sequential at the start of the inspection) as shown in the picture above. My problem is when I add another observation missed by the operator in the field, that observation needs to fall in the sequence by distance.

Thank you all for taking the time to comment on this issue!!

SELECT DISTINCT CODE.KEY, CODE.CODE, CODE.DESCRIPTION, OBSERVATION.OBSERVATIONID, OBSERVATION.INSPECTION, OBSERVATION.DESCRIPTION, OBSERVATION.DISTANCE, OBSERVATION.CLOCKFROM, OBSERVATION.CLOCKTO, OBSERVATION.REMARKS, OBSERVATION.CONTINEOUSS, OBSERVATION.CONTINEOUSF, OBSERVATION.LENGTH, OBSERVATION.JOINT, OBSERVATION.PERCENTAGE, OBSERVATION.LATHEIGHT, CODE.DESCRIPTION AS CodeDesc
FROM CODE RIGHT JOIN OBSERVATION ON CODE.KEY = OBSERVATION.CODE
WHERE (((OBSERVATION.INSPECTION)=104))
ORDER BY OBSERVATION.OBSERVATIONID, OBSERVATION.DISTANCE;
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Jan 23, 2006
Messages
13,401
If you want the observations to be Distance ascending sequence,
then
remove OBSERVATION.OBSERVATIONID from the ORDER BY OBSERVATION.DISTANCE;
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
If you want the observations to be Distance ascending sequence,
then
remove OBSERVATION.OBSERVATIONID from the ORDER BY OBSERVATION.DISTANCE;
JDraw,

If I do that, then I loose the code sequence. That is, (ST, LD, WL.....................FH). The first 3 codes must be in that order. In addition, the inspection must end with code FH.

Thanks for the reply.

Sac
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Jan 23, 2006
Messages
13,401
Perhaps you should tell us exactly what you want as output.

FH will not collate after WL, unless you set up a separate sequencing arrangement.

another field or table, called MySequence
SeqCd, SeqVal
1- ST
2- LD
3- WL
99- FH

Sort on SeqCd and Display SeqVal.

What in your SQL above dealt with Code sequence?
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
JDraw,

The sequence above (ST,LD,WL FH) will be created if there are no other observations in between ST (start if inspection) and FH (Finish Inspection). I will call that a perfect pipe with no other observation or defect. Every inspection must start with the first 3 codes. If no other observation is found in the inspection, the FH code will be added to indicate that the inspection is finished. But like I said above, Operators miss observations while doing the live inspection. So That's when we need to add those additional observations. Perhaps adding another field in the observation table where I can control the sort like you explained would do.

Thanks so much for your help!!

SAC
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Feb 19, 2002
Messages
30,098
try sorting by inspectionID, distance, and then ObservationID. This will sort rows with the same distance value by the order in which they were entered. That means that if you have some rows that MUST be first and MUST be in a specific sequence, then you would need to delete the entire set if you missed one.

Another, more flexible solution is to add a sequence number that is generated when a row is added. The sequence number needs to increment by 10 within InspectionID. So if you have 10, 20, 30, oops forgot the item between 10 and 20. The generated number will be 40 but you can change it to 25 to move it to the correct place. When I use this type of sequence number, I also add the ability to renumber the set back to 10s in case something else needs to get inserted.

If you want code, I'll post it or an example if you want one.
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Pat,

The second part of your comment is what most likely will work in this situation. I would like to see the code sample if its possible.

Thanks so much!!

Sac
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Feb 19, 2002
Messages
30,098
Rather than tear the code out, I'll post a sample database. The code you are looking for is on one of the subforms of the custom switchboard maintenance form.
 

Attachments

  • SwitchboardForm20201018c.zip
    1.6 MB · Views: 10

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Pat,

Thank you very much for your help! I am relatively new to Access and VBA. I have 3 tables to play with in my case: INSPECTIONS, OBSERVATIONS and CODE. Those are the tables I used to produce the list above. (INSPECTION (1) TO OBSERVATIONS (many) and OBSERVATIONS (1) to CODE (many). I really like your approach but it uses only one table (tblSwitchboardItems). Also, I use primary keys in the tables. But I see the way you did your solution. That is, use an update query to update the sorting field with its value plus 1000. and then grab the rows of tblSwitchboardItems table and increment the values by 10. Also, I guess I would have remove the primary keys on my tables in order to insert delete and update.


Sac
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Feb 19, 2002
Messages
30,098
You should NOT be using the sequence number as the PK. Use an autonumber as the PK. In my solution, the sequence number is not the PK. It can't be because it is not unique over the table. It is only unique within a group of rows.
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Pat,
You are right. InspectionID is an autonumber in the inspection table. As well as in the observation table as foreign key.
I am trying to to see how can I use your code with the 3 tables (Inspection, Observation and Code). The list above is the result of a query. The sorting has to be done in the Observation table as all the values are stored in that table. So I was thinking on adding another field in the Observation table as in your code. eg: ItemNumber and go from there.

Thanks a lot for your help!! It has got me thinking.

Sac
 

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Pat,

Having a third look at your code, InspectionID should be used as SwitchboardID. Even though InspectionID is not the key of the table Observation.

Thanks!!

Sac
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Feb 19, 2002
Messages
30,098
Let's start with - you NEVER renumber a primary key. Period!!!

I actually modified a different sample that was only showing the generation of human-readable unique identifiers that would be used as invoice numbers or order numbers or whatever. I added a second table and in that table I generated a simple sequence number which numbers just a set of data. It does exactly the same thing that the Switchboard example does but it might make more sense to you since the context is a little different.
 

Attachments

  • CustomSequenceNumber20201020c.zip
    85.6 KB · Views: 10

Sac

Member
Local time
Today, 03:36
Joined
Feb 28, 2020
Messages
30
Pat,

Thanks for the code!! Yes it does make more sense to me in relation to my issue!!

Sac
 

Users who are viewing this thread

Top Bottom