from excel to access - a specific question

G

Guest

I’ve just started using access in literally the last few days as I realised
that after painfully learning how to do all this in excel, it seems the
extravagant formulas I created in the cells take up far too much memory
rendering the worksheet pretty much useless. So now I’m moving all my work
across to access and starting with a program I know very little about. I have
something quite specific I want to do but am not sure the best way to go
about it, I’ve been learning bits and pieces about queries, how to operate
them but as yet haven’t been able to come up with a solution.

I have a lot of sports historical market data from a betting exchange in a
database – 15,00 records by 17 fields

Each sporting fixture has a lot of records assigned to it, each record
quoting a different price for a particular outcome of the event, amount of
money changed hands at the price and the amount of trades made at that price.

The 17 fields are in this order

1. ID – a 4 or 5 digit number specific to each record within a group of
records determined by one fixture. The number seems to stay around the same
digits usually going up one or two in sequence on every record dedicated to
the fixture but its starting point varies in no particular order at the
beginning of each new record group (the start of a new fixture)

2. Fixture ID – a 7 – 9 digit number that stays the same for each record in
a group of records determined by the one fixture, it only changes when the
fixture changes, again in no particular order

3. Sports ID – a number given to that particular sport, IE every record to
do with football has the number 1 in it

4. Settled date – when the event was over and the market settled

5. The fixture itself
E.g. French Soccer/Ligue 2 Orange/Fixtures 11 December /Dijon v Grenoble
Or; Portuguese Soccer/Superliga/Fixtures 11 December /Nacional v Porto

6. Scheduled event start time E.g. 11/12/2006 19:45:00

7. The particular market in question E.g. Match Odds

8. Actual event start time E.g. 11/12/2006 19:45:54

9. Outcome ID is a number assigned to a group of records determined by one
outcome of the event. So in the instance of match odds for a match listed
above there would three outcomes; Nacional win, The Draw, or Porto win and a
fluctuating price for each – every price that was available recorded from
lowest to highest – the same number assigned to every price E.g.

Outcome ID Outcome Price No# Bets Amount matched
48785 Porto 1.93 21 £2,213.30
48785 Porto 1.94 8 £227.58
48785 Porto 1.95 3 £58.26
48785 Porto 1.96 2 £3.98
58805 The Draw 3.5 4 £11.70
58805 The Draw 3.55 3 £36.30
58805 The Draw 3.6 2 £5.42


Last Taken First Taken Win flag Inplay
10-Dec-06 08-Dec-06 1 PE
09-Dec-06 09-Dec-06 1 PE
09-Dec-06 09-Dec-06 1 PE
09-Dec-06 09-Dec-06 1 PE
08-Dec-06 08-Dec-06 0 PE
08-Dec-06 08-Dec-06 0 PE
11-Dec-06 11-Dec-06 0 PE

10. The outcome as listed in the example above
11. The price
12. Number of Bets made at the price
13. Amount of money matched at the price
14. When the price was last taken
15. When the price was first taken
16. What the eventual outcome was (1 indicating a win)
17. Whether the event was being bet on in running or not

What I am trying to do is run a query or do something in visual basic that
(I don’t know which would be the best way to do it) that will go through all
this data, filter out the things I don’t need and pick out the important
parts. I want the resulting table to look something like this:

Highest odds Amount matched Last Taken First Taken
Porto 1.96 £3.98 09-Dec-06 09-Dec-06
The Draw 3.9 £770.12 11-Dec-06 11-Dec-06
Nacional 5.9 £16.70 11-Dec-06 11-Dec-06
Sheff Utd 3.55 £3.24 05-Dec-06 05-Dec-06
The Draw 3.4 £13,264.36 11-Dec-06 06-Dec-06
Aston Villa 2.92 £325.52 11-Dec-06 11-Dec-06

(continued accross)

Lowest odds Amount matched Last Taken First Taken
1.52 £1.62 07-Dec-06 07-Dec-06
3.5 £11.70 08-Dec-06 08-Dec-06
4.6 £61.38 09-Dec-06 09-Dec-06
2.78 £65.00 11-Dec-06 11-Dec-06
3.2 £45.16 04-Dec-06 04-Dec-06
2.52 £18.00 06-Dec-06 05-Dec-06

(cont)

Most bets taken At these odds Last Taken First Taken
392 1.78 11-Dec-06 11-Dec-06
228 3.75 11-Dec-06 10-Dec-06
126 5.7 11-Dec-06 11-Dec-06
1029 2.86 11-Dec-06 10-Dec-06
1555 3.35 11-Dec-06 05-Dec-06
1075 2.84 11-Dec-06 09-Dec-06

(cont)

Most money matched At these odds Last Taken First Taken
£29,889.78 1.79 11-Dec-06 07-Dec-06
£8,536.38 3.75 11-Dec-06 10-Dec-06
£2,371.10 5.7 11-Dec-06 11-Dec-06
£358,584.26 2.86 11-Dec-06 10-Dec-06
£332,716.84 3.35 11-Dec-06 05-Dec-06
£331,704.26 2.84 11-Dec-06 09-Dec-06

Extracting the bits of information like the highest price for a team or the
price where the most money changed hands and then fields linked to these
records like when that price was first and last taken, and I want to try and
set it out in a continuous table so it lists fixture after fixture like above.

Any light that could be shed on this would be much appreciated
Many thanks

Tom
 
S

Steve

It seems the first thing that needs to be done is to create a normalized set
of tables that represents your data. The tables would begin something like
this:
TblSport
SportID
Sport

TblSportParticipant
SportParticipantID
SportParticipant

TblMarket
MarketID
Market

TblSportEvent
SportEventID
SportID
Market
ScheduledStartTime
ActualStartTime
SettledDate

TblSportEventParticipant
SportEventParticipantID
SportEventID
SportParticipantID

Fixtures and Outcomes also need to be created as tables.

Once you have a proper set of tables, you will be able to easily report on
the data.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
J

Jeff Boyce

RAM is (relatively) cheap -- learning Access is not. Is there a reason you
don't just stuff more memory in your box and keep using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top