Knowledge db – please help with the next bit.

Steve C

Registered User.
Local time
Today, 04:55
Joined
Jun 4, 2012
Messages
119
Hello everyone in Access Land ~ hope you’re all well!

As you know, I’m training to be a London Taxi driver by learning one ¼ mile radius circle at a time (a Quarter) of London and all the Resturants, Bars etc. (a Point) in each Quarter. Then I learn the route between all the Points in the Quarters (that's how we're told to do it).

I haven’t the “Access speak” to properly phrase my question and none of the sample databases on here seem do what I need.

My Table “tblQuarter” is in a One-to-Many relationship with “tblPoint” (there aren’t any LookUp fields in any Table).

My MainForm shows each Quarter, all Points there are on SubForm1.

Next (tricky) bit.

Once I’ve learned Quarter number one (Q1), I will do Q2, it’s easy enough to have a field (although I don’t yet) in Q1’s record showing Q2’s QuarterID.

Then my MainForm for Q1 will show the Points in Q2 on SubForm2

Looking at the MainForm, I can choose a Point from Q1 (on SubForm1) another Point in Q2 (on SubForm2) and test myself to see if I have properly learned the route in between the two by saying out it loud.

But here’s the bit I can’t “see” how to do. Earlier I thought this was a Table problem but now i think it's a Query Problem.

When I’ve learned Quarter #3 (assuming Q1,Q2 & Q3 are on a straight line) I want the MainForm record for Q1 to show all Points in Q2 & Q3 in SubForm2.

How can I achieve that? So far my ideas aren’t working!

I have set out my db so far, if it helps.

tblQuarter
IDQuarter (PK) – In the Many-To-One relationship with tblPoint this is the “One”
IDList (FK)
QuarterNumber
QuarterName
QuarterAddress
IDPostcode (FK)
QuarterMemo
QuarterGrid
QuarterStopTest
QuarterMemoryScore
QuarterMapHyperlink


tblPoint
PointName
PointAddress
IDPostcode (FK)
IDBorough (FK)
IDQuarter (FK) – this is the “Many” in the Many-To-One relationship with tblQuarter
IDType (FK)
Point Notes
PointGrid
PointStopTest
PointMemoryScore
 
You may want to place a "completed" field in tblQuarter and base the recordsource of the subform to:
SELECT tblPoint.*
FROM tblPoint LEFT JOIN tblQuarter ON tblPoint.IDQuarter = tblQuarter.IDQuarter Where tblQuarter.Complete = True;
This way you are getting all Points from tblPoints from all completed Quarters.
 
Thank you very much indeed for your answer Michael.

I will to exactly as you suggest. And then learn as much as I can about LEFT JOIN too.

I'll report my results back on here.

Best wishes

Steve C
 
Hi Micheal.

Still working on your LEFT JOIN suggestion. I'm trying to learn INNER and RIGHT JON too - just to see what that means.

Also, I've been looking at Association Tables and Many-to-Many Ralationships

I have realized, there's a lot to learn all about what Access can do and how it does it before deciding how to acheive the objective.

So, if you're out there, I thought I'd post to say I haven't forgotten to report back on here - it's just taking some time to get my head around all this.
 

Users who are viewing this thread

Back
Top Bottom