Replacing Data

[mikesal57]

Thank you for doing that

your welcome.....looking to getting many questions answered here

mike

[Micron]

I hear you and I'm sorry....

I started by Googleing Access Forums .....these 2 came up.......not knowing which one would satisfy me....I did post in both....
But after all the responses , I set my tack here...

Mike

Don't know if you care, but that's enough for me to take you off of my ignore list. I saw a lack of concern when you were called out for cross posting, plus there was a comment at UA about that being the 'better' place (IIRC). Moke123 is quite right - but sometimes the help effort goes over several days even though this is a non-paying gig. Common courtesy can get you a lot of free help; I have worked on large issues for days when the OP has some to spare.

So maybe the approach is to continue researching db normalization - as long as you have a good understanding of the business that the db is supposed to support. If you don't have that, you have no chance, same as if one of us who knows the concept has no understanding of the business. Try these,

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

and/or keep looking until you find something that makes that light go on. You might then need to write a treatise on the business and what you propose as the table schema as a new post and get feedback. I suggest you read these also when you have conquered normalization. These will help you avoid a LOT of the common pitfalls:

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[mikesal57]

Don't know if you care, but that's enough for me to take you off of my ignore list. I saw a lack of concern when you were called out for cross posting, plus there was a comment at UA about that being the 'better' place (IIRC). Moke123 is quite right - but sometimes the help effort goes over several days even though this is a non-paying gig. Common courtesy can get you a lot of free help; I have worked on large issues for days when the OP has some to spare.

So maybe the approach is to continue researching db normalization - as long as you have a good understanding of the business that the db is supposed to support. If you don't have that, you have no chance, same as if one of us who knows the concept has no understanding of the business. Try these,

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

and/or keep looking until you find something that makes that light go on. You might then need to write a treatise on the business and what you propose as the table schema as a new post and get feedback. I suggest you read these also when you have conquered normalization. These will help you avoid a LOT of the common pitfalls:

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

WOW...that should keep me busy a long time
and out of peoples hair...

thxs

[moke123]

I wont pretend to understand your data or what you're trying to do but I can give a psuedo example of normalization when it comes to repeating fields.
You have 37 fields such as nPos1, nPos2, nPos3, etc. Looking at your sample data the majority of these fields appear empty.

If you used another table, related by a field call it BetID. You could then have a tall, thin table with a structure like:

nPosID (PrimaryKey)	BetID (Foreign Key)	nPos	nPosValue
1	1	1	1
2	1	4	1
3	1	9	1

No null fields. All the same information is there.

You'd probably be able to write simple functions to return the data you want rather than complicated queries.

[mikesal57]

I wont pretend to understand your data or what you're trying to do but I can give a psuedo example of normalization when it comes to repeating fields.
You have 37 fields such as nPos1, nPos2, nPos3, etc. Looking at your sample data the majority of these fields appear empty.

If you used another table, related by a field call it BetID. You could then have a tall, thin table with a structure like:

nPosID (PrimaryKey)	BetID (Foreign Key)	nPos	nPosValue
1	1	1	1
2	1	4	1
3	1	9	1

No null fields. All the same information is there.

You'd probably be able to write simple functions to return the data you want rather than complicated queries.

looks promising....thxs...I'll look into that
mike