Access/ASP help displaying distinct Info

[printedgoods]

Hello all,

I have a table that has data about the t-shirts i sell. My goal is to display the colors that are available in a specific way on my asp page:

Shirt

I have been playing around for hours with this and need a 2nd opinion.


I am using the following query to get the data:

set color2RecordSet = Server.createObject("ADODB.RecordSet")
color2Query = "SELECT DISTINCT product_shirts_colors_sizes.color, product_shirts_colors_sizes.hex, product_shirts_colors_sizes.size, product_shirts_colors_sizes.size FROM product_shirts_colors_sizes WHERE (((product_shirts_colors_sizes.size)='Youth Large' Or (product_shirts_colors_sizes.size)='Adult Large') AND ((product_shirts_colors_sizes.product_id)=" & Request.QueryString("product_id") & "))"  
                color2RecordSet.Open color2Query, dbConn, adOpenStatic, adLockReadOnly, adCmdText
 
 

And the following to display it:

<% while not  color2RecordSet.EOF  %>                    
<% if  color2RecordSet.fields("size") = "Youth Large" then %>          
<div style="padding:2px; padding-left:20px; height:30px; width:180px">
<div style="height:30px; width:30px; float:left; background-color:#<%= color2RecordSet.fields("hex")%>"></div>
<div style="height:25px; padding-left:10px; padding-top:5px; float:right; width:140px; color:#FF0000" ><%= color2RecordSet.fields("color")%></div>
</div>
<% else %>
<div style="padding:2px; padding-left:20px; height:30px; width:180px">
<div style="height:30px; width:30px; float:left; background-color:#<%= color2RecordSet.fields("hex")%>"></div>
<div style="height:25px; padding-left:10px; padding-top:5px; float:right; width:140px" ><%= color2RecordSet.fields("color")%></div>
</div> 
<% end if%>     
<% color2RecordSet.movenext  %>
<% wend %>
 

The problem is that not all colors are available in youth as seen in this image:

What i am trying to accomplish is pulling distinct colors and if it has youth sizes then display in red. If it dose not have youth sizes the display in black.

I hope i have explained enough... if not i can supply more info.

Thanks in advance

[jhardman]

I am using the following query to get the data:

set color2RecordSet = Server.createObject("ADODB.RecordSet")
color2Query = "SELECT DISTINCT product_shirts_colors_sizes.color, product_shirts_colors_sizes.hex, product_shirts_colors_sizes.size, product_shirts_colors_sizes.size FROM product_shirts_colors_sizes WHERE (((product_shirts_colors_sizes.size)='Youth Large' Or (product_shirts_colors_sizes.size)='Adult Large') AND ((product_shirts_colors_sizes.product_id)=" & Request.QueryString("product_id") & "))"  
                color2RecordSet.Open color2Query, dbConn, adOpenStatic, adLockReadOnly, adCmdText
 
 

When you are only pulling up info from one table, it is OK to shorten the name of the field to just the name of the field, you don't have to say tabelName.fieldName, just say fieldName:

color2Query = "SELECT DISTINCT color, hex, size FROM product_shirts_colors_sizes WHERE (((size)='Youth Large' Or (size)='Adult Large') AND ((product_id)=" & Request.QueryString("product_id") & "))"  

I know, it's a little thing, but I figure it helps, right?

And the following to display it:

So you are currently pulling up all the records where size equals "Youth Large" or "Adult Large", right? If the record you are displaying is "adult" then you print in black and if it is "youth" then you print in red. From the link it looks like this is working. Is it not?

Jared

[printedgoods]

So you are currently pulling up all the records where size equals "Youth Large" or "Adult Large", right? If the record you are displaying is "adult" then you print in black and if it is "youth" then you print in red. From the link it looks like this is working. Is it not?

Jared

Thanks for the reply Jared.

About the query, i was letting Access build it and you know how it loves to change the query to be like that with all the "{{[((" and stuff.....

It does display the query correctly. What i want to show (if possible to do in the query) is:

1) pull the data
2) If "youth" and "adult" show up for the same "color" then display in red
3) else -- if only "adult" then display in black


Thanks for your help

Jason

[jhardman]

Jason,

Are the only two possibilities that it is 1- only available in adult or 2- available in both adult or youth?

If that is right, I would add to the end of the query: " ORDER BY color ASC, size DESC", then when I loop through the records if the record is adult, I display in black and continue. If the record is youth, I display in red and skip the next record (it will be the same, but in adult) before I continue. Does this make sense? It might look something like this

do until RS.eof
   if rs("size") = "Adult Large" then
      'display in black, whatever your code looked like here
   else
      'display in red
      RS.movenext
   end if
 
   'whatever else you do per record
   RS.movenext
loop

Let me know if this helps.

Jared

[printedgoods]

thanks that worked like a charm. Check out the page now.

Now onto the next thing.......