Create an editable "cross-tab" style query or form

[Guest]

Hey all:

I could really use some ideas for a solution to this problem of creating a
tab-style interface that would allow the client to edit records.

I have four tables:

tblCompanies (co_id, co_name)
tblListFields ((PK)field_id, list_id, field_name)
tblListFieldValues ((FK)field_id, field_value)

I've created a cross-tab query:

TRANSFORM Avg(TESTINPUT.fieldvalue_id) AS AvgOffieldvalue_id
SELECT TESTINPUT.co_name
FROM TESTINPUT
GROUP BY TESTINPUT.co_name
PIVOT TESTINPUT.field_name;

which utilizes the following select query (TESTINPUT):

SELECT tblListFieldValues.*, tblCompanies.co_name, tblListFields.field_name
FROM (tblCompanies INNER JOIN tblListFieldValues ON
tblCompanies.co_id=tblListFieldValues.co_id) INNER JOIN tblListFields ON
tblListFieldValues.field_id=tblListFields.field_id;

My goal is that I need to provide the tab-style interface for the client who
will be entering field values for each company. They believe this is the
fastest way for them to enter field values for many clients in each list. The
cross-tab query gives me EXACTLY the type of interface I'm looking for but I
cannot edit the data like you can with a standard select query. I've also
generated a form using the crosstab as a recordsource, but still it won't
allow me to edit the data.

Basically, I need to generate the following editable interface:

company name field_name field_name field_name

Company1 value1 value2 value3
Company2 value1 value2 value3

This is probably a wicked unclear explanation, but any ideas would be wildly
appreciated so I can get this project over and done with!!

Thanks very much

 

[Douglas J. Steele]

Since cross-tab queries rely on aggregate functions such as Avg, Max, Sum,
etc., they are not updatable. (What row(s) in the table would you update so
that the new average equals the value in the query?)

 

[Guest]

actually, i have no need of any averages, etc. Basically, I want to end up
with the each company's name as the first column of each row (so the client
knows whose data they are viewing/editing), then the value of the
tblFieldValues fields in each consecutive column. The client would then edit
values in those subsequent field value columns.

Thanks.