Joining column values in Table1 to column headers in Table2 2

[actuaryinworks]

Hi all,

I am not sure if what I am trying to do is possible at all but I thought I'd post it here to find out.

In a nutshell, I am trying to join column values in a table to headers of another table's columns.

Here is an example:


Table1: Client DB

ClientID FundsAtYearStart
1001 100
1002 120
1003 80
etc..

Table2: Transactions recorded over year:

Transaction 1001 1002 1003 etc...
Deposits 10 0 40
Withdrawls 50 10 20


My tables are a bit more complex, but operate on the same concept.

What I am trying to do is to get a resulting table in the format of Table1 with final amounts. That is FundsAtYearStart from Table1 plus deposits, minus withdrawls from Table2 for each client. The issue is: how do I link to the proper client ID? I'll need to link row1 in Table1 to column2 of Table1, etc.

Any ideas?

I tried to "flatten" the 2nd table to make it into three columns: ClientID, Transaction, Amount, but the query was too complex for Access.

Also, both tables are linked from Excel and are constantly modified in the Excel source file, so I don't have the luxury of "flattening" the second table manually.

Thanks!

 

[lespaul]

I tried to "flatten" the 2nd table to make it into three columns: ClientID, Transaction, Amount, but the query was too complex for Access.

To "flatten", or "normalize" - see document linked below, the table you need a union query:

[tt]
SELECT '1001' As ClientID, Transaction, 1001 As "Amount" FROM Table2
UNION SELECT '1002', Transaction, 1002 FROM Table2
UNION SELECT '1003', Transaction, 1003 FROM Table2
...[/tt]
for each of the fields in table2

now depending on your version of Access you may have to save this as its own query before using it (Access 97) or you may be able to embed it in another query (Access 2000 and above).



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[actuaryinworks]

I have Access 2000.
I did exactly what you stated above, but the query appears to be too complex.

 

[Golom]

Can you post the SQL?

The "... query too complex ..." message often means that you in fact have an error in the coding of the query and the SQL parser can't figure it out.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[lameid]

I'm not an excel expert, but couldn't you pivot the data in excel to a new worksheet and link to that? That way you wouldn't have to worry about complex queries in Access.

Alternately, you might consider a crosstab query on your table1. Then you could compare this query and table2. This would pose the same issue as the union query... How do you deal with changing or new clientID's?

 

[actuaryinworks]

Our analysts update ClientIDs in Excel and Access links to the Excel table.

Pivot wouldn't work - too much data (too long for Excel)

 

[lespaul]

again, can you post the actual UNION query you tried to run that gave you the 'too complex' error.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[actuaryinworks]

I don't think I can post any sql (company regulations and such) but when i split it into two queries, both queries worked. So the syntax is correct.

 

[lespaul]

so you ran:
[tt]
SELECT '1001' As ClientID, Transaction, 1001 As "Amount" FROM Table2
[/tt]
and that worked?
but the following didn't?
[tt]
SELECT '1001' As ClientID, Transaction, 1001 As "Amount" FROM Table2
UNION SELECT '1002', Transaction, 1002 FROM Table2
UNION SELECT '1003', Transaction, 1003 FROM Table2
[/tt]

As far as posting the actual SQL, can't you just change the field names? We're not looking at any DATA and that's what your company should be interested in protecting, not the field names.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lameid]

Looking at the select, you have double quotes around the alias that don't belong...

SELECT '1001' As ClientID, Transaction, 1001 As Amount FROM Table2
UNION SELECT '1002', Transaction, 1002 FROM Table2
UNION SELECT '1003', Transaction, 1003 FROM Table2

Another idea is to build a correct table by appending the values in each column in Excel for the appropriate clientID... this would not be dynamic but would be easier to code.

Realistically, I think you need to create a database that takes the same input as the spreadsheet and then report the information however people want it. You could even dump the data to Excel.

 

[lespaul]

my bad, but if the 1st query runs then those issues must have been addressed. It's the combining of the queries that's the issue.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[actuaryinworks]

Thank you all for your great comments.
I decided to break it down into sub union queries and append them to each other at the end. Can I write an append query that will take more than 2 queries?

 

[lameid]

You could base an append query off a union query but I would think it would run faster to append them individually. That's just a hunch though not guaranteed.

My thought was to write a procedure to append the values for each 'Client ID' coulmn to a different table. If the union doesn't become a performance problem, go for it.