Chunks of 20 products from a list of 180 - best approach? (1 Viewer)

So I want to pull in pricing information from Amazon for the goods I sell. Amazon, restrict such API requests to 20 products at a time ....I have about 180 products.

therefore I need to 'chop' my overall list of 180 products up into nine groups of 20 products, for example...

Product1,Product2,Product3,Product4...& so on up Product20

this list gets submitted with the remaining XML parsed

After the XML for the first 20 products has been parsed, I then need submit the next group of 20 products...

Product21,Product22,Product23 .....& so on up to Product40

I've managed to get the first group submitted, but I need a way of calling a sub routine/function that picks up where the last 'count' was left off.

I'm still finding my coding feet (very new to it all) & this is what I have so far (I'm sure it's very inelegant so welcome comments towards helping clean it up!)...

(note the use of the term 'ASIN' below can be taken to mean Product code)


but as you'll see myCounter is reset at the beginning of the function...which means every time I call the function only the same first 20 products will be returned) how can I get around this (I'm sure the answer is embarrassingly simple)

Add a flag to this table that says "I've already processed this."

At the start of your "grab 20-at-a-time" sequence, do an UPDATE query to reset all the flags to False.

Make your query the "Top 20" items for which the flag is False. When your do this, before you start the next iteration, be sure to mark the top 20 items to have the flag True. Loop through this "top 20" query until nothing is left.

As an option, reset the flag to False again before exiting.

You're a bit ahead of where I am!

I'm progressing through each challenge a little at a time (how to sign a URL, how to get access to request, how to parse the XML response etc.)...but that brings me onto my next step!!! My parsed XML looks like this...

B000W8HILS,11.99,true,6
B000W8IWCW,11.99,true,8
B002YHFIZU,9.99,true,16

...it's created one line at a time (at the minute all I'm doing to show the above is a debug.print in a loop with all the associated variables to construct a row.

I now need to work out how to get the parsed XML info into a table....so, perhaps I can work out this next bit now, which means I can then implement your suggestion!

It wasn't clear whether you were using your underlying product tables to generate the XML request. My suggestion was how to limit the visitation of your tables as you build your XML request from those tables. If that assumption was incorrect, Whoops!

Ok here's what I've done.....

1. I added a new true/false column in my main 'products' table called 'PreviouslyRequested' (a flag basically).

2. In my main body of code, I call a function to 'go get' the next 20 (previously unrequested) products - this aimply hunts down the table column for the first row that 'PreviouslyRequested' is not set....& then starts counting a further 20 products from that point.

Simple enough...*but* how can I get my main body of code to know when all products have been requested? Else there'll be a loop as the main body of code calls the sub function but the sub function has no more products (it's at EOF) & therefore returns nothing...rinse repeat.

How do I cater for this scenario?

Ok, re what happens when the EOF file is reached - now sorted, I've a counter in my sub function (which chops the products up into groups of 20....or until the EOF is reached), I simply return the count back to the main code....this counter is then used in the main code.

The main problem I had was that by going this way, I needed to return two values from the sub function, but I've worked around that by concatenating two values at the end of the sub function, then deconcatenating them back in the main code again....fugly, but functional!

Search the difference ByVal versus ByRef

If your calling procedure passes variables ByRef to the subroutine, this will enable variables to be set and available in the calling routine without the need to conconcatenate/deconconcatenate strings.

Another useful thing to note is the Static keyword.

So, rather than Dim, you can use Static, and the variable value will be retained.

One approach is to get the nth set of 20 records is to select the top (n-1) * 20 records, find the value of the ID last in descending order, then open a set of 20 records with the ID < that number
ie

set rst = db.openrecordset("select top " & ((n -1) * 20) & " * from tblProducts order by SKU DESC")
rst.movelast
lngLast = rst!SKU
set rst = db.openrecordset("Select top 20 * From tblProducts where SKU < " & lngLast & " order by SKU DESC")