Creating and putting together several tables from one table

[creagan12]

Hi everyone. I have a question regarding what I thought would be a simple problem.

Let's say I have a table:
NUM JAN FEB
1 4 5
2 3 2

And from this table I want to rebuild a new table in which jan and feb become field entries. So, it would look like this:

NUM COUNT MONTH
1 4 JAN
1 5 FEB
2 3 JAN
2 2 FEB

Now for the actual problem I am working on, I have many month fields, so what would be nice is being able to simply create several tables from this first table:

NUM COUNT MONTH
1 4 JAN
2 3 JAN
... and one for Feb, Mar etc. using one large sql statement.

I am working in Access. I'm not quite sure how to create a table from another table using SQL there. Ideally, I could just create these several tables for each month from the original table and then put them together since the fields are all the same. I appreciate any help.

 

[mutley1]

You can go from your first step to the third straight away, but in the last line, you say you ideally want to create tables for each month then put them together in another?

Are you after:
TBL_JAN
NUM COUNT MONTH
1 4 JAN
2 3 JAN

TBL_FEB
NUM COUNT MONTH
1 5 FEB
2 2 FEB

then concatenate them into your second example:


NUM COUNT MONTH
1 4 JAN
1 5 FEB
2 3 JAN
2 2 FEB

You can miss out either step 2 or step 3 depending on what you want for the end result, or do it all if you want both - sorry, just not sure of the total end result!

Rgds,

M.