"Multiplication Table" type issue in ACCESS

[Shawn29316]

Hi,
I have an issue very similar to the chart we used back in the stone ages to learn our multiplication facts. You remember, 1 - 10 as row headers, 1 - 10 as column headers and where the row and column intersect, the result of multiplying the column header by the row header.

For my data, if I did this in Excel, I'd have 170 columns and 170 rows but I'm not sure that's the best way. I'm assuming that I need 3 columns (column, row and result) and 28,900 rows (170*170). Is that the "correct" method?

Thanks,
Shawn

[zmbd]

You mention Excel; however, this is the VBA/Access forum. The solution I am showing here could be modified for Excel and a pivot table; however, I would think the simple cell formulas would work :)

ANYWAY.... for Access... CrossTab Query comes to the rescue:


For a 10 x 10 using 1 table with the records 1, 2, 3,... 9
Save the table as [Multipliers] with field named [PK] as numeric long
You can of course do this with two tables; however, why if the same values would be used.

Open a new query in design view.
Close the show table dialog box... this isn't easily done from the GUI Query editor
Right click in the table field and switch to SQLView

Cut and paste the following:

TRANSFORM [T1]![PK]*[T2]![PK] 
    AS Product
SELECT [T1]![PK]
FROM [Multipliers]
       AS T1
   , [Multipliers]
       AS T2
GROUP BY [T1]![PK]
PIVOT [T2]![PK]

This will give you your 10*10 multiplication table.

I would think that something similar should work for your situation.

[Shawn29316]

zmbd,

Reading back through my post, it's probably only clear to me what I "MEANT" to say. - Sorry!
What I'm trying to figure out is how to create the table to hold the relationship between the data elements. I used the multiplication table as a visual but I'm really trying to figure out how to best design the table. Should the table look like the output from the query you wrote above or should it be 3 columns (element1, element2 and relationship)?
Thanks,
Shawn

[zmbd]

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?!