Bump on the road for my Knowledge db

[Steve C]

Good morning gentlemen I've gotton a bit stuck creating a table to capure data.

May I ask you to help me figure this out?

These are my important tables so far

List (The Quarter are grouped by List)
Quarter (There are 640 Quarter, each is a ¼ mile radius area of London)
Point (There are 20,000 Point. Each is a landmark (in a ¼ mile))

Relationships

List can have many Quarter
Quarter can have many Point

I have created a Form (called Main) showing each possible ¼ mile area (from Quarter Table) and a Subform (called MainSubform1) showing the many landmarks (from Point Table). Which is lovely and thanks to you gentlemen on this site so I’m extremely grateful.

Now it’s onto the next bit and I’m stuck again for how to capture the data I need.

If the Main form (so far) shows possible setting off places for imaginary taxi journeys, I want a second Subform (MainSubform2) to show the landmarks (from Point Table ) that are in the ¼ mile areas (From Quarter Table) that I know how to say the taxi journey too.

My solution (which feels wrong and doesn’t work) is: A Table (called SetDown - which I haven’t created yet). Each record in SetDown is one Quarter (from Quarter Table) and then, somehow, I want the SetDown Table to record the many other ¼ mile radii (from Quarter Table) and so their many landmarks (From Point Table) that can be shown in Subform2. each time I learn a new ¼ mile I will update which other Quarters I can go to in SetDown.

Sorry for such complicated preamble to my question

How should I set the extra table/s up for this?

Thank you in advance.

 

[jdraw]

Could you please provide a description - 2-3 lines plain English - for
Quarter
List
Point
Landmark so the rest of us are "up to speed with what you are talking about?

Have you tried creating a relationship diagram or data model?
There are free data models at
http://www.databaseanswers.org/data_models/index.htm

Perhaps something there may help.

 

[Steve C]

Thank you for replying jdraw.

I’m very pleased with the link you sent – I am working through the sample db and lessons there.

All of my posts seem huge, and I am anxious not to overload you chaps with useless (and dull) info. I have tried to be as concise as poss and not lose meaning. My field lists are at the bottom of this post (where you can ignore it) in case it helps.

First, to answer your questions jdraw;

Quarter. A ¼ mile radius circle on the map of London. There are 640 Quarters I have to learn everything inside the circle, things like; Resturants, Railway Station, Buildings (anything a taxi customer might ask to go to).

List. The 640 Quarters are published in books (Called “Blue Book Lists”). Each Blue Book List has 32’s Quarters (so there are 20 Blue Book Lists). I don’t know why they do that way but, if ever I find out I might want to query Quarters (and so Points) by their List Number. A Table just seemed right for that

Point. “Taxi driver speak” for all the Restaurants, railways, etc; found in each Quarter.

Landmark – I used that word to mean Point, but, on reflection it isn’t any clearer – sorry, I won’t use it again.

My Relationship Diagram works up the point where I can’t draw the next bit. And. The next bit doesn’t seem to be in the samples.

Okay, so if you interested here’s how London’s Trainee Taxi Drivers learn their Knowledge. It’s very clever, not at all obvious and terribly complicated to explain!

First sorry for assuming you don’t already understand the method. Once/if you do, I am hoping you will see where I am wrong and put me right on what I’ve missed.

The end goal is for me to be able to connect any two Points in London by the straightest route and proving it by saying, out loud, every road name between those two Points without hesitation or error. That’s called Calling Over a Run – or sometimes Call or Calling.

The only way to achieve the goal is by endless Calling of the shortest route between Points.

I want the database to give me starting Points (Set Off Points) (does that already) and end Points (Set Down Points) (where I’m having trouble) of an imaginary taxi journey.

