naiminp naiminp - 9 days ago 5
SQL Question

Joining multiple tables in Access via vba/sql

I have a database of tables, each contains two columns, the first being a code, second being an amount. The tables are pulled from data created on a daily basis, where a code will have data for a particular date if it was triggered by an external event on that date. For e.g. the table for day 2015-10-02 would look like this:

Code 2015-10-02
1 321.23
2 3442.13
3 679.2
4 6201.4


So each table has the same first column (code) but the codes will vary each day. Some days we may have different codes being triggered than any other previous day (first time it's ever been triggered) or it may have been triggered on a previous day, but with a different amount. The table I want to create will look like:

Code 2015-10-02 2015-10-03 2015-10-04 ....
1 321.23 0 0
2 3442.13 0 10.42
3 679.2 41.2 0
4 294.12 41.31 1042.12
5 0 0 371.14
.
.


I know I could join all the tables but I want to add on any new codes as well and assume a 0 for each of the previous days the code was not triggered on.
Is there an easy way to do this via VBA/SQL?

Thanks

Answer

Nooo. This is not how databases work.

You need a table with 3 columns, like this:

+------+------------+---------+
| Code |    Date    | Amount  |
+------+------------+---------+
|    1 | 2015-10-02 |  321.23 |
|    2 | 2015-10-02 | 3442.13 |
|    3 | 2015-10-02 |   679.2 |
|    4 | 2015-10-02 |  6201.4 |
|    3 | 2015-10-03 |    41.2 |
+------+------------+---------+

Code + Date is the primary key.

Then you can get your matrix of Code vs. Date with a Crosstab query (use the wizard).