Crosstab query - how to "invert"

[Renner]

Hello there,

I'll try to explain my problem as simple as possible. I
made a Crosstab Query from a table containing my database.
This crosstab will be exported to the Excel, then it will
be actualized. So I will import it again to Access.
How can I transform these new data (they are in
the "shape" of a crosstab) in order to update my database?
In other words, how can I get back the data contained in a
Crosstab?

Thanks in advance,

Renner,
Brazil.

 

[Duane Hookom]

I would create a union query from the crosstab and then a make table query
from the union query. After the table is "made", you can create a unique
index so that it can make an updateable recordset when joined with your
original table.

 

[Tom Ellison]

Dear Renner:

We have found this to be sufficiently useful that we have developed
the ability to create "crosstab forms" that allow you to edit the data
in a crosstab directly in a form, then process any changes back into
the original, well normalized data. This last step is exactly what
you're asking to do.

Some notes about doing this:

- In a multi-user system, which could be the case even when using a
spreadsheet to edit the data, a system to detect and resolve conflicts
would be extremely beneficial.

- You cannot, of course, have any actually aggregated (summed,
averaged, etc.) column in the crosstab. Changing a sum from original
data would be an ambiguous process. If the original was 1 + 2 + 3 = 6
and someone changes the 6 to 8, there are a large number of ways to
change 1, 2, and 3 so they now add up to 8.

- It is possible a crosstab could create more than 255 columns. Using
a scroll bar control on a form, you can handle any number of columns
by breaking the data into segments (whatever will fit on a screen) and
changing the RecordSource as the user scrolls.

This is a fairly expert approach when you do all the steps required
for a complete job.

Back to your question, I would like to show you how to do what you
want by illustrating it using your actual situation. I would need
details of your data and the SQL of your crosstab in order to be able
to create such an illustration.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts