Add/append data to crosstab query

[DatabaseIntern]

So I have made a crosstab query that works by taking the data off the "intersections" of data between to fields and mapping the "Module Names" as rows, and "MicroNames" as columns, from the MicroModules table. This part works well, but I was wondering if there was a way I could achieve this and include ALL Module names and Micronames even if they have no intersections (IE they are not in the micromodules table). I want this for totaling reasons mostly, and did not think of this when I first designed the DB - they question just sprung up in beta testing (I only have a few that dont have intersections, they are mostly "incomplete entries" but I have been asked to include them regardless)



Here is a simplified version of my DB architecture:





Essentially I want ALL modules as Rows, and ALL Micros as Columns in the query, and still somehow have the information from MicroModules linking them together.

Currenttly I have a query to give me all the modules not in micromodules, maybe there is a way I could append those on as rows? I am a little confused here about how how/if I can move forward. Please feel free to ask for clarifications or for any questions.


Here is a VERY bad drawing of the ideal query I want - it looks similar to the one i have working, but it would include all the entries from the Modules table even if they had no link to MicroMods.

[June7]

Try:

Query 1 that includes both Modules and Micros tables WITHOUT a join clause. This will result in records for every possible combination of modules and micros pairs.

Query 2 includes Query 1 and the MicroModules table WITH compound join on both the module and micro ID fields and join type 'Include all records from Query 1 ..."

Query 3 would be a crosstab of Query 2.

[DatabaseIntern]

I am sorry, I dont have much experience with advanced query design, I am confused at the first step, if I just try to include everything I get a type mismatch. could you explain a little more in depth how you would go about creating these particular queries?

[June7]

Nothing really advanced about my suggestion, actually quite basic.

The first query should just have the two tables in the query design frame. If a link is created, delete it. Do not want the query to have a join clause. Then drag only the ID field from each table to the field grid.

Show the resulting SQL statement if you still have an issue.

[DatabaseIntern]

Musta been running on too little sleep yesterday cause everything worked fine today, funny enough all I needed to do was change the join type on a query I already had (although I didnt realize that until I walked through making this other query). Its amazing what a little experience can do for you. Thanks for all your help, that push in the right direction saved me hours no doubt.