Make a Table from a Crosstab query

[WanderingStar]

I want to make a table from the results of a crosstab query and am struggling to find a way. Is this possible?

Any help gratefully received!

 

[Rich]

Why would you want to?

 

[neileg]

A cross tab query produces a dataset that is inherently non-normalised. Why do you want to save it as a table?

 

[WanderingStar]

A cross tab query produces a dataset that is inherently non-normalised. Why do you want to save it as a table?

Cheers for the reply.

I want to create a table which stores the result of the crosstab query at that point in time for recall / manipulation later. I can send the data elsewhere (E.g. to Excel) for recall later but wondered if there was a neat way of sending it straight to a new table.

Cheers

 

[MobiusDick]

Hi there,

I would create the crosstab query and then call it in a new, make-table query, just add the fields as if you were creating the make-table from any other table or query. Then if you wanted it to update you just have to have your code or macro run the make-table and any other queries that produce the crosstab data.

I have used this method to perform various calculations and have had no reported problems from users.

Hope this helps.

Cheers.

---------------------------------

Man...is a tame or civilized animal; never the less, he requires proper instruction and a fortunate nature, and then of all animals he becomes the most divine and most civilized; but if he be insufficiently or ill- educated he is the most savage of earthly creatures. (Plato)

 

[Dennisk]

Use a make table query that uses the xtab query as its record source.

 

[WanderingStar]

MobiusDick & Dennisk,

I can't believe I didn't think of that ... sometimes the answer is so obvious you can't see it? Many thanks for that ... I shall now go and hang my head in shame.

Cheers.

 

[MobiusDick]

No worries, glad I could help.

 

[Bilbo_Baggins_Esq]

If I might add, that I think the reason you got so many inquiries as to why you would want to do this is that there really is no major reason to save a "snapshot" of this point in time.
Assuming your database has a good structure by adding a date range, or even adding a seperate report dates table, you should be able to recreate any point in time at any point in the future without having to save and manage all of these "snap shots".

 

[WanderingStar]

If I might add, that I think the reason you got so many inquiries as to why you would want to do this is that there really is no major reason to save a "snapshot" of this point in time.
Assuming your database has a good structure by adding a date range, or even adding a seperate report dates table, you should be able to recreate any point in time at any point in the future without having to save and manage all of these "snap shots".

Generally I would quite agree, however the tables I am using are accessed via an ODBC link and are not under my control. These source tables are not well structured and the volumes are such that recreating the whole source with a timestamp is not practical. I am therefore left having to take snapshots to support periodic extracts. Cheers anyway.