Perfectly alright... it took me a few tries to get the right "question asking" skill down and I still occasionally get a poke-n-the-ribs when asking my own questions for pertinent information. :-D
I think you are still looking at a crosstab query (CTQ); however, it's difficult to tell from your description.
The other option as a Many:Many (M:M) relationship table.
IN all of the following, PK = Primary Key, FK = Foreign Key
+ tblA
[tblA]![PK] - numeric(long)
other fields as needed.
+ tblB
[tblB]![PK] - numeric(long)
other fields as needed.
>>> Something to note...
If you place [tblA] and [tblB] in a query without any relationships, place the [PK] in the show table grid, then you would get every possible combination of
([tblA]![PK],[tblB]![PK])
Called the cartesian product of the tables... this is the trick I used in the 10x10 SQL above, I just grouped out the duplicated entries.... This may be all you need?
if not then the M:M relationship table
+ tblAtoB
[tblAtoB]![PK]
[tblAtoB]![FK_tblA] - force no null
[tblAtoB]![FK_tblB] - force no null
Compound Index on [tblAtoB]![FK_tblA]:[tblAtoB]![FK_tblB]
Force unique value on compound key
Yes, one can use the compound key as a primary key, I; however, prefer not to do so for many reasons - primarily dealing with VBA coding.
From there is should be a simple matter to create the CTQ
Then there is the self join table, for example, a table with employees and managers
tblE
[tblE]![PK]
[tblE]![IK_Manager]
other fields as needed
In the table relationships:
[tblE]![PK] :1:M: [tblE]![IK_Manager]
An example is given here:
Having one field that will need multiple sub fields-Post#9
Now this shows a document management; however, the self join is quite common for other reasons.
Personally, I've never created a crosstab query directly from a table like this; however, I am sure that Rabbit, NeoPa, or one of the other experts has done so... seems like something one would do?!