Bettter design for collecting information to turn into a result

[MrMode]

I need to produce a simple 'table' - (query output) like the one below:
Entries Prizes
No. Entries | 1st | 2nd | 3rd | 4th | 5th
1 to 3 | $10 | $7.50 | $5.00 | $0 | $0
4 to 6 | $10 | $7.50 | $5.00 | $3.50 | $0
7 to 9 | $10 | $7.50 | $5.00 | $3.50 | $1

I have 3 tables:

tblPrizeScheme - this collects the name of the scheme and the references to the tblPrizeValues and tblPrizePlaces:
PrizeSchemeID | PrizeValueID | PrizePlaceID
127 | 1 | 1
128 | 2 | 2

tblPrizeValues - Collects the amounts of the prizes by position e.g.
PrizeValuesID | 1st | 2nd | 3rd | 4th | 5th
1 | $15 | $10 | $5.00 | $2.50 | $0
2 | $10 | $7.50| $5.00 | $3.50 | $1

tblPrizePlaces - collects the number of entries that trigger prizes, each field is a place, e.g.
PrizePlaceID | 1st | 2nd | 3rd | 4th | 5th
1 | 1 | 1 | 1 | 3 | 6
2 | 1 | 1 | 1 | 4 | 7


When queried, if I link the tables together, it produces a result like this:
PrizeSchemeID | PrizePlace.1st | PrizeValue 1st | PrizePlace.1st | PrizeValue 1st
127 | 1 | $15 | 1 | $10 etc...

There are several issues with this approach. First, I have no idea how to pivot the information to produce the 'table' at the start of this thread, secondly I was wondering if there was a more simple and effective way to solve the collection and presentation of the information?

Any suggestions on how I use the information as I have already collected it, or a better approach would be most appreciated

 

[Andrzejek]

To quote another TT user:

You've been around long enough to have noticed the TGML markup, that would make the examples more understandable...

Entries Prizes[pre]
No. Entries | 1st | 2nd | 3rd | 4th | 5th
1 to 3 | $10 | $7.50 | $5.00 | $0 | $0
4 to 6 | $10 | $7.50 | $5.00 | $3.50 | $0
7 to 9 | $10 | $7.50 | $5.00 | $3.50 | $1[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MrMode]

Ah!
Thanks Andrzejek
Never used them before. Thanks for pointing it out, I was wondering how to make it look reasonable

I need to produce a simple 'table' - (query output) like the one below:

Entries vertical the right hand column, Prizes horizontal in each row.

No. Entries | 1st [tab]| 2nd [tab][tab]| 3rd [tab][tab]| 4th [tab][tab]| 5th
1 to 3 [tab][tab]| $10 [tab]| $7.50 [tab]| $5.00 [tab]| $0 [tab][tab]| $0
4 to 6 [tab][tab]| $10 [tab]| $7.50 [tab]| $5.00 [tab]| $3.50 [tab]| $0
7 to 9 [tab][tab]| $10 [tab]| $7.50 [tab]| $5.00 [tab]| $3.50 [tab]| $1

I have 3 tables:

tblPrizeScheme - this collects the name of the scheme and the references to the tblPrizeValues and tblPrizePlaces:
PrizeSchemeID | PrizeValueID | PrizePlaceID
127 [tab][tab][tab][tab]| 1 [tab][tab][tab][tab]| 1
128 [tab][tab][tab][tab]| 2 [tab][tab][tab][tab]| 2

tblPrizeValues - Collects the amounts of the prizes by position e.g.
PrizeValuesID [tab]| 1st [tab]| 2nd [tab]| 3rd [tab]| 4th [tab]| 5th
1 [tab][tab][tab][tab][tab]| $15 [tab]| $10 [tab]| $5.00 [tab]| $2.50 [tab]| $0
2 [tab][tab][tab][tab][tab]| $10 [tab]| $7.50[tab]| $5.00 [tab]| $3.50 [tab]| $1

tblPrizePlaces - collects the number of entries that trigger prizes, each field is a place, e.g.
PrizePlaceID | 1st | 2nd | 3rd | 4th | 5th
1 [tab][tab][tab][tab]| 1 [tab]| 1 [tab]| 1 [tab]| 3 [tab]| 6
2 [tab][tab][tab][tab]| 1 [tab]| 1 [tab]| 1 [tab]| 4 [tab]| 7


When queried, if I link the tables together, it produces a result like this:
PrizeSchemeID | PrizePlace.1st | PrizeValue 1st | PrizePlace.1st | PrizeValue 1st
127 [tab][tab][tab][tab]| 1 [tab][tab][tab][tab][tab] $15 [tab][tab][tab] 1 [tab][tab][tab][tab][tab][tab]$10 etc...

There are several issues with this approach. First, I have no idea how to pivot the information to produce the 'table' at the start of this thread, secondly I was wondering if there was a more simple and effective way to solve the collection and presentation of the information?

Any suggestions on how I use the information as I have already collected it, or a better approach would be most appreciated

 

[Darrylles]

MrMode,

Firstly: look-up 'database normalisation' and redesign your tables.
(Each one of 1st, 2nd, 3rd, 4th, 5th fields, should be in separate, single, individual records - for both tblPrizeValues and tblPrizePlaces).

Secondly: FORGET about positioning the fields in a query - it's irrelevant, as long as it draws the correct rows.
'Prettifying' is easy (and the very last step).

Thirdly: Is your table/field naming convention (and thus logic) correct?
Do you really get less reward for more entries?
E.g. 1 entry gets you 1st, 2nd or 3rd prize whereas more than one entry penalises you and you get 4th or 5th.

I started to define your new tables, but then got confused regarding your 'number of entries' business rule, which sort-of implies that you don't need both values AND place tables - just one table.

Also, what is a PrizeScheme? How is it defined? It seems like if it's valueid is 1, then it's placeid is 1, ditto for 2.
Will PrizeValueID ALWAYS = PrizePlaceID?

The business logic needs to be clarified to help you further.

ATB,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.