Assign number value to records based on value in another field (1 Viewer)

[spudracer]

I have a continuous form that will display records if they are selected [UseForTracking] in a separate form.

These records already have a number value [Order] assigned in the source table, but these values can be changed by the user to adjust the order they are displayed in.

What I want to be able to do is with the records marked True with [UseForTracking], and based on the order the user had them set [Order], assign number values 1-8 in order automatically in another column [TrackingOrder].

Every search has come up with nothing.

 

[theDBguy]

Hi. When you said: "...assign number values 1-8 in order," what exactly do you mean by "in order?" Are you saying by the number value in the [Order] field in increasing order? Can you show us some sample data and the result you want out of them? Thanks.

 

[spudracer]

Hi. When you said: "...assign number values 1-8 in order," what exactly do you mean by "in order?" Are you saying by the number value in the [Order] field in increasing order? Can you show us some sample data and the result you want out of them? Thanks.

The value of the [Order] field can vary depending on the user. So, say there are three records that meet the tracking criteria. What I want to happen is the lowest [Order] number of those is assigned 1 with 2 going to the next number, again based on the order field, with 3 going to the last one.

 

[theDBguy]

Okay, thanks for the clarification. It might be possible to use a correlated subquery to get what you want. Can you post some table structure information or a query SQL to help us get started?

 

[spudracer]

Okay, thanks for the clarification. It might be possible to use a correlated subquery to get what you want. Can you post some table structure information or a query SQL to help us get started?

I'm using the TrackingOrder column in a form to identify what goes where, so these numbers have to be 1-8, or else the form won't work correctly. I was going to leave it to the user to enter 1-8 manually, but that's assuming they will actually do that (which is not always the case).

The source table is set up as follows:

Location (Text)	Order (Number)	UseForTracking (Yes/No)	TrackingOrder (Number)

 

[theDBguy]

Okay, thanks. So, it might look something like this (untested):

SELECT T1.Location, T1.Order,
(SELECT Count(*)
 FROM TableName 
 WHERE Order<=T1.Order AND UseForTracking=True) AS TrackingNumber
FROM TableName AS T1
WHERE UseForTracking=True

Hope that helps...

 

[spudracer]

Okay, thanks. So, it might look something like this (untested):

SELECT T1.Location, T1.Order,
(SELECT Count(*)
FROM TableName
WHERE Order<=T1.Order AND UseForTracking=True) AS TrackingNumber
FROM TableName AS T1
WHERE UseForTracking=True

Hope that helps...

Would that be in a query or in an OnClick event within the form?

 

[theDBguy]

Would that be in a query or in an OnClick event within the form?

That would be in a query that you can then use for a form or report, if you want.

 

[spudracer]

That would be in a query that you can then use for a form or report, if you want.

Great! I seem to recall coming across this when I was looking for a solution, but didn't really see the application. I think I'm there now. Thank you much!

 

[theDBguy]

Great! I seem to recall coming across this when I was looking for a solution, but didn't really see the application. I think I'm there now. Thank you much!

Hi. You're welcome. Glad we could assist. Good luck with your project. Let us know how it goes.

 

[spudracer]

That would be in a query that you can then use for a form or report, if you want.

It's me again... So, I created a query and in SQL view added your code in. When I go to view the results I get a Syntax Error in the query expression.

For reference, this is what I updated the code to:

SELECT T1.Location, T1.Order,
(SELECT Count(*)
 FROM tblLocation 
 WHERE Order<=T1.Order AND T1.UseForTracking=-1) AS TrackingOrder
FROM tblLocation AS T1
WHERE UseForTracking=-1

 

[theDBguy]

Hi. The query designer would have try to highlight the part causing the syntax error. Did it? If so, where did it say the error starts happening?

 

[spudracer]

Hi. The query designer would have try to highlight the part causing the syntax error. Did it? If so, where did it say the error starts happening?

It was a simple fix. Just need brackets around the Order and UseForTracking fields.

SELECT T1.Location, T1.Order,
(SELECT Count(*)
 FROM tblLocation 
 WHERE [Order]<=T1.Order AND [UseForTracking]=-1) AS TrackingOrder
FROM tblLocation AS T1
WHERE [UseForTracking]=-1

Now the only issue I have is getting the data from the TrackingOrder field in the query to the correct rows in the table.

 

[theDBguy]

Hi. Glad to hear you got it fixed. Was it giving you a consecutive number in the correct order? Just curious...

 

[spudracer]

Yes, the numbers are displaying in the correct order. However, like I said in my previous post, getting the [TrackingOrder] data to actually populate in the table has me scratching my head.

 

[theDBguy]

Yes, the numbers are displaying in the correct order. However, like I said in my previous post, getting the [TrackingOrder] data to actually populate in the table has me scratching my head.

Maybe you can use an UPDATE query for that. Do you really need it in the table though? Are you not able to use the query itself? Just curious...

 

[spudracer]

Maybe you can use an UPDATE query for that. Do you really need it in the table though? Are you not able to use the query itself? Just curious...

I guess the order doesn't really matter if it ends up in the table or not. It would be good to have it stored as a just in case, but it is what it is.

 

[theDBguy]

I guess the order doesn't really matter if it ends up in the table or not. It would be good to have it stored as a just in case, but it is what it is.

Right. You should be able to use an UPDATE query to store the number; but if the user changes their mind, then you need to make sure to update the table again. Whereas if you just simply use the query, it will always show the correct numbers because it's recalculated each time. Just my 2 cents...