INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Bettter design for collecting information to turn into a result

Bettter design for collecting information to turn into a result

(OP)
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

RE: Bettter design for collecting information to turn into a result

To quote another TT user:

Quote:

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

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 

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.

RE: Bettter design for collecting information to turn into a result

(OP)
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     | 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

RE: Bettter design for collecting information to turn into a result

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close