Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Unanswered: Multiply Two Tables (sort of)

    Hi,

    I am new to access, but have significant excel/vba experience. I am using access because my dataset is very large (150k rows x 139 columns) and excel cannot deal with it.

    I have two tables I have imported into access. One table has the many rows and 139 columns, the other table has factors I would like to multiply each corresponding columna and entry with. So I have found that I can accomplish this by creating a select query and multiplying the columns (i.e. Apr-00: [Apr-00]*[03/04/2011]), but I have to do this for 124 out of the 139 columns. Is there some way I can do this in SQL without having to type out that formula 124 times?

    As an example:

    Table 1:
    Company Jan Feb Mar Apr...
    x .1 .2 .3 .4
    z .1 .2 .3 .4
    a .1 .2 .3 .4
    s .1 .2 .3 .4
    r .1 .2 .3 .4
    (150k rows)

    Table 2:
    Company Jan Feb Mar Apr
    z 150 100 200 300
    x 150 100 200 300
    s 150 100 200 300
    a 150 100 200 300
    r 150 100 200 300
    (Has as many rows as there are individual company names in Table 1)

    Desired Result:
    Company Jan Feb Mar Apr
    x (150*.1) (100*.2) (200*.3) (300*.4)
    etc...
    z
    a
    s
    r

    I have created a relationship between the tables (option 2: Include ALL records from Table 1 and only those from Tabl2 where the Company fields are equal


    Appreciate the help.
    Last edited by ManticoreX; 12-23-11 at 08:41.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    almost certainly
    but you will still have to do some typing
    and we would need to know details of the data model (table design), but from what you've suggested so far I suspect it will be a frightmare
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    4
    Hi Healdem,

    Just edited my post, was that more what you were looking for? I can't see this being easy! but at least it may prove to be a good lesson in SQL (I know a bit).

    Appreciate the help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so what you need is a join between the two tables
    a join could look like
    Code:
    select t1.jan * t2.Jan as Jan, 
    t1.Feb * t2.Feb as Feb, 
    t1.Mar * t2.Mar as Mar, 
    t1.Apr * t2.Apr as Apr, 
    t1.jan * t2.Jan as Jan, 
    t1.jan * t2.Jan as Jan, 
    from [Table 1] as t1
    join [Table 2] as t2 on  t2.Company = t1.Company
    Order by Company;
    for your current problem
    Code:
    select (t1.Jan * t2.Jan) * (t1.Feb * t2.Feb) * (t1.Mar * t2.Mar) * (t1.Apr * T2.Apr) as MyValue from [Table 1]
    join [Table 2] as t2 on  t2.Company = t1.Company
    Order by Company;
    MyValue is an alias or name for the resultant value in the query
    you may need to do some error checking to make certain that you don't try and multiply by 0, Access may not be as fussy as others, but it will have issues should you need to divide by a value

    as these factors consistent, ie month - 4 = .1, -3 = .2, -2 = .3 & current month .4, if so then you don't need to assign factors to each and every comany. if you need a few factors then put some sort of code identifying the factors ot use

    eg
    Code:
    T1
    Code  F1  F2  F3  F4 
    Std   .1  .2  .3  .4
    Av    .25 .25 .25 .25
    
    T2
    Co Code Jan Feb Mar
    x   Std 100 200 300
    y   Av   300 400 100
    then change the join to be
    Code:
    join [Table 2] as T2 on T2.Code = T1.code
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2011
    Posts
    4
    Merry Xmas healdem!

    Appreciate your help (I shipped off for holiday a bit early).

    I see how the code works and your solution at the end is quite clever. However, in my actual datasheet, each "cell" in either table can vary and I need to be able to view each data point individually and the resulting product individually so I can't summarize the data.

    Is there anyway I can automate the creation of the query (multiplying the corresponding columns of T1 and T2 together i.e (t1.jan*t2.jan)) as I have to do this for 10 years (120 months). I suppose I am just approaching this from a vba perspective in excel where I would have written something to multiply each corresponding column from each table (sheet) untill there were no columns. Of course it would be a seriously computationally heavy and painful task to do for data this size in excel, which is why I am not doing it in excel.

    In any case, your code has saved me a great deal of time (even if I have to write out 120 months by hand it is better than how I did it previously), appreciate your help!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect the solution to your problem is to design your tables to meet a database design, NOT a spread sheet design.

    the way you have your tables designed means you have to write individual queries

    you could write the queries using VBA, storing them in the querydef object and then run these queries
    you could write the queries on the fly

    had the design been normalised then you probably could have used on piece of SQL to generate ALL the results.

    in the db world you have tables and columns NOT cells.
    SQL data manipulation uses set theory ie perform the specified action on the set of data. so the key is to be able to identify (through judicious use of joins and where clauses) the set you want to process.

    it may be possible to do what you want with one or two queries. if you are up for it consider posting your solution here
    remove or obfuscate any sensitive data
    take a backup copy of your database
    compact and repair it
    compress it into a zip file
    attach it to a posting here.

    I can't guarantee I'll have time to look at it, or even if I can get a solution, however someone else may also look at this and come up with other ideas

    however it would help if you could explain the business logic behind this process.

    is it only Jan, Feb, Mar, April for each year for each company
    or is it a moving target
    Jan, Feb, Mar, April
    then Feb, Mar, April, May
    and so on
    Last edited by healdem; 12-28-11 at 06:18.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2011
    Posts
    4
    Hi,

    So I have been looking into normalizing the database. I have seriously obscured the data and am using a metaphor...(bear with me). One way to think of the database is of keeping track of a game, with a "person" and "partner". Each person and partner has attributes. Each of the many "partners" teams with each of the four "Persons". Their team (by team I mean each combination of person + partner) score is tracked on a monthly basis. DB attached.

    So I have solved my initial query using your suggestion and now have moved on to more complicated things. My first attempt in normalization has resulted in two extra tables. One called "Partners" and the other "Persons" where each partner or persons attributes are stored.

    My bigger concern is normalizing the time series data, which I am not sure how to do. Like you were saying I would like to be able to run much simpler queries to pull the data I need. Not sure how to dothis....

    I am currently trying to create a query which will output each partner and person group sum totals by month. for example:

    Partner Person Group Sum of Jan Sum of Feb
    x 1 5 6
    x 2 etc
    x 3
    x 4
    y 1
    y 2
    y 3
    y 4
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off
    I'd strongly reccommend NOT using the Access query lookup 'wizard'
    where you have partnerlocation, consider having another table storing city / location names and use the primary key of that table in the main table.
    eg
    table: Locations
    ID autonumber (Primary Key)
    Location string / text

    in your 'Sheet1' replace the Partner Location with LocationID, declare it as a numeric column of type long. save the table and then make a realtionship (tools | relationships) to associate Sheet1 with Locations as an Relational Integrity RI link.
    do the same with any other db lookups you've used. the reas they are a pain to use, cause problems with queries and every time there is a change it requires a change to the table. by making it a RI link to another table the user can add / modify do what ever the heck they like to the locations file without changing the design.

    the columns Jan to Dec should at first glance be candidates for pushing down to a sub table

    to leverage the power of database's you have to get away from the spreadsheet mentality. there's no harm in disolaying data as spreadsheets (datasheets in Access speak), but you shouldn't store data in that way in a database

    At a breif glance I think you have several entities (tables) there
    one for
    Persons
    Locations
    PersonGroups
    PartnerGroups
    possibly PartnerStyles, but if you only ever will have 3 styles then probably you can get away with the lookup wizard
    something thats stores these actual ratings

    I'd suggest you lookup 'intersection tables'
    for the entity that stores the scores you coudl go several ways, but lookign at your data I'd suggest that you store the style scores for each partner per month
    eg
    PartnerScores
    Person1
    Person2
    DateOfMatchup
    DefensiveScore
    AggresiveScore
    EvasiveScore

    you will need to work out how to relate
    a person to a 'person' group'
    a person to a 'partner group'


    the aim is to flatten the data. gettign away from a block of 12 scores (or 36 if the three types are replicated for each partner combination) makes the data manipulation easier. ferinstance when you want to retrieve a specific 3 month block of data instead of writing 9 queries you can 'get away' with jsut the one (by telling the data engine you want results for the three months starting with whatever, or the three months ending with. a bit more though, by parameterising the query ie you supply a start and finish date with the one query you can return whatever.
    say you are required to return
    by month.. same query start 01/Apr/2011 end 30/Apr/2011
    by quarter.. same query start 01/Jan/2011 end 31/Mar/2011
    by year.. same query start 01/Apr/2011 end 31/Dec/2011
    and so on
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •