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.

 

[dhookom]

Creating several tables is probably not a good solution. You should be able to create a union query like:

SELECT [Num], [Jan] as TheCount, 1 As Mth
FROM tblNoNameGiven
UNION ALL
SELECT [Num], [Feb], 2
FROM tblNoNameGiven
UNION ALL
SELECT [Num], [Mar], 3
FROM tblNoNameGiven
-- etc --

You can use this query as the source for a Make Table or Append query.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]