London is learned one Quarter at a time. The Quarters are dotted all over London. The Runs between them criss-cross all over London. I must learn the route from Quarter No.1 to Quarter No.2 (that route is called Run 1). The next Run (Run No.2) is from Quarter No.3 to Quarter No.4 and so on. (I have ignored the route from Quarter No.2 to Quarter No.3 for the moment (although that still has to be learned) because it isn’t an official “Blue Book Run”.

As time goes by, the number of Runs I learn builds up, so it’s possible to mentally “jump Runs”, so to speak. To arrive at other Quarters which aren’t in sequence 1,2,3,4.

When practicing Calling Over, any Pick Up Point is okay (I can filter out the Quarters I don’t want to work with). But a random Set Down Point doesn’t work at all because it may be in a Quarter I can’t reach using a combination of Runs I already know so far.

My plan was another Table in which each record unique record is a Quarter (well I have that Table already it’s called Quarters) and then other table to record the many other Quarters that I can go to – but that’s the same Table!

Of course I would have to update all the Quarters possible Set Down Quarters as each new Run is learned.


Entities
Field Names Field Type Notes

List groups of Quarter
IDList PK
ListNumber text for keeping list in order
ListStopTest Y/N to exclude/include List in Queries

Quarter 1/4 mile radius of London Map
IDQuarter PK
QuarterNumberAndSuffix text e.g. 01a or 01b - for keeping List in order
QuarterListID FK to ListNumberID
QuarterName text The landmark at the centre of the ¼ mile
QuarterAddress text Road name
QuarterPostcode FK to PostcodeID
QuarterTitle text not the same as QuarterName
QuarterMemo memo Memo, of roads to go to next BB ¼
QuarterGridAToZ text Map reference
QuarterGridPremierMap text Map reference for alternate map
QuarterStopTest Y/N to exclude/include in Queries
QuarterMemoryScore text max 1 char. to record score in tests
QuarterSetDown FK Combo Box to DestinationID THIS FIELD IS NOT YET CREATED

Point Resturants, Railway stations, Law Courts, buildings anything interesting
IDPoint PK
PointAddress text Road name
PointPostcode FK to PostcodeID
PointBorough FK to BoroughID
PointQuarter FK to QuarterID
PointType FK to TypeID
PointNote text Text box 255 chars
PointGridAToZ text Map reference
PointGridPremierMap text Map reference for an alternate map
PointMemoryScore text max 1 char. to record score in tests
PointStopTest Y/N to exclude/include in Queries

Type
IDType PK
Type e.g is the Point a Restaurant etc
TypeStopTest

Borough
IDBorough PK
Borough text name of London Borough

Postcode
IDPostcode PK
PostcodePostcode text The postcode
PostcodeArea text Areas having this Postcode
PostcodeStopTest Y/N to exclude/include in Queries
THIS IS ALL FUZZZY BELOW HERE

Set Down
IDSetDown PK
SetDownQuarter FK to QuarterID

 

[Steve C]

Perhaps I said too much. If so, I'm sorry, I'll be more concise in future.

Perhaps you've already told me the answer and I missed it (quite likely actually).

Or, perhaps the answer is so obvious - that it seems I haven't done my homework. That's okay too - I will try harder. Once I have worked out the answer I will post back.

Thanks for your attention and help so far.

 

[jdraw]

Very interesting stuff. Do you have a clear plan of what you want this database to do (purpose of the database)?

With 20000 points and criss-cross routes I'm not sure of the questions to be asked or answered without having all 20000 points geocoded (or something similar). And from experience, shortest route between points is NOT always the fastest because of traffic conditions, traffic lights/stop signs, time of day ..... Seems like an awful lot of set up, but you know your situation better than us.

Is there a taxi driver training/testing database/application available? Is that where you're heading?

Can you post a jpg of your tables and relationships?
As I see it

A List contains many Quarters
A Quarter contains many Points

There are 20 Lists
Each List has 32 Quarters

 

[Steve C]

Hello Jdraw

Thank you so much for your post - I was feeling lonely.

I’ve been off scratching my head for a solution alone. Sometimes a week or so not thinking about a problem helps solve it. Other times (like now) it just takes a week longer.

Yes. I have a very clear plan. I know exactly what data to capture and how I want to present it on a Form.

I have the WHAT but not yet the HOW!

First. Forget Geocoding. The whereabouts of 25000 streets in London, is not part of the database. That’d be crazy to try to capture.

Experience is, respectfully, irrelevant. The requirement is to know the straightest route between any two of 20,000 Points in London. It’s safe to ignore; Traffic Lights, Road Works, Time of Day.

You have understood the Relationships perfectly Jdraw. But there’s a bit more – which I haven’t properly explained, because, as already said, I don’t know how

I’m going to spend some more time learning and testing out ideas. I will come back when I have made some progress.

In the meantime. Thank you for reading this.

Best wishes

Steve C