Finding the next and previous record in a table

[respree]

Hello. I'm not sure if Access can do this, but I'm trying to write a query that will find the next and previous sequential records in a table.

SKU is given, PREVIOUS and NEXT are calculated based on SKU.

If calculated properly, it would look something like this:

SKU PREVIOUS NEXT
B0587159901 B0587159979 B058715991X
B058715991X B0587159901 B0587159928
B0587159928 B058715991X B0587159936
B0587159936 B0587159928 B0587159944
B0587159944 B0587159936 B0587159952
B0587159952 B0587159944 B0587159960
B0587159960 B0587159952 B0587159979
B0587159979 B0587159960 B0587159901


Note: For the first record (SKU), the PREVIOUS field would show the last record. For the last record (SKU), the NEXT field would show the first record.

Can anyone think of a way to do this. I'm not sure even where to start.

Any help would be greatly appreciated.

 

[dk]

Hi,

Assuming you're familar with SQL you can just use a few select statements for most of your SKUs e.g.

SELECT tblSKUS.SKU, (SELECT TOP 1 tblSKUS.SKU FROM tblSKUS WHERE (tblSKUS.SKU<'B0587159936') ORDER BY SKU DESC) AS Before,

(SELECT TOP 1 tblSKUS.SKU FROM tblSKUS WHERE (tblSKUS.SKU>'B0587159936') ORDER BY SKU) AS After

FROM tblSKUS WHERE (((tblSKUS.SKU)='B0587159936'));

The tricky part I think is getting it to return the first as the after and last as before for the first and last SKUs but it should be doable with some IIF statements. The basic syntax for an IIF statement is:-

IIF(Test, SQL statement to execute if True, SQL statement to execute if False).

Hope that gets you started.

 

[respree]

Hi dk -

I'm afraid I'm new to Access and don't have any SQL experience, but I'd appreciate some clarification.

Is the logic you posted based on an assumption the the SKU's are in sequential ascending order. I'm assuming that because I see the < sign in your code. In my case (for the live data) they are not (although in the example I gave, they were. sorry if that mislead you).

If so, can the logic be changed so that looks it for the 'next' and 'previous' record, rather than determining if the current record is <> the next/previous.

Any help would be appreciated. Thanks again.

 

[mdmilner]

Could you share why you need to create this query?

What technical problem are you trying to solve? What capability are you trying to create?

You may be 'solving' something that has a much simpler method.

Mike

 

[respree]

Surely. This is part of an inventory database, used for an online store. The next and previous records correspond to links a user will click when they view a product page (as in "next product" and "previous product").

From raw data, my Access database is creating information needed for this product database (which eventually will be imported into a MYSQL database). Among the information appearing in the database is SKU, Title, Prices, graphic references (.jpgs and thumbnails), categories and HTML.

If you'd like to see what I'm talking about, you can go to our store.

Its my username in this forum plus a .com extension.

When you get to a product page, you'll see the context of the next and previous database fields I seek.

Thanks for any help.

I might have chosen some kind of other progamming to call upon the next and previous field calls to the MYSQL database, but unfortunately, I'm not a programmer (wouldn't know how) and its somewhat complicated by the fact that any code would also have to interact with our shopping cart program.

 

[dk]

Hi dk -

I'm afraid I'm new to Access and don't have any SQL experience, but I'd appreciate some clarification.

Is the logic you posted based on an assumption the the SKU's are in sequential ascending order. I'm assuming that because I see the < sign in your code. In my case (for the live data) they are not (although in the example I gave, they were. sorry if that mislead you).

If so, can the logic be changed so that looks it for the 'next' and 'previous' record, rather than determining if the current record is <> the next/previous.

Any help would be appreciated. Thanks again.

Hi,

Notice the ORDER BY part of the SQL statement? That will ensure that the SKU numbers are sorted correctly.

After I read your explanation of what you want this for I would have suggested using some simple VBA to do what you need. However, if you're transferring this database to MySQL then I'm not sure what to suggest.

 

[mdmilner]

That's what I was thinking, VBA module.
But, it sure sounds like this will be a one-time event. You only need to create the list once and then you're done.

I have this mental picture of a sorted excel spreadsheet with three identical columns of data. Use the Insert Cell/Delete Cell functions (manually) to readjust the positions of the data so that when you're done, each row (record) will be correct. Import into Access or directly to MySQL.

Sometimes the complex is unnecessary.

Mike

 

[respree]

Thanks for your response.

Actually, I have been doing my database in Excel with a bunch of formulas and have been achieving this result in the manner which you describe. In Excel, its fairly easy to refer to the next or previous record with a formula, but it appears Access is not as friendly (I'm new to Access by the way).

Unfortunately, my inventory has now reached more than 100,000 records, which is making this exercise very cumbersome in Excel. The finished product in Excel is about 100 megabytes. Due to Excel's row limitation, I have to use three tabs to manipulate and move data around. It takes about 12 hours of manual copying of manipulation to produce the finished product from the raw data.

I've now automated this process in Access, but this is the final piece I cannot seem to figure out.

Any suggestions?

 

[respree]

Also, more clarification. Due to the nature of product availability, there are hundreds of deleted and added records on a regular basis. If worst comes to worst, I can manually do this in Excel, then import it. Unfortunately, the database is updated with some regularity (so its, unfortunately, not a one-time event).

My goal was to completely automate this process (if that is possible).

Thanks again for your help.