Working with tables relating. I have a table that has all positions of colors available for each page (32 in total) of the sample book we are going to put out showing colors that the customer selects for each position. This will be used for internal use one to keep track color selections by customer.

I have it setup so that all positions have their own fields in the table (then to the form), linked to select colors from another table. When I setup the Relationships, relating the colors to each position, I end up with 32 of the same tables (32 times) in the Relationship area. I see this as sloppy work and may not be efficient when the db is completed.

In addition, when I try to set up a query and set it up similar to the relationship, I receive a, “Query to Complex” dialog window. As of now, I am working around that by creating two separate queries splitting the positions to 16 per query.

I am assuming there must be a better solution so the db will run smoother and build the rest of the db, but I have no idea as I have never ran into this issue in the past.



http://usastray.com/usastray/uploads/color_db_relationship.jpg

Thanks,

Steve

Working with tables relating. I have a table that has all positions of colors available for each page (32 in total) of the sample book we are going to put out showing colors that the customer selects for each position. This will be used for internal use one to keep track color selections by customer.

I have it setup so that all positions have their own fields in the table (then to the form), linked to select colors from another table. When I setup the Relationships, relating the colors to each position, I end up with 32 of the same tables (32 times) in the Relationship area. I see this as sloppy work and may not be efficient when the db is completed.

In addition, when I try to set up a query and set it up similar to the relationship, I receive a, “Query to Complex” dialog window. As of now, I am working around that by creating two separate queries splitting the positions to 16 per query.

I am assuming there must be a better solution so the db will run smoother and build the rest of the db, but I have no idea as I have never ran into this issue in the past.



http://usastray.com/usastray/uploads/color_db_relationship.jpg

Thanks,

Steve

You could try this site. It may give you some ideas.
http://r937.com/relational.html

The better solution is to normalize your tables.

tbl_page:
page_ID (pk)
etc.

tbl_position:
page_ID (pk fld1, fk to tbl_page)
page_position_ID (pk fld2)
ColorNumber (fk to tbl_800_colors)

You can create a crosstab if you want to display all the positions/colors on a single row.

The better solution is to normalize your tables.

tbl_page:
page_ID (pk)
etc.

tbl_position:
page_ID (pk fld1, fk to tbl_page)
page_position_ID (pk fld2)
ColorNumber (fk to tbl_800_colors)

You can create a crosstab if you want to display all the positions/colors on a single row.

Pat,

Can you elaborate on what I had in bold in your quote? I am back to this project now.

Thanks,

Steve

He's saying to put a unique index on page_ID and page_position_ID, create a relationship between tbl_page.page_ID and tbl_position.page_ID, and create a relationship between tbl_800_colors.color_id and tbl_position.ColorNumber.

He's saying..
George -

Just a quick FYI for you for future reference (sorry to embarass you) but Pat is a woman. :)

He's saying to put a unique index on page_ID and page_position_ID, create a relationship between tbl_page.page_ID and tbl_position.page_ID, and create a relationship between tbl_800_colors.color_id and tbl_position.ColorNumber.

That is what I thought, just wasn't clear on the terminolgy. Thanks George and Pat.

George -

Just a quick FYI for you for future reference (sorry to embarass you) but Pat is a woman. :)

LOL...thanks for the update!

Sorry, Pat. I'll never make that mistake again.

OK, after thinking about this overnight, I am thinking I may going about this with the wrong approach. What is the best solution/setup for creating a Form where I am able to select a color for each position that will end up in the record of the table? What I am trying to do here, is to be able to select all the color (that a customer selects for their personalized color sample book) where as when the color number is selected, the color image will also appear. The color image I have already figured out (or so I think, uggg).

Thanks,

Steve

http://usastray.com/usastray/uploads/form_layout.jpg

LOL...thanks for the update!

Sorry, Pat. I'll never make that mistake again.

Meaning he (George) has it down pat :rolleyes:

NP George:) it's a common mistake that I never correct.

skilche1,
You are caught on the horns of a common delema. Your unnormalized structure was causing you a problem so I suggested the normalized approach. However, the normalized structure does not lend itself to the layout you desire. You want the layout to look like a grid (crosstab) which is not updateable. So your choices are
1. Normalized structure which will make querying and coding simple but will not work with this form layout unless you use an unbound form which will be tedious but doable if you use the tag property of the controls to help you create control "arrays". VBA does not natively support control arrays as VB does.
2. Stick with the unnormalized structure which will make this form simpler but everything else harder.

I say door number 1.

Is there a reason skilche1 couldn't achieve this with 24 subforms?

I haven't tried it, just wondering.

Is there a reason skilche1 couldn't achieve this with 24 subforms?

I haven't tried it, just wondering.

OK, after playing with around a couple of ideas, this is what I came up and it seems to work well. I split the color tables into two categories. This was done because when I created a query and includes all positions with associated link to the color image (48 fields in total), I got an error of “Too Complex”. So, by splitting into two, I was able to get it to function to how I needed. The downfall is that I needed to do the same for the query and forms and will need to the same when I generate the report. It seems to work well. I also kept the Relationship simple too. Again, still seems to work. NOTE IMAGES BELOW (I wanted to include them for others to see an example as this is a place for people to seek information/help.) :cool:

Anyone have any suggestions?

FORM:
http://usastray.com/usastray/uploads/CustomerColors.jpg

RELATIONSHIP:
http://usastray.com/usastray/uploads/Relationship.jpg

Actually George, the 24 subforms might work fine. I would try that before resorting to an unbound form.

Actually George, the 24 subforms might work fine. I would try that before resorting to an unbound form.

That's what I was thinking. But, I must say, skilche1 has designed a really nice looking screen. I'm glad I got to see it before the thread was closed.

Thanks for the kind words George. :)