Convert a Crosstab Query back to a normal query 1

[Dave177]

I'm working on a fairly large database and there is a crosstab query in it which is the end result of lots of other append/normal queries etc. I need to change some of the field values in the crosstab and it would be much easier to do if it was a normal query. Is there anyway you can convert a crosstab into a normal query?

Thanks for any help.

David

 

[lespaul]

what's the SQL of the crosstab?

Leslie

 

[dhookom]

What's your definition of a "normal" query? You can't edit the values in a crosstab or any query created from a crosstab. If you really want to "change some of the field values", you will need to use the crosstab as the source of a make table query. You could then edit the records in the "made" table.

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Leslie, I will post the SQL in due course but can't get at it at the moment.
Duane, By edit I mean build relationships with other tables. There are a number of measure names that in the crosstab query are being used as column headings. I would like to use these measure names as a foreign key in a new query.

At the moment the data looks like:

Site Measure1 Measure2 Measure3
A 22 56 78
B 55 23 21
C 68 21 88

I want it to look likeL
Site Measure Value
A 1 22
A 2 56
A 3 78
B 1 55
B 2 23
B 3 21
C 1 68
C 2 21
C 3 88

So then I can link it with another table on field Measure.

Thanks for any help.

David

 

[dhookom]

Why can't you use a pre-crosstab table or query rather than creating a crosstab then un-crosstabbing it? It's like taking a train out of town and then renting a car to drive back.

If you are stuck using the crosstab, you can use a normalizing union query to normalize.

Duane
Hook'D on Access
MS Access MVP

 

[Dave177]

Someone needed to do lots of calculations (for example adding one field to another and dividing by another to give a new value) so they made it into a crosstab. It's also quite a large database and it would be much easier/safer not to change anything pre-calculation stage. Also, the query will only have to be run once a month so the extra "mileage" isn't that much of a problem.
I will try the normalising union query.

Thanks for your help.