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
> Special Query Problem, Access 2007    
 
   
dim.1962
post Mar 14 2019, 07:32 PM
Post#1



Posts: 4
Joined: 14-March 19



Hi,

I have a table with two fields [Op] (numeric) and [Dep] (Text) ordered A-Z by field [Op]:

Op Dp
10 A
20 B
30 B
40 C
50 A
60 A
70 B
80 C
90 C
100 C

I need to add a new field named [Tag] to indicate the counter for each value which will repeat.
For example the value A appear only one time then B two times and C one time, etc...
In other words the counter have to start each time when the value for Dp will change.
So the new table should be like this:

Op Dp Tag
10 A 1
20 B 1
30 B 2
40 C 1
50 A 1
60 A 2
70 B 1
80 C 1
90 C 2
100 C 3

I did not find any way to do it.
Can you help please?

Thanks!
This post has been edited by dim.1962: Mar 14 2019, 07:35 PM
Go to the top of the page
 
theDBguy
post Mar 14 2019, 08:03 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,695
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

You don’t need a new filed in the table for this, just use a query. Try to a search on the term “serialize,”

--------------------
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
 
MadPiet
post Mar 14 2019, 08:07 PM
Post#3



Posts: 2,942
Joined: 27-February 09



Not sure I totally follow what you're trying to do, because you never provided an example of your expected result.

Op Dp Tag
10 A 1
20 B 1
30 B 2
40 C 1
50 A 1
60 A 2
70 B 1
80 C 1
90 C 2
100 C 3

It sounds like you can get what you want by adding another table, a table of "Numbers"... In this case it would only have 3 values... like this:

CREATE TABLE Tally (N int PRIMARY KEY);

GO
INSERT INTO Tally(N) VALUES (1), (2), (3);

Then you could join that to your original table

SELECT ot.Op, ot.Dp, ot.Tag, t.N
FROM OriginalTable ot INNER JOIN Tally t
ON t.N<=ot.Tag

Go to the top of the page
 
dim.1962
post Mar 15 2019, 03:21 PM
Post#4



Posts: 4
Joined: 14-March 19



Hi,

I need a query on this table with a new field Tag which will show the value 1 or 2 or 3... respecting the rule what I mentioned.
So in others words, I need a formula to calculate those values for the field Tag.
I hope now is more clear.

Thank you
Go to the top of the page
 
theDBguy
post Mar 15 2019, 04:12 PM
Post#5


Access Wiki and Forums Moderator
Posts: 74,695
Joined: 19-June 07
From: SunnySandyEggo


Hi. What did you find when you searched for “serialized?”

--------------------
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
 
dim.1962
post Mar 15 2019, 08:40 PM
Post#6



Posts: 4
Joined: 14-March 19



Thank you theDBguy, but I did not found something which can help me.
I'm not so advanced and actually, I don't know exactly what is the serialize function.
Go to the top of the page
 
RJD
post Mar 15 2019, 08:47 PM
Post#7


UtterAccess VIP
Posts: 9,507
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but what is the real-world application for this? It looks like you may have greatly simplified things, and that may mask the real situation.

To just address this as shown, it looks like a VBA routine to write another table would be an easy way to do this, since you have to re-start the numbering sequence as the Dp value changes each time. How are your VBA skills?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Mar 15 2019, 08:48 PM
Post#8


Access Wiki and Forums Moderator
Posts: 74,695
Joined: 19-June 07
From: SunnySandyEggo


Hi. Here’s one from the Code Archive you could try.

--------------------
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
 
dim.1962
post Mar 18 2019, 08:22 PM
Post#9



Posts: 4
Joined: 14-March 19



Thank you theDBguy,

The module used in your example for the function serialize was exactly what I need!

Thanks again!
Go to the top of the page
 
theDBguy
post Mar 18 2019, 08:27 PM
Post#10


Access Wiki and Forums Moderator
Posts: 74,695
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.

--------------------
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    25th March 2019 - 05:07 PM