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
> Is It Possible To Subtract The Min Of A Column From Every Value In That Column, Access 2013    
 
   
jmkeuning
post Dec 4 2019, 10:55 AM
Post#1



Posts: 176
Joined: 20-October 15



I have data that looks like this:

AutoValue
14word
15along
16alpha
17alpine
18already
19alter
20altered
21although
22aluminum
23alumni


I want to calculate the value of Auto - Min of Auto + 1 so:

AutoValueMinAuto-Min+1
14word141
15along142
16alpha143
17alpine144
18already145
19alter146
20altered147
21although148
22aluminum149
23alumni1410


Is this possible?
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 11:12 AM
Post#2


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Why do you want to do this?

It sort of looks like you are trying to create a ranking column. Is that the goal?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Dec 4 2019, 11:22 AM
Post#3


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. What happens if you have gaps in Auto? Does it matter?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 11:25 AM
Post#4


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


It also appears that most of the "values' are sorted alphabetically except the first one. Why is that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jmkeuning
post Dec 4 2019, 11:32 AM
Post#5



Posts: 176
Joined: 20-October 15



That was a mistake when I pulled the data into the table for the example. "word" is the field name
Go to the top of the page
 
jmkeuning
post Dec 4 2019, 11:33 AM
Post#6



Posts: 176
Joined: 20-October 15



Yes, it's the goal. I want a ranking column that starts at 1. Is there a better way to get here?
Go to the top of the page
 
jmkeuning
post Dec 4 2019, 11:34 AM
Post#7



Posts: 176
Joined: 20-October 15



There won't be, because this is a temporary table that gets emptied before an append query loads in a new list.
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 11:36 AM
Post#8


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


I see. One of the reasons I dislike attempts to alias names in sample data provided for questions is that it often obscures the real problem, but here, it led to an actual mistake in the values presented.

So you have a list of words, and what I assume is actually an AutoNumber in the first field. Right?

And the real goal is to provide a ranking from "1" to whatever the total count of records in the table would be. Right?


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2019, 11:39 AM
Post#9


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Here's a good explanation of creating ranking in a query.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nvogel
post Dec 4 2019, 11:39 AM
Post#10



Posts: 1,040
Joined: 26-January 14
From: London, UK


Try this:

SELECT Auto, Value,
(SELECT MIN(Auto) FROM tbl) AS Min,
Auto-(SELECT MIN(Auto) FROM tbl)+1 AS AutoMin1
FROM tbl;
Go to the top of the page
 
theDBguy
post Dec 4 2019, 11:40 AM
Post#11


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi.

QUOTE (jmkeuning)
There won't be, because this is a temporary table that gets emptied before an append query loads in a new list.

I wouldn't rely on that because an Autonumber fields is "not guaranteed" to always produce a sequential number. The only thing it supposed to guarantee is the number will be unique.

QUOTE (jmkeuning)
Yes, it's the goal. I want a ranking column that starts at 1. Is there a better way to get here?

Not sure about better, but one way to get there is to use a subquery. For example:

CODE
SELECT T1.*, (SELECT Count(SQ.*) FROM TableName SQ WHERE SQ.Auto<= T1.Auto) AS Seq  FROM TableName T1
(untested)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:29 AM