XTab Query to Denormalize, Now Want To Update That Data Set

[Guest]

Hello,

I am using 2 Cross-Tab queries to denormalize data to present in a datasheet
view within a subform.

I've added a YES/NO field to the underlying query, which the user wants in
order to SELECT/DESELECT one or more records using this YES/NO field.

The question: As I am using 3 data sources for this datasheet, 1 table, and
2 cross-tab queries, joined with the table, I understand I cannot update any
data, due to the fact that cross-tab queries are used.

Any suggestions for working around this issue?

Thanks!

 

[Jeff Boyce]

David

Even if you had a checkbox on the form, WHICH of the data would your user be
wanting to update when the checkbox got checked? As you point out, the
dataset is not updateable because it portrays the results of a crosstab (and
other combinations of tables, etc).

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Spencer]

Stuff the data into a temporary table and update the temporary table. When
the user is finished entering/changing use the temporary table to update the
real records and then delete the records from the temporary table.

Optionally use a continuous form instead of the datasheet view and use the
control's events to change the underlying records (assuming that all you
need is to update the Yes/No field) and then requery. This might be
acceptable if you have a small set of "records".

 

[Guest]

Thanks John.

This "could" work, I'm discovering, if I can make the Key field in the
temporary table a PRIMARY KEY. Do you know how to do that through a MAKE
TABLE query?

 

[Duane Hookom]

There is a sample of pushing a normalized table into a temporary "flat"
table at www.access.hookom.net/samples/seating.zip. When the first form
opens, check the On Change event of the combo box cboEvnID which populates
the temporary table. There is also a function in the same module that
updates the normalized table from edits in the denormalized form
(UpdateSeating).