Title: SQL Virtual Table
Tags: sql;ms-access;ms-access-2003
Question: I have my database set up as such:

Each product I have has it's own table where it records the quantity sold/transaction # (so column 1 is "transactionID", column 2 is "quantity")

ex) p-backScratcher (where p- is indicative of "product")

There are also tables for each year which hold records of each transaction that went through.  Each of these tables holds the following columns: "transactionID", "date", "time", "pt_CA", "pt_DB", "pt_VC", "pt_MC", "pt_CH", "pt_AM"

ex) sales-2008, sales-2009, etc. etc.

I'd like to be able to reference a single table that holds all the records for each year without having to change the sql for the table to include a new year.

So for example, I'd want to query all transactions for "p-backScratcher", I don't want to have to type out

```SELECT sales-2008.date, sales-2009.date 
  FROM sales-2008, sales-2009 
 WHERE sales-2008.transactionID = p-backScratcher.transactionID 
    OR sales-2009.transactionID = p-backScratcher.transactionID
```

...but rather:

```SELECT sales.date 
  FROM sales 
 WHERE sales.transactionID = p-backScratcher.transactionID
```

Comment: This design seems to rather difficult to work with.  Is there a reason for choosing this design over others which might be easier to work with?

Comment: My first thought was to include everything in one table; where anything past the "pt_AM" column would be a product's quantity.  Basically anytime I'd do something like that, I'd eventually run out of columns (in the long run).  Having a table for each product assures me that a) I won't have any tables with excess null entries and b) lookup times would be shorter by separating sales by year; considering reports are typically generated either monthly or for a given year (though due to the concerns posted, I'll probably just consolidate all transaction info to a single table)

Here is the accepted answer: ```
  I'd like to be able to reference a single table that holds all the records for each year without having to change the sql for the table to include a new year.
```

This is why you should not be using one table per product and one table per year.

What you need is one "Product" table and one "Transaction" table.

Comment for this answer: Exactly.  This is a bad design.

Here is another answer: What you're looking for is called a "View" which pretty much is a stored statement that is a list of properly formatted results.  You can query it directly like it is a table.


Here is another answer: In SQL, as Kyle's answer states, you can create a View, which is a kind of Virtual table, but I would strongly recommend that you get a book, or google, Relational database design, before you commit yourself to a database structure.  


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 4, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 2.5', 'CreationDate': '2009:00:12/18/09', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><ms-access><ms-access-2003>', 'Title': 'SQL Virtual Table', 'ViewCount': 2013, 'id': 1929414, 'user_AboutMe': '<p>...Master of none, but always better than a master of one.</p>\n', 'user_AccountId': 65071.0, 'user_DisplayName': 'SnakeWasTheNameTheyGaveMe', 'user_Reputation': 2466.0, 'user_Views': 345.0, 'user_id': 191741.0}}