Sequence custom query

[drag1c]

Hi all,

I am using table with multiple columns, but for this operation query needs just two columns + one custom column named Sequence with formula.

For example, I have columns [Order] and [Workcenter].
Formula should look like this:
=IF(row2.Order<>row1.Order,1,IF(row2.Order&row2.Workcenter<>row1.Order&row1.Workcenter,Sequence+1,Sequence))
=IF(H4<>H3,1,IF(H4&L4<>H3&L3,T3+1,T3)) -> This is presentation how it would look like in Excel.

But I want to get rid of excel file and to work with database. Right now I work with all data in Access and just because of this step I have to manually copy data into excel to calculate via formula

Does anyone have idea how it would work?

 

[XPS35]

It won't work in Access.
In Access and any other DATAbase it's all about DATA. In Excel you can refer to the previous row. In Access you cannot do that. You need data to know the order of the row/records. So you need something like a date/time or entry number to determine the order of the rows.

 

[Micron]

In Excel you can refer to the previous row. In Access you cannot do that.

Not sure I agree with that. One way to refer to other rows within data.

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

Whether or not it can be accomplished for this particular issue I don't know. For one thing, showing 2 fields of data doesn't provide much in the way of insight to the data. There would have to be something that provides a usable order.

 

[drag1c]

Not sure I agree with that. One way to refer to other rows within data.

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

Whether or not it can be accomplished for this particular issue I don't know. For one thing, showing 2 fields of data doesn't provide much in the way of insight to the data. There would have to be something that provides a usable order.

I can provide all columns, but I think they are not necessary for this calculation.

I think there should be used "IIF" through query, but question is how to call it for a row?

Here are all columns:

So, for this calculation is needed only Order and Workcenter.

 

[XPS35]

There would have to be something that provides a usable order.

That is exactly what I said.

 

[XPS35]

So, for this calculation is needed only Order and Workcenter.

What does the NR field mean. Does it say anything about the order of the records?

 

[XPS35]

Looking at your table again I am not sure all those that should be in one table. Most times when you switch from Excel to Access you cannot move a table one to one to Access. Proper Access tables are normalized and I doubt that when you apply the normalization rules, you would end up with one table. Sort that out before you move on.

On second thoughts I also doubt that you should store the sequence at all. In fact it is derived data and it is preferable not to store that kind of data. What would you do if you have sequence numbers 1, 2 and 3 with a order/workcenter and you delete the sequence 2 entry? Or when you find out you entered the wrong workcenter for sequence 2 and change it?

So plenty to think of in response to a seemingly simple question

 

[Micron]

I think there should be used "IIF" through query, but question is how to call it for a row?

A function in a query is applied to every record that the query returns.
If that is what all of your tables look like, then you're always going to struggle. You haven't said (or I missed) what the sequence field is for. Perhaps if your tables were properly normalized and indexed you would not need that at all. Having an autonumber primary key might be all that you need to get some sort of order while using query criteria.

 

[petertenthije]

What you could do is create a query that identifies all unique combinations of [order] and [workcenter].

The resulting list can be copied into a table that has a autonumber column. This autonumber column would be your sequence.

Then, using a second query, you can cross reference the [order] and [workcenter] data to add the sequence to every row.