By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,270 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,270 IT Pros & Developers. It's quick & easy.

Creating "empty records" in Access

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. ID   YEAR      DATA
  2. a        1            xxxxx
  3. a        2            xxxxx
  4. a        3            xxxxx
  5. a        4            xxxxx
  6. b        1            xxxxx
  7.  
But I would like
Expand|Select|Wrap|Line Numbers
  1. ID   YEAR      DATA
  2. a        1            xxxxx
  3. a        2            xxxxx
  4. a        3            xxxxx
  5. a        4            xxxxx
  6. a        5 
  7. a        6            
  8. a        7            
  9. a        8            
  10.  
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
Nov 4 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
Table3 if your data. Table4 is a list of years.
Expand|Select|Wrap|Line Numbers
  1. SELECT y.*, Table3.Data INTO tbl_Test
  2. 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)
  3. ORDER BY y.ID, y.Year;
  4.  
Nov 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.