How do I find records in groups, make changes and continue...

[sladewilson]

I have an ASP intranet application powered by an Access database. The database holds all my product data, shoes to be exact. I need to be able to process groups of items with the same NAME then proceed to the next group and run the same process again... how can I do this?

The data looks like this:
SKU Name Size Price On-Hand
4900 Bogs Shoe 59.99
4907 Bogs Shoe 6 59.99 11
4915 Bogs Shoe 6.5 59.99 9
4923 Bogs Shoe 7 59.99 14
4934 Regs Shoe 54.99
4955 Regs Shoe 3 54.99 7
4976 Regs Shoe 3.5 54.99 0
4997 Regs Shoe 4 54.99 4

I need it to look like this when I get done... maybe even process the data and save the finished product to another table.

SKU Name Price Options
4900 Bogs Shoe 59.99 Size 6 6.5 7
4934 Regs Shoe 54.99 Size 3 4

 

[emozley]

I think the best way to do it would be to loop through the recordset and first display the show name and then start a loop that continues until the shoe name is different to the previous record. This 2nd inner loop would build your <select></select> form element.

Something along the lines of:

<%
Do While Not rs.EOF
Response.Write(rs("ShoeName"))
%>
<select name="ShoeSize">
<%
Do While rs("ShoeName")=LastShoeName
%>
<option value="<% = rs("ShoeSize") %>"><% = rs("ShoeSize") %></option>
<%
LastShoeName=rs("ShoeName")
rs.MoveNext
Loop
%>
</select>
<%
rs.MoveNext
Loop
%>

Please note this code is not tested and may well miss out records under some circumstances but may be a sensible starting point!

 

[Tarwn]

I would advise against saving this data to another table, the data looks good the way it is and by creating another table derived from the data in this one you will not only be looking at data redundancy, but also have to keep that tables in sync (which isn't fun). And all for very little gain.

You stated that you wanted to output one line for each shoe name and it appears that later lines simply get their Size added to a comma-delimited string. I'm assuming, for the sake of description, that your using a table.

The easiest way to do this would be to declare a variable before you loop through you recordset and then in your recordset use that variable to keep track of the last shoe name you processed. At the beginning of each loop you could compare the shoe name of the current record to the shoe name from the previous record and, if they changed, start a new row, output the SKU, name, and price and necessary HTML. You should also start the table cell for the sizes here.

After that you would want to check if the current record has a size, and if so, output it. Then you will want to save the shoe name for the current record to your variable and MoveNext to the next record. Before the end of the loop you want a final set of checks.
this part can get a little confusing, so i will do it in pseudo-code:

If yourRecordset.EOF Then
   'end this row becuse it is last
   Response.Write "</td></tr>"
ElseIf yourRecordset("ShoeName") <> LastShoeName Then
   'end this row so a new row can start at beginning of next loop
   Response.Write "</td></tr>"
End If

If you do implement your loop in this fashion, then your biggest challenge is going to be figuring out how to handle the comma between the available shoe sizes.

-T