SQL payments matrix

Question

I want to combine two tables into one:

The first table: Payments

id | 2010_01 | 2010_02 | 2010_03 
1  | 3.000   | 500     | 0
2  | 1.000   | 800     | 0
3  | 200     | 2.000   | 300
4  | 700     | 1.000   | 100

The second table is ID and some date (different for every ID)

id | date         | 
1  | 2010-02-28   | 
2  | 2010-03-01   | 
3  | 2010-01-31   | 
4  | 2011-02-11   |

What I'm trying to achieve is to create table which contains all payments before the date in ID table to create something like this:

id | date         | T_00  | T_01   | T_02
1  | 2010-02-28   | 500   | 3.000  | 
2  | 2010-03-01   | 0     | 800    | 1.000
3  | 2010-01-31   | 200   |        |
4  | 2010-02-11   | 1.000 | 700    |

Where T_00 means payment in the same month as 'date' value, T_01 payment in previous month and so on.

Is there a way to do this?

EDIT: I'm trying to achieve this in MS Access.

The problem is that I cannot connect name of the first table's column with the date in the second (the easiest way would be to treat it as variable)

I added T_00 to T_24 columns in the second (ID) table and was trying to UPDATE those fields

set T_00 =    
iif(year(date)&"_"&month(date)=2010_10,

but I realized that that would be to much code for access to handle if I wanted to do this for every payment period and every T_xx column.

Even if I would write the code for T_00 I would have to repeat it for next 23 periods.

Yep! There are many ways to do this. WHat have you tried so far? — Pieter Geerkens, Jan 25 '15 at 00:30
I cannot define first table's headings as dates, so I cannot connect those headings to dates in the second table... — Stefan, Jan 25 '15 at 00:33

score 0 · Accepted Answer · edited May 23 '17 at 11:56

Your Payments table is de-normalized. Those date columns are repeating groups, meaning you've violated First Normal Form (1NF). It's especially difficult because your field names are actually data. As you've found, repeating groups are a complete pain in the ass when you want to relate the table to something else. This is why 1NF is so important, but knowing that doesn't solve your problem.

You can normalize your data by creating a view that UNIONs your Payments table.

Like so:

CREATE VIEW NormalizedPayments (id, Year, Month, Amount) AS
SELECT id,
    2010 AS Year,
    1 AS Month,
    2010_01 AS Amount
FROM Payments
UNION ALL
SELECT id,
    2010 AS Year,
    2 AS Month,
    2010_02 AS Amount
FROM Payments
UNION ALL
SELECT id,
    2010 AS Year,
    3 AS Month,
    2010_03 AS Amount
FROM Payments

And so on if you have more. This is how the Payments table should have been designed in the first place.

It may be easier to use a date field with the value '2010-01-01' instead of a Year and Month field. It depends on your data. You may also want to add WHERE Amount IS NOT NULL to each query in the UNION, or you might want to use Nz(2010_01,0.000) AS Amount. Again, it depends on your data and other queries.

It's hard for me to understand how you're joining from here, particularly how the id fields relate because I don't see how they do with the small amount of data provided, so I'll provide some general ideas for what to do next.

Next you can join your second table with this normalized Payments table using a method similar to this or a method similar to this. To actually produce the result you want, include a calculated field in this view with the difference in months. Then, create an actual Pivot Table to format your results (like this or like this) which is the proper way to display data like your tables do.

SQL payments matrix

1 Answers1