Hi there. I am trying to figure out a way to create a series of empty records in a database (ACCESS 2003). Basically, the table has 10 years of information by id. However, if there is no ID, then there is no record. I would like to make a record that simply does not have any data in it in that case.
For instance if have:
-
ID YEAR DATA
-
a 1 xxxxx
-
a 2 xxxxx
-
a 3 xxxxx
-
a 4 xxxxx
-
b 1 xxxxx
-
But I would like
-
ID YEAR DATA
-
a 1 xxxxx
-
a 2 xxxxx
-
a 3 xxxxx
-
a 4 xxxxx
-
a 5
-
a 6
-
a 7
-
a 8
-
I have a table with just the year numbers in it that I tried to JOIN, or UNION, but that did not work because I could not figure out how to match the IDs to it (not in the years table). There are about 6000 IDs in the table that I am trying to manipulate.
I know how to do this using php or VBA code, but I am looking for a way to do if purely in SQL. Any advice that you guys have would be much appreciated.
Thanks