SQL Virtual Table

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Not sure how big this project is or what your specific requirements are (for instance if you have code associated with it) but you might want to look into migrating your data to MS SQL (or MySQL, etc). SQL Server has a desktop edition which is free but supports only a limited feature set (views are included in that).

MySQL, PostGre, etc support the features you are looking for, including inherited tables in PostGre (not exactly what you need but similar).

I have not worked with Access for a while, and I am not sure it will support the features you are looking for.