Creating "empty records" in Access

[mlangley]

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

[Rabbit]

Table3 if your data. Table4 is a list of years.

SELECT y.*, Table3.Data INTO tbl_Test
FROM [SELECT x.*, Table4.* FROM (SELECT DISTINCT ID FROM Table3) AS x, Table4]. AS y LEFT JOIN Table3 ON (y.Year = Table3.Year) AND (y.ID = Table3.ID)
ORDER BY y.ID, y.Year;