Too Many Indexes

[cbrsix]

I created a table and when I tried to save it, I got an error saying it was unable to save it because there are too many indexes in the table. When I checked the indexes, I only see 1. All of the fields are look up fields that have 2 choices that allow multiple values. Attached are pictures of the error messages and the indexed table. Any ideas?

[RayMilhon]

Could you explain your table definition a little. It looks more like data then fields

[cbrsix]

This table is going to be used to hold data that is a part of a subform that I want to add to a form that already exists. Attached shows how each lookup field is set up.

[RayMilhon]

Not exactly what I was looking for Let me ask a more specific Question.
Based on your Data Structure Each Representative will have some value for every State in the US. Is this what you intend?

[cbrsix]

Yes, they will either be listed as AG or RA

[RayMilhon]

Ok, then in the design view at the top there is a button that lists all of the indexes. I can see that you only have the ID as the Primary but check the index list make sure it's the only index.

[RayMilhon]

Never mind missed the middle image in your original post.

[RayMilhon]

Sorry, don't have a clue.

[cbrsix]

I've been doing a little reading and it seems that all of my combo boxes create hidden indexes. I can't find how to get rid of them. Any ideas?

[RayMilhon]

That's a new one on me. I wasn't aware that they did that.

[boblarson]

First off I will state that your table is NOT a good setup. Those should be individual records, not columns.

Get the table structure right first and THEN we can address the issue of getting the form to a way that you would like.

The correct table structure would be

tblStateReps
StateRepID - Autonumber(PK)
StateID - Long Integer (FK)
RepID - LongInteger(FK)
RepTypeID - LongInteger (FK)

tblRepTypes
RepTypeID - Autonumber (PK)
RepType - Text

And then you would have a record for each rep for each state.

[cbrsix]

Why would that be the correct set up?

[cbrsix]

That table structure you recommend doesn't look like it'll accomplish what I need. All I want to happen is to have this table be used for a subform in the Reps main form and I need to know whether they are listed as AG and/or RA (combo box - multivalue) in a specific state. My way seemed to be the easiest and most straightforward way to accomplish my goal.

[boblarson]

My way seemed to be the easiest and most straightforward way to accomplish my goal.

And while it would APPEAR that way to the untrained eye. Those of us who have been working with relational databases (in my case for 14 years) are aware of the nightmare you face when you want to get any meaningful data back out.

If you are just putting it in the form to look a specific way and are not planning on ever USING that data for other purposes then by all means you can go with your design. But if you are ever going to want to know anything like "which reps are AG in which states?", "which reps are RA in which states?" then my suggestion would be the way to go.

The thing to realize is this. Making things easy on yourself for development usually has a direct correlation in that the easier you make it on yourself for developing usually means more work for the end user in the long run. Or more work for yourself in other areas.

We can almost ALWAYS create a form to look like you want (even your original design) just by using some code to move data between the normalized and the not normalized display. But if you set it up non-normalized then you, and potentially others, are going to suffer for that.

[cbrsix]

If you are just putting it in the form to look a specific way and are not planning on ever USING that data for other purposes then by all means you can go with your design. But if you are ever going to want to know anything like "which reps are AG in which states?", "which reps are RA in which states?" then my suggestion would be the way to go.

Wouldn't be able to just run query from that table to see this specific information?