Updating A CrossTab Query?

[Guest]

I have a table with projects (as a field), employee (as a field), months (as
a field), and manpower (as a field).

From this table, I create a CrossTab Query to display the months across the
page with the employee downward. This is to indicate the employee
utilization. For each month we have his utliization (i.e. 1.0 for 100% busy).

I use the CrossTab Query also to start with the current month and then show
the following 11 months. I am unable to edit the crosstab query.

Is there a way for me to update the crosstab query or I must move it into a
table and then update it. There will be times I need to add another employee
to the table and change his workload.

I would appreciate any help you can offer!!


Gary

 

[Guest]

I am unable to edit the crosstab query.table and then update it.
Open the query in design view and you can edit. Further edits can be made
by opening the SQL view and editing. What edit do you need to do?

Post your SQL and say what else you need it to perform.
Another employee can just be added in the current table.

 

[Guest]

I wanted to change the contain of the query (which is based on a table).
I know how to edit a crosstab query, but I think I can't changes the contain
of the query???

Thanks,

Gary

 

[Guest]

but I think I can't changes the contain of the query???
I do not understand what you mean by "contain."

As I said Post your SQL and say what else you need it to perform.

 

[Tom Ellison]

Dear Gary:

A crosstab query requires you choose an aggregate function. Queries that
are aggregated cannot be edited.

That is not to say what you want to do is not possible.

Many crosstab queries are not necessarily based on a needed aggregate. You
might put in FIRST of some other aggregate that is effectively meaningless.
In that case, each "intersection" in the crosstab is actually a singe row in
a table (or a potential row in a table).

In such cases, the appearance made by a crosstab query is often quite
desirable for data entry. You can dump the data into a table and bring that
table up on the screen for editing, then process any changes back into the
original table structure.

There can be considerable difficulties in doing this. In a multi-user
system, I have found it necessary to track the original value of every entry
so I can tell which have been changed. When trying to save what has been
changed, it can be very important to check first to see if the original
value is still what is stored, or if someone else may have changed it. It
may be necessary to implement rules for how conflicts are resolved, or to
prompt the user before conflicts can be resolved.

This is one of the most complex database application situations with which I
am familiar. Getting it right is very rewarding, but it's not a simple
thing to do.

If you can guarantee no one else will be modifying the data while you have
it open for your own modification, then it's not too bad at all.

If it sounds like this is your situation and you want to pursue this
further, please let me know. Fill in some details, OK?

Tom Ellison

 

[John Vinson]

I use the CrossTab Query also to start with the current month and then show
the following 11 months. I am unable to edit the crosstab query.

That is correct. Crosstab queries are not updateable, since each
"record" in the query is composed of multiple records in the parent
table. It's ambiguous what should be edited.

There is NO way to directly update a Crosstab query. You'll need to
either use a temp table with code to fill it from the Crosstab and
then other code to repopulate the original table, or fake the crosstab
using an unbound Form.

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

It is not possible to update the data presented by the crosstab because Jet
can no longer identify which record to update once the data is aggregated.

The desire to update data in a crosstab is a symptom of spreadsheetitus.
When you're used to working with spreadsheets, it can be difficult to
reorient your perception of the data to allow you to be comfortable updating
less data on a single form. Access can always present your data in the
crosstab view your spreadsheet used to show.

Some times if you have a small, fixed number of columns that are pivoted,
you can simulate the look of a spreadsheet by using multiple subforms, each
subform would show the data that would be displayed by one column of a
crosstab. An example of this might be days of the week. There are never
more than seven so you could use seven subforms, formatted to look like a
spreadsheet. The only other alternative if you want a spreadsheet view is
an unbound form and I wouldn't wish that on anyone. You'll be coding from
here until next year and will still probably have missing functionality that
bound Access forms give you for free.