Multiplying two tables question

[mmanzo1001]

What's the most efficient way to multiply out two tables given the attached example. I've got this going in excel and it's getting to be too large as Table two is 50 rows by 100+ columns.



Not really sure what you would call this, matrix multiplication, cross join?

[June7]

That certainly has the look and feel of matrix multiplication. A challenge I had to face with my project. Your choices are to use the Excel matrix functions in VBA code or to program the algorithm in VBA. Both require arrays.

My project also required inverse matrix calc. I found VBA code for matrix calcs from http://vplevris.com/

Here is example of Excel matrix function in VBA http://www.ozgrid.com/forum/showthre...t=11967&page=1

I discovered that matrix calcs in VBA with either method produced slightly different results than the calc in Excel spreadsheet.

[rpeare]

Well your tables are not normalized which is causing you a problem. I'm including an example of how you can do what you want with both a non-normalized table and a normalized table. I would really strongly recommend you opt for the normalization simply because it's better able to handle change over time.

The UNION query is what you would use for your current structure.

The CROSSTAB query is what you would use for a normalized structure.

[June7]

rpeare, good point about the normalization. I knew there was something else I should have mentioned.

However, rpeare, in your example db, tblProjects and tblProjects_Normalized are identical. Also Qry_SingleProject and Qry_AllProjects_Union are throwing input parameter prompts.

[CraigDolphin]

I should also point out that the example you presents does not include all of the information required to logically generate the output you want.

Table1 refers to Project 1
Table 2 refers to Project Type A

Nowhere do you provide information about which type (A, B or C) Project 1 is. Same for projects 2 and 3.

If you want access to give you an answer, you must supply each piece of information required to logically come up with that answer.

[rpeare]

Craig, it's called a cartesian join and while it's not the best way to do things sometimes it's the most efficient.

Attached is a fixed copy, I must have done a save instead of a save as when I copied the table.

Here's a fresh copy, the two 'final' queries aren't exactly what you asked for but you can play with them to get what you want.

[ajetrumpet]

I should also point out that the example you presents does not include all of the information required to logically generate the output you want.

Table1 refers to Project 1
Table 2 refers to Project Type A

Nowhere do you provide information about which type (A, B or C) Project 1 is. Same for projects 2 and 3.

If you want access to give you an answer, you must supply each piece of information required to logically come up with that answer.

Craig, when did you show up here? Get tired of things over at AWF, did ya?

[mmanzo1001]

rpeare,

Thanks a ton for taking the time to look at this; I know you don't have to. Apologies if the program/project nomenclature was confusing.

In reality the first table is People and what percent of you time has gone to the following Pojects, these should total 100% across (looks like person1 does not currently!).

The second table is a wieght factor table who's columns corespond to the projects above. The goal is to multiply out Table 1 by Weight A or in this case Project Type A, then B etc and finally sum by person.

With that said, I'm going to keep looking at this, thanks again, but I was expecting 15 lines of output, not 45 so I'm not sure this is quite there yet.

Craig, it's called a cartesian join and while it's not the best way to do things sometimes it's the most efficient.

Attached is a fixed copy, I must have done a save instead of a save as when I copied the table.

Here's a fresh copy, the two 'final' queries aren't exactly what you asked for but you can play with them to get what you want.

[rpeare]

I gave you queries that would show you all of the data in the database, it's not limiting it in any way. Are you saying that in your first table PROJECT1 only relates to the project type A in the second table?

My assumption based on your description that for every PERSON would have a resulting table for every value in the tblPrograms table. So 5x9 (3x3 matrix) = 45

If your PROGRAM1 does correspond only to the item labeled A in the PROJECTS table you have even more reason to normalize your tables because you need a common criteria as Craig said you would need to establish a link between the tables if a specific column in one table is supposed to refer to a specific row in a second table.

Here's an updated version of your database based on that assumption.

[mmanzo1001]

Here's an updated version of your database based on that assumption.

Well this is just fantastic!

[CraigDolphin]

Craig, when did you show up here? Get tired of things over at AWF, did ya?

Ha!

Actually, blame Bob. He spotted a fish biologist needing help here and PM'ed me to come over and help. I was active here briefly a long while ago but I got way too busy with work, and pretty burned out with AWF, and mostly gave up on assisting folks for a while.