Forum Discussion

SystemAdmin4's avatar
SystemAdmin4
Qrew Member
5 years ago

Many to Many Summary

Hi

I'm sure there must be a way to do this natively, but I cannot get my head around it, so any help would be appreciated.

My table set up is as follows:

Parent: Lessons (Key: Lesson ID) - circa 1500 records
Child 1: Topic (Key: Topic ID) - circa 50 records
Child 2: Subject (Key: Subject ID) - circa 15 records

Both Parent Child relationships are Many:Many so are joined via their own mapping tables

In other words I have:

Lessons <Mapping Table 1> Topic
Lessons <Mapping Table 2> Subject

Obviously the keys for the mapping tables are system generated and have no contextual meaning.

So, what I want is a report which shows all the 'valid' combinations and permutations of Topic and Subject (with their respective IDs) where a 'valid' combination is one where there is at least one lesson which combines them.

If I was playing in good old MS Access, I'd be able to do this quite easily, but can't work out how to in QB.

Any help greatly appreciated.

Thanks.

David


------------------------------
dmlaycock2000 dmlaycock2000
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I would set up a table called Subject Topics where the key field is the hyphenated  concatenation of the subject and the topic.

    then set up a helper table with Record ID on of 1, ie, 1 Record, and use it to record the Focus Subject.  Ie the Record ID# of the focus Subject.

    use a relationship to Lesson Subjects to flag if the Subject is in focus and if so use a summary field to flag that the lesson is in focus for that subject.

    look up a flag that the lesson is in focus down to Lesson Topics.

    make the concatenated Key field of Subject Topics on the Lesson Topics Table by combining the Focus  Subject Record ID with the Record ID Of  the Topic.

    set up a saved table to table copy to copy that concatenated field to the new Subject topics table where the Lesson was in focus.

    create a URL formula button on the Subject to put it in focus in that helper table and to run the saved table to table copy. 

    Push the button on each of the 15 Subject records.

    post back if you get stuck anywhere or contact me directly for one on one Assistance.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------