NEED TO TRANSPOSE A TABLE IN ACCESS

[mchester]

MY TABEL NAME IS: INDIVPRODUCTION
HAS 29 COLUMNS
I HAVE VERY LIMITED KNOWLEDGE OF VB
THANKS MIKE

 

[lespaul]

have you looked into the Cross Tab query?

 

[mchester]

YES A CROSS TAB QUERY WONT WORK FOR WHAT I AM DOING, I NEED TO END UP WITH A REPORT FROM THE NEW TABLE. i TRIED A CROSS TAB AND THE RESULTS WERENT WHAT I WAS LOOKING FOR.

THANKS FOR YOUR RESPONSE.

 

[lespaul]

ok, first off, all caps is considered shouting, please stop.

Secondly, without some details from you it's impossible to help you come up with a solution that will work.

Why do you need a new table? Why can't your query be the report source? What was wrong with the cross tab query that you tried? Can you give some sample data and expected results (we don't need all 29 FIELDS {not columns}) just enough to get an idea of what you are trying to accomplish.

Check the FAQ areas for tips on posting so that you get a helpful response.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[mchester]

My table looks like this
Appraiser,Date,Improv<1200,Improv>1200,Improv12001-400,.....
The crosstab query I made,I used Date as my column headings and needed the Improv fields as my row headings. So dates across the top and the Improvement discriptions down the side for each appraiser.
05/01/05 05/02/05 05/03/05
improv
improv>1200
improv1200-3000
Thanks for your patience and help

Mike

 

[lespaul]

and was there something specific that was wrong with the cross tab? what you are describing is exactly what it is designed to do. Do you still have that query? can you post the SQL?

(thanks for not shouting!!)

leslie

 

[mchester]

Here is my SQL
TRANSFORM Var(INDIVPRODUCTION.DATE) AS VarOfDATE
SELECT INDIVPRODUCTION.[IMPROV<1200]
FROM INDIVPRODUCTION, PRODUCTIONMAIN
GROUP BY INDIVPRODUCTION.[IMPROV<1200]
PIVOT INDIVPRODUCTION.DATE;

I need all the Improv fields not just the one.

I needed a feild named Task with all the improvement fields as rows.

Thanks
Mike

 

[lespaul]

well that query is only selecting a SINGLE field:

[IMPROV<1200]

what you need to do is create a query that has all the information you want in it and then change it to a cross tab query.

this will select all the fields from both tables (INDIVPRODUCTION and PRODUCTIONMAIN)

TRANSFORM Var(INDIVPRODUCTION.[DATE]) AS VarOfDATE
SELECT *
FROM INDIVPRODUCTION, PRODUCTIONMAIN
GROUP BY INDIVPRODUCTION.[IMPROV<1200]
PIVOT INDIVPRODUCTION.[DATE];

I enclosed your field DATE in [] because Date is a keyword/reserved word in most languages and has to be identified as a field with the brackets.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[mchester]

When I used the sql it said * not in a crosstab query.