Using a Form to query data and enter a price into a data table (1 Viewer)

JahJr · Aug 20, 2009

There is a sample db attached. I have a table with the following columns;
Sale#
Type
Model
Price
Date

I have a form with 3 combo boxes;
Date
Type
Model

1 Text Box
Price

Command button
Enter Price

The combo boxes should not have duplicates in them and should narrow selection base on box aboves selection. Once you have all 3 combo boxes selected you can enter a price and it will only go to the price column but only in the rows that matche the information from the combo boxes.

John Big Booty · Aug 20, 2009

Have a look at Cascading Combo Boxes

JahJr · Aug 20, 2009

I have updated the db. The first 2 combo boxes work correctly but the 3rd one is not filtering the results correctly. The 3rd combo box should only show results that match both combo box 1 and 2.

I have also tried to tinker with the text box. The data that is entered into the text box needs to be entered into ONLY the cells that match the criteria from all 3 combo boxes.

John Big Booty · Aug 21, 2009

You seem to have got a pretty good handle on cascading combo boxes, however you will need to insert some code in your form to requery the combos when;

Any one of them changes
When you first open the Form
When you move to a new record

The next thing your going to have to do is read up on Normalisation, from there you will need to normalise your data. Once you have done that see if you can move your project forward from there.

Once you've done that, post back HERE if you are still having trouble

JahJr · Aug 21, 2009

I have no idea where to go with the code. As for the normalization, I went and read about it and I think I understand, but i am in way over my head with what I am working on. Breif explanation if you do not know about sports betting. You have 2 main types of bets Over/Under and Line. Over/Under is a bet where the line might be 50 and you pick weather the total score will be over or under that line. The other bet is a line bet where the line is + or - a number. Jets are +7 that means the Jets have to win by more then 7 for you to win if you bet on the jets. You also have 2, 3 and 4 game parlays which are any combination of the above mentioned bets where you have to win all of the bets involved to win the bet. Please look at data below and let me know how you would normalize this data. Each item below is a column in the db.
Bet# (key)-Customer#-Date-MasterBetType-Bet Type1-Line1-Over1-Under1-Sport1-Team1-Score1-OpScore1-TotalScore1-Juice-BetType2-Line2-Over2-Under2-Sport2-Team2-Score2-OpScore2-TotalScore2-BetType3-Line3-Over3-Under3-Sport3-Team3-Score3-OpScore3-TotalScore3-BetType4-Line4-Over4-Under4-Sport4-Team4-Score4-OpScore4-TotalScore4

John Big Booty · Aug 21, 2009

Now you've really lost me

How in the name of all that's holly does any of that relate to the sample you've posted

JahJr · Aug 22, 2009

I have learned from other post that I have made that some people are against gambleing.
So the relation is that 1 of the things i need my db to do is similar to the other post if you replace type with sport and model with team and price with score.
Now that the cat is out of the bag maybe you will be able to help me.

Thanks in advance.

John Big Booty · Aug 22, 2009

OK. I see.

Any way as an example see the attached, it should give you an idea on how to normalise your data. See if that gives you some ideas. Then come back and let me know how you got on, and we can move forward from there.

JahJr · Aug 22, 2009

I'm getting a better understanding I would like to send you the real db if possible. I see where everything has a ID number associated with it and that is what is being passed around. Still a little confused. Currently there are 5 tables.
Sport
Teams
BetType
Customers
LoggedBets
I think the data is normalized on the easy part which is just the sport and teams table. Where I get lost is the LoggedBets.
Thanks

John Big Booty · Aug 22, 2009

JahJr said:
I'm getting a better understanding I would like to send you the real db if possible. I see where everything has a ID number associated with it and that is what is being passed around. ...

BINGO

Your tables should look something like

TBL_Sports
SportID PK
Sport

TBL_Team
TeamID PK
Team

TBL_BetType
BetID PK
Bet

TBL_Customer
CutomerID PK
Customer

TBL_LoggedBets
LogBetID PK
SportID FK
TeamID FK
BetID FK
CustomerID FK
BetAmount

That would be the bare minimum for each of those tables.

Have a play with that then post back and let me know how you get on. Post a copy of you DB if you like.

JahJr · Aug 23, 2009

