Joining multiple tables in Access via vba/sql

Question

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

Yep, column corresponding to the column in the table I am joining up. I'm essentially converting the individual tables into a 2d table, where the first column will consist of all codes to have triggered over the period, and the subsequent columns (with names corresponding to date) consisting of "amounts" for each day. — naiminp
– naiminp, Commented Dec 1, 2016 at 13:15
Access is a relational database, and when using it you are supposed to follow some design rules for your tables. Search the web for "database normalisation" (eventually normalization). You should redesign your database with a column to include the dates — Kelaref
– Kelaref, Commented Dec 1, 2016 at 13:46
You should NOT maintain separate tables for every day, nor columns with date values. This will be a management headache down the line. Heed @Kelaref's comment and Andre's answer below. — Parfait
– Parfait, Commented Dec 1, 2016 at 14:03

Andre · Accepted Answer · 2016-12-01 13:45:49Z

3

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).

answered Dec 1, 2016 at 13:45

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

naiminp Over a year ago

I understand what you guys are saying, so I've changed the format of the tables to include the date as a field rather than a column heading. Thanks a lot for the crosstab idea, it's worked like a treat.

naiminp Over a year ago

Apologies for the poor initial construction.

Andre Over a year ago

Although I did contemplate to link nooooooooooooooo.com , an apology isn't needed. :) We're all here to learn. Glad the solution works for you. @naiminp

Collectives™ on Stack Overflow

Joining multiple tables in Access via vba/sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related