In my limited experience with Access, I learned that Auto Numbers were "essential" to a database. I don't necessarily agree with that info today, I would say that the Primary Key for this database is a composite of RaceDate and RaceTime. Any duplicates to that are where there is more than one bet in the same race, which happens often but not always.
Running Totals
- Thread starter davegoodo
- Start date
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
Code:
I would say that the Primary Key for this database is a composite of RaceDate and RaceTime. Any duplicates to that are where there is more than one bet in the same race, which happens often but not always.1. What you just said completely contradicts yourself.
If you can have two bets in the same race that means you will have two records with same Date and Time. Therefore impossible to make Date and Time a composite key. Or at least impossible to enter 2 records for a given race. I would forget about a composite key because it would require at least 3 fields in this case. I would simply make the PK an autonumber.
See thread 46.
Running Totals
@arnelgp There is no field called Date? It is called RaceDate.
www.access-programmers.co.uk
No, that is also incorrect. A good primary key is essential. Autonumbers will always make good primary keys, but are not essential or the only good choice for PKs. A PK needs to be unique, non-changing (or at least rarely), simple (no special characters, spaces, etc.), and known at the time the record is entered.
3.
Out of curiosity what part do you disagree with? I can again assure you are mistaken. The information I presented is accurate.
4. The PK and the solution to the query are two different things.
Did you even read what I wrote? If the assumptions I presented are possibly true, then query1 will fail. If and only if records are entered into the database always in the correct order will it work. Is that the case? You never are going to miss a single entry and have to go back and enter it?
But if you can have two bets in a race, and records could possibly be entered out of order, then the solution is this.
Code:
SELECT transaction.*,
(SELECT SUM(winlose)
FROM transaction AS T
WHERE ( T.racedate + T.racetime ) <= (
transaction.racedate + transaction.racetime )
AND T.transid <= transaction.transid) AS RunningTotal
FROM [transaction]
ORDER BY transaction.racedate,
transaction.racetime, transID;
Last edited:
GPGeorge
George Hepworth
- Local time
- Today, 15:05
- Joined
- Nov 25, 2004
- Messages
- 3,210
Two different things.
AutoNumbers are good for one thing only. They provide unique values that make ideal Primary Keys.
The other issue is being able to create a unique index on two or more fields. RaceDate and RaceTime would be a candidate for that index.
You could enforce a unique combination of RaceDate and RaceTime on those two fields without having to use it as the Primary Key.
Many a lively debate has been sparked over the choice of Surrogate keys (i.e. non-meaningful values such as those provided by the AutoNumber) or Natural Keys (i.e. your two field composite RaceDate and RaceTime). You can make either one the Primary Key in a table.
I am firmly in the surrogate key camp, but many highly skilled database developers prefer Natural Keys. Your call.
The important thing here is that your need is for a reliable way to sort records by date and time. That's not what a Surrogate Key like an AutoNumber is good at. However, that doesn't mean abandoning the AutoNumber, which is --in my opinion-- superior in other ways.
GPGeorge
George Hepworth
- Local time
- Today, 15:05
- Joined
- Nov 25, 2004
- Messages
- 3,210
Hm. As MajP pointed out, you can't actually enforce a unique combination of RaceDate and RaceTime, so that isn't even an option. You could still create a composite index, though, which would be non-unique, for sorting.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
Actually, my mistake.
If you can have two bets against the same race then this table is not normalized. You need a
tblRace
RacedID (I make it an autonumber and the PK)
. Information unique to a race which is most of the current information
tblTransactions
TransID (I make it an autonumber and the PK)
RaceID_FK
.information unique to the bet
(Stake, dividend, win lose)
So you cannot have two bets on the same race in the same table, because that information belongs in a seperate table.
If you can have two bets against the same race then this table is not normalized. You need a
tblRace
RacedID (I make it an autonumber and the PK)
. Information unique to a race which is most of the current information
tblTransactions
TransID (I make it an autonumber and the PK)
RaceID_FK
.information unique to the bet
(Stake, dividend, win lose)
So you cannot have two bets on the same race in the same table, because that information belongs in a seperate table.
As pointed out above, there are 3 stages to my betting process:
1) There is meeting information to be entered which happens 2 days prior to RaceDate when the official fields are released. These are RaceDate, Meeting, RaceTime, RaceNum and Distance. Just these 5 fields initially.
2) Next there is the process of placing bets (this is where a Stake is placed on 1 or more horses (a split stake for multiple bets in the same race) for the "next" race, where Result, Dividend and WinLose and RunningTotal info are not known. This is where the Rating, Plus1, Plus2 and Clarity info is used as well as TAB and HorseName. All these fields should appear on the (b) Betting form (below) as well as the Meeting fields shown above.
3) The race is run and decided (Win or Lose) and the final missing info goes into the fields listed in step 2 above. And a RunningTotal is known.
I now realise I need a proper Meetings form to add the info in step 1. I also need a form to enter any bets I may have on a given race, meaning I don't bet on every race listed. There may also be a form needed for entering the results of a given race, (When "correct weight" is given for the race in question). So I'm hoping for help with these final forms:
(a) Enter Meetings info, (b) to enter Bets, if any, (c) enter Results.
Thanks.
1) There is meeting information to be entered which happens 2 days prior to RaceDate when the official fields are released. These are RaceDate, Meeting, RaceTime, RaceNum and Distance. Just these 5 fields initially.
2) Next there is the process of placing bets (this is where a Stake is placed on 1 or more horses (a split stake for multiple bets in the same race) for the "next" race, where Result, Dividend and WinLose and RunningTotal info are not known. This is where the Rating, Plus1, Plus2 and Clarity info is used as well as TAB and HorseName. All these fields should appear on the (b) Betting form (below) as well as the Meeting fields shown above.
3) The race is run and decided (Win or Lose) and the final missing info goes into the fields listed in step 2 above. And a RunningTotal is known.
I now realise I need a proper Meetings form to add the info in step 1. I also need a form to enter any bets I may have on a given race, meaning I don't bet on every race listed. There may also be a form needed for entering the results of a given race, (When "correct weight" is given for the race in question). So I'm hoping for help with these final forms:
(a) Enter Meetings info, (b) to enter Bets, if any, (c) enter Results.
Thanks.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
How tables are structured has ZERO to do with the order you enter information. Information gets entered once and only once. The forms can look different if needed. But you do not duplicate meeting and race information. As far as I can tell there is no need for a meeting table. The information is unique to a race.
@MajP I apologise for missing some of the info you have shared. I have saved the code you put up, thanks for that. I'm not sure if I should scrap the Meetings table.
My reason for having it is I don't ultimately bet on every race, only certain races (which I don't know about 2 days before race day). If I have a Meetings table I could link a form to it to load any race info needed to the Transactions table on race day. To load all the meetings info to the Transactions table would cause unnecessary clutter to the database.
My reason for having it is I don't ultimately bet on every race, only certain races (which I don't know about 2 days before race day). If I have a Meetings table I could link a form to it to load any race info needed to the Transactions table on race day. To load all the meetings info to the Transactions table would cause unnecessary clutter to the database.
@MajP I wanted to say that there are frequent/some instances where I'll be placing 2 bets in one race. There hasn't been a case where there were 3 in one race but theoretically it could happen. I just want to bed down the table structure issue and move on to getting forms built. With my novice level experience with Access I will probably need help with forms and reports.
I appreciate your enthusiasm and ideas. The RaceDate and the RaceTime are important to the order and I need to have a possibility for multiple bets in one race which thwarts the idea of these two fields forming a composite key. I'm not sure what the final solution is.
I appreciate your enthusiasm and ideas. The RaceDate and the RaceTime are important to the order and I need to have a possibility for multiple bets in one race which thwarts the idea of these two fields forming a composite key. I'm not sure what the final solution is.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
However, I do know the solution. That is a properly normalized database.
You need the following tables
tblMeetings
MeetingID PK autonumber
tblRaces
RaceID PK autonumber
MeetingID_FK 'relates to meetings
tblRaceTransactions
TransID PK autonumber
RaceID_FK ' relates to a race
tblMeetings tblMeetings
| MeetingID | RaceDate | MeetingLocation |
|---|---|---|
1 | 12/16/2023 | Caulfield |
2 | 12/16/2023 | Randwick |
3 | 12/23/2023 | Moonee Valley |
4 | 12/23/2023 | Randwick |
5 | 1/6/2024 | Geelong |
6 | 1/6/2024 | Randwick |
7 | 1/13/2024 | Flemington |
8 | 1/13/2024 | Rosehill |
tblRaces tblRaces
| RaceID | MeetingID_FK | RaceTime | RaceNum | Distance |
|---|---|---|---|---|
1 | 1 | 1:20:00 PM | 3 | 1600 |
2 | 1 | 1:55:00 PM | 4 | 1400 |
3 | 1 | 2:30:00 PM | 5 | 1200 |
4 | 1 | 4:20:00 PM | 8 | 1400 |
5 | 1 | 5:00:00 PM | 9 | 1400 |
6 | 2 | 1:05:00 PM | 2 | 1600 |
7 | 2 | 1:40:00 PM | 3 | 1400 |
8 | 2 | 3:25:00 PM | 6 | 1100 |
9 | 2 | 4:00:00 PM | 7 | 1600 |
10 | 2 | 4:40:00 PM | 8 | 1400 |
tblRaceTransactions tblRaceTransactions (top 10)
| TransID | RaceID_FK | Tab | HorseName | Rating | Plus1 | Plus2 | Clarity | Stake | Result | Dividend | WinLose |
|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 28 | 4 | Tympanist | 1 | o | Avg | $20.00 | 2nd | | ($20.00) | |
2 | 1 | 5 | Fullproof | 1 | o | Avg | $20.00 | Unp | | ($20.00) | |
3 | 29 | 10 | Zouphoria | 1 | oo | Avg | $20.00 | Won | $2.60 | $32.00 | |
4 | 23 | 8 | Running By | 1 | oo | Avg | $20.00 | Won | $1.70 | $14.00 | |
5 | 3 | 1 | Is It Me | 1 | oo | Avg | $20.00 | Won | $2.90 | $38.00 | |
6 | 19 | 10 | Boston Rocks | 1 | o | Avg | $20.00 | Won | $2.70 | $34.00 | |
7 | 30 | 11 | Grebeni | 1 | oo | Avg | $20.00 | Won | $4.50 | $70.00 | |
8 | 15 | 8 | He's Handsome | 1 | o | Avg | $20.00 | Unp | | ($20.00) | |
9 | 31 | 8 | Plundering | 1 | o | Avg | $20.00 | 3rd | | ($20.00) | |
10 | 46 | 9 | Dubai Poet | 1 | oo | Good | $20.00 | 3rd | | ($20.00) |
Now that it is properly normalized you can build a form
At the top you add Meetings
Below that you add races
Whatever race you are clicked on you add transactions
(Notice you can have 1 or mor transactions for a race )
(you might want to add the raceID_FK on the transaction subform so you can easily see what race number it is)
The second tab shows (no edit) all the transactions for that meeting
Give it a try to see if works.
Attachments
Thank you MajP I'm knocked outt! In a good way. You've put a lot of time and effort into this it looks great!
One minor thing was I ran the query "qryRunningTotals_MajP" and it gave an error about could find transaction table. Looking at the SQL it has the name "Transaction" but I think it could be tblRaceTransactions instead. I can change the field name in the SQL. Does Access have a find and replace feature? I can do it by hand there's not many.
It looks great. I will have to implement all this in the actual database, I can probably import everything into the new database. You've done a great job. Thanks very much. I'll go and have a play with it.
I've changed the qryRunningTotals_MajP query with tblRaceTransactions. I'm running it now and its giving me the "Add Parameter"msg for T.RaceTime, I'm not sure what that is about, can you have a look at it?
I've had another look at it, I think perhaps it needs to be updated to the new structure
Done.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
The form part is not that important, it is getting a correct table structure. You need to verify it makes sense for what you are doing. I believe it is correct. Once you build the tables and logic you can build a form like this pretty quickly using the wizards.
The only real trick is linking two continuous subforms. The wizard will not help with that and you have to do this yourself.
The way that works is that in the Race subform oncurrent event it sets the red link textbox to the current RaceID. The transaction subform is then linked to that text box. You can hide that textbox if you want.
The RaceTime field is important in that it tells me what is the Next race of interest. Did that have to go from the Meetings table?
I'll take a closer look at the tables and give any feedback, may I ask how long you'll be in the forum for today?
I looked at frmMeetingsRacesTransactions the subforms don't appear to be linked at all. Is that because the tables need to be right before the forms proceed any further?
I'll take a closer look at the tables and give any feedback, may I ask how long you'll be in the forum for today?
I looked at frmMeetingsRacesTransactions the subforms don't appear to be linked at all. Is that because the tables need to be right before the forms proceed any further?
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:05
- Joined
- May 21, 2018
- Messages
- 9,951
Maybe. Is this different from a racetime in the Race table?
My guess was that a meeting represents a Race Day. A Race Day would be a location and given date for a set of races. The races are then built in the race table. Races have racetime.
If that is not correct, then what is a meeting and how does that relate to the races on that day?
Clearly the forms are all linked, but I added some conditional formatting to show the selected race. People are not as familiar looking at synched continuous subforms.
At Moonee Value on 12/23 there races 1,4,6,7,8,10
In race 10 there are two transactions for Veloce Carro, and Private Jumbo
I need to add some more information to the discussion:
1) I bet offline, I don't physically attend the race meetings. I'm betting in multiple locations on each race day. Which is why the RaceTime is so important.
2) The RaceTime field needs to order the races (along with RaceDate) available to bet, even if, on that date, they are in different locations. At present the bets are ordered to RaceTime but are ordered by the meeting location.
3) Looking at the qryAllDataWith_RunningSum the total on the final row is not correct. I think this is due to the order in which the bets are placed. The bets need to strictly ordered to the RaceDate and the RaceTime. When you were developing the query it worked fine on the sample data, the final balance was correct. In this query the balance at record 47 = -$111.75 in the next row 48 the balance jumps to +$283.75. I don't get what is happening there.
Thanks