That is pretty much how i had it structured. Here is the problem. Lets say a 3 game parlay is placed. The bet would look something like this.
SPORT TEAM BetType LINE OVER UNDER
NBA __Bulls __Line ___-7
NFL __Jets __Over _______44
NFL _Giants _Under _____________39

For the customer to win the bet he would have to win all three of these games. How would you recommend logging this data. I currently have it all in 1 Row as, Sport1, Team1, Line1, Over1, Under1, Sport2, Line2, Over2, Under2 and so on. Your breakdown of PK and FK is helping shine some light on the situation but really stuck here. When the bets are currently logged the first column is Bet# PK maybe you could seperate each of these into there on individual row and assign them all the same Bet#. Let me know you thoughts. I'm attaching a sampledb that just has all of the tables.

John Big Booty · Aug 23, 2009

I've made a couple of changes to your DB. I've removed the table level look ups you had in one of the table, these are just more trouble than they are worth. I've added a new table, TBL_BetDtls, and a raft of forms. There is still a lot to do, you will need a form to handle editing of selected bets, amongst others that I've not even imagined.

You might also want to consider normalising state and zip code data for client addresses. You could then implement cascading combos for state and then city/zip, if you are able to get your hands on that data easily.

JahJr · Aug 23, 2009

Attached is what I have completed so far. I'm affraid that I have only done the easy part. I can log all of the data. But I need a way after the game to enter the scores so the db can figure the winners and losers. My thoughts would be a form that would show you all of sport and teams that don't have score posted beside them. There would be two txt boxes by each team returned, 1 for team score, 1 for OpTeam Score. I would need to make sure this would not write over any pre existing scores. The db would calculate the total score and I think I can use if then statements to calculate the winners. Please remember I am not a programmer and I am learning on the fly.

John Big Booty · Aug 23, 2009

You will need to restructure your tables a little.

Firstly create a new table, called; say,

TBL_Fixture
GameID (PK)
HomeTeamID (FK)
AwayTeamID (FK)
MatchDate
HomeTeamResult
AwayTeamResult

You will also need to restructure the Bet details table

TBL_BetDtls
BetID (FK)
BetTypeID (FK)
GameID (FK)
HomeTeamScore
AwayTeamScore
BetAmount

From there you will need to make some changes to the forms, but given what I've already shown you that should be a snap. You can compare the current date with the match date, and filter out games that have already taken place show that they do not show in the Bet add form.

JahJr · Aug 23, 2009

1. I was hoping to not keep track of who the oposing team is.
2. I'm confused as to where this is going the way you are showing me. Can it not be done the way I described. Search table for all teams that do not have a score in there score column. Then update the table with the appropriate scores.

John Big Booty · Aug 23, 2009

Ultimately what I have suggested, comes from a position of ignorance regarding the gaming industry. So it's really going to be up to you to take what I have shown you, break it down and rebuild it to suit your needs. Bare in mind the principals of normalisation. Also remember that calculated values should not be stored.

JahJr · Aug 24, 2009

I understand that calculated values should not be stored and I think that I have my data as normalized as i need it to be for what I am trying to do. My big problem is how to enter the scores into the table after the bet has been entered and the game has been played. The simplist way I can think of is a query that will search the logged bets table for all team names that do not have a score posted next to it already. I could go back in and edit every bet like you suggested but. There might be 100 different bets on 1 team in 1 game. Therefore I need a easy way to enter the scores into the logged bets table. please let me know if you have any suggestions or can point me somewhere that does. Thanks

John Big Booty · Aug 24, 2009

You are going to need a table to record your bets (which we've already dealt with, although it's structure may need tweaking). You will also need to have a table to record the results of all the various games.

Once you have that sorted, you will need to create a query/s that compares those two tables and can then give you a report that shows the winning bets and I would guess the appropriate pay outs.

Given that you have about half a dozen (from memory) betting formats, the query/s that generate this report are going to be the tricky bit, and ultimately how difficult it is will be, depend largely on your table structures, so think carefully about it, and be prepared to change things.

Good luck and have fun

Using a Form to query data and enter a price into a data table (1 Viewer)

Andy

Attachments

AWF VIP

Andy

Attachments

AWF VIP

Andy

AWF VIP

Andy

AWF VIP

Attachments

Andy

AWF VIP

Andy

Attachments

AWF VIP

Attachments

Andy

Attachments

AWF VIP

Andy

AWF VIP

Andy

AWF VIP

Similar threads

Users who are viewing this thread