Sequential Row Count

[alexfwalker81]

Hi everyone, first post in a while, and first one on a new username!

The background is that I am trying to create an order import file for a legacy system, which is very rigid in terms of what it expects. The specific problem is that for an order which has multiple products on it, each product line must be sequentially numbered - a bit like this;

Order 123 | 001 | Product1 | Qty
Order 123 | 002 | Product2 | Qty
Order 123 | 003 | Product3 | Qty
Order 124 | 001 | Product1 | Qty
Order 125 | 001 | Product6 | Qty
Order 125 | 002 | Product5 | Qty

What I am struggling to do is find a way to dynamically add the 001, 002, 003. I can count the number of rows in an order, but I just cannot figure out how to sequentially number them in my query!

There will always be several orders in an order file, as per the example above.

I can think of various ways to do this in excel, but this needs to happen in Access, as the rest of the processes that surround this output require a database, not a spreadsheet.

Thank you!

 

[sneuberg]

I'm not sure what your input to this process is. Is it that you are given an Order Number, the product names and quantities, and the products are to be numbered arbitrarily as long as the line numbers within an order are sequential. Or is there some order to the input.

Where does the output go. Is it appended to a table or does it result in a report?

 

[MarkK]

I just cannot figure out how to sequentially number them in my query!

Don't do this in the query, do it after the query, in a recordset or a report, or whatever you do after your query completes.

 

[alexfwalker81]

@sneuberg - it's still very much a work in process, but as I need to add a file header line, and a file footer line, I'm anticipating doing this in a report that would then be output to a pipe delimited text file.

There's no stipulated order to the input. It's just a requirement that the output has sequentially numbered order lines, regardless of their content.

@MarkK - ok, sounds more promising as I've hit a total wall in the query. How do I do that in a report?


One approach I have considered overnight would be simply to create an order from each combination of Order Number & Product. So in other words, each order would have a maximum of one product (therefore only requiring a line of 001), as product lines are always unique within an order.

The problem is that although that would work, it feels like cheating on the original brain teaser!

 

[sneuberg]

If the goal of this is the delimited text file I would skip the report and do all of this in code. Given what you described so far this doesn't look very complicated. If you provide specific information on the inputs and the output format, I'll put together some code.

 

[alexfwalker81]

Thank you so much for the offer. Give me the weekend, as I've spent today in an incredibly dull meeting, which has at least given me time to think about how to do this better.

If I'm still stuck by Sunday then I'll be back for help!

 

[MarkK]

In a report you add a global variable to hold the row count. Then group by the order, and add a header for that group. Zero the counter when the header prints, and add 1 to the counter for every detail record, and display the counter in every detail section.

Do a similar process for whatever other approach you might take,
1) loop thru every record,
2) zero the counter when the order changes,
3) increment the counter for every row in the order.