Access Relationships and ADO Bound Controls

[evoluder]

Hello,

I am setting up a datagrid in a Vb6 application, its datasource is an ADO recordset that I am creating
from a SQL query. For example, consider the following table layout in an Access database:

tblClothes

ClothesID: 1 Jacket: 2 Pants: 3 Shirt: 2
ClothesID: 2 Jacket: 1 Pants: 2 Shirt: 3

The three clothing fields store an integer value that relates back to the primary key on the color table:

tblColor

ColorID: 1 ColorName: Red
ColorID: 2 ColorName: Black
ColorID: 3 ColorName: Yellow

Now lets say I want to create a recordset that includes the information above but is displayed as below when
the datagrid is set to use the ADO recordset as its datasource:

dgClothes

ClothesID Jacket Pants Shirt

1 Black Yellow Black
2 Red Black Yellow

The database shows the relationships and the form view in Access displays the relationships correctly (the fields for each clothing type has a drop down that displays all of the color names.) However, I am not sure how to get a SQL query to return ColorName three times and have it correspond to the clothing type, is it possible to do this with SQL? It seems strange to do a JOIN on three fields when they all return the same field "ColorName." It doesn't seem like that would work...am I thinking about
this in the correct way? Or should I build a couple of recordsets, one for clothes and one for colors, build an array out of colors and then build another recordset linking the two together? That seems to negate the benefit of the relationships in the db... I am hoping to have this datagrid be a bound control so that the user can edit the fields and update the db as needed. I get the
feeling that I am not conceptualizing this correctly...

Thank you for any assistance.

 

[braindead2]

I don't think the design of tblClothes appears to be normalized. Because I am not 100% sure what you are doing it could be, but I think it should look more like.

tblColor
ColorID: 1 ColorName: Red
ColorID: 2 ColorName: Black
ColorID: 3 ColorName: Yellow

tbltype
typeid:1 typename: jacket
typeid:2 typename: shirt
typeid:2 typename: pants

tblClothes

ClothesID: 1 colorid:1 typeid:1
ClothesID: 1 colorid:2 typeid:2

This way you can add a tie, shoes and socks to the outfit later if needed

granted your grid would look more like

1 jacket black
1 shirt black
1 pants yellow

unless you transform the data

good luck

 

[evoluder]

What you are saying is true. The issue I am confused about is how do I build my ADO recordset in such a way that I display:

1 Jacket Black
2 Shirt Black
3 Pants Yellow

Rather than displaying the IDs:

1 1 2
2 2 2
3 3 3

Does that make sense?

Thanks.

 

[braindead2]

strsql ="SELECT clothestbl.id, colortbl.color, typetbl.type
FROM (clothestbl INNER JOIN colortbl ON clothestbl.colorid = colortbl.colorid) INNER JOIN typetbl ON clothestbl.typeid = typetbl.typeid;"

 

[evoluder]

I messed up. I need to clarify: In my first example, the clothes types were field Names rather than values. So each row would simply be numbers. I am having trouble selecting multiple color values for the row.

Example tbl:

Column Headings: ClothesID Jacket Shirt Pants
Field Values: 1 2 2 3

Where the Jacket, Shirt, and Pants values relate to the tblColor color ID. I want to display the colornames alongside the ClothesID in my datagrid.

If I do the following:

SELECT tblClothes.ClothesID, tblColor.ColorName
FROM tblClothes INNER JOIN tbleColor ON tblClothes.Jacket = tblColor.ColorID

I get the correct color back, so my Grid would look like the following:

ClothesID ColorName
1 Black

But what happens when I want to show the colors for all three clothing types? Let's say I want to display it like thus:

Column Headers: ClothesID Jacket Shirt Pants
Field Values: 1 Black Black Red

If I try:

SELECT tblClothes.ClothesID, tblColor.ColorName AS Jacket, tblColor.ColorName AS Shirt
FROM (tblClothes INNER JOIN tblColor ON tblClothes.Jacket = tblColor.ColorID) INNER JOIN tblColor ON tblClothes.Shirt = tblColor.ColorID

I get an error: "Join not supported." It seems like there should be a good way to do this, but I am not getting it for some reason. It doesn't seem that the statement above would work, but I am not sure where to go. Any additional help is greatly appreciated.

Thank you.

 

[braindead2]

strsql = SELECT clothestbl.id, colortbl.color, (select color from colortbl where colorid = [shirt]) AS shirtcolor, (select color from colortbl where colorid = [pants]) AS pantcolor
FROM clothestbl INNER JOIN colortbl ON clothestbl.jacket = colortbl.colorid;

 

[evoluder]

I figured out how to get what I was after. The solution was simpler than I anticipated:

SELECT ClothesID, (SELECT ColorName FROM tblColor WHERE tblColor.ColorID = tblClothes.Jacket) AS Jacket, (and so on...)
FROM tblClothes, tblColor

It seems to work correctly, hopefully I won't run into any problems.

Thanks again for all of your time and suggestions.

Regards.