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.

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

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

ok so what you need is a join between the two tables
a join could look like
for your current problem
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
then change the join to be

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!

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

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

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