Can query with left join be made editable?

[deko]

SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
FROM (tblTxAcct INNER JOIN tblTxType ON
tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;

I use this query as a RecordSource for a Datasheet that displays Accounts,
Types, and the number of Transactions in each Account. An Account cannot
exist outside of a Type, but an Account does not have to have Transactions
(hence the left join).

The problem is the datasheet is not editable and I need to let users edit
Account names. The Datasheet is editable if I omit the left join and use a
DLookup inside the Datasheet cell to get the TxCount from qryTxAcctListCt -
but that's slow and results in a delay populating the counts when the form
is scrolled.

I'd rather not use a popup form for editing Account names, but I don't know
if it's possible to use the above query for a DataSheet RecordSource and
still have in-cell editing... suggestions?

Thanks in advance.

[Steve Jorgensen]

On Fri, 21 Jan 2005 19:44:41 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:

SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
FROM (tblTxAcct INNER JOIN tblTxType ON
tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;

I use this query as a RecordSource for a Datasheet that displays Accounts,
Types, and the number of Transactions in each Account. An Account cannot
exist outside of a Type, but an Account does not have to have Transactions
(hence the left join).

The problem is the datasheet is not editable and I need to let users edit
Account names. The Datasheet is editable if I omit the left join and use a
DLookup inside the Datasheet cell to get the TxCount from qryTxAcctListCt -
but that's slow and results in a delay populating the counts when the form
is scrolled.

I'd rather not use a popup form for editing Account names, but I don't know
if it's possible to use the above query for a DataSheet RecordSource and
still have in-cell editing... suggestions?

Thanks in advance.

The problem is probably not the LEFT JOIN, but that I'm guessing
qryTxAcctListCt is a GROUP BY query. Access won't allow editing of any query
that includes a group by or aggregate subquery anywhere. If you don't have an
aggregate anywhere, I can't see why it wouldn't be editable, but sometimes you
can make a non-editable query editable by useing an inconsistent-updates
dynaset type. Other than that, you're pretty much stuck using something like
DLookup.

[paii, Ron]

You can get around the GROUP BY problem by creating a query to dump the data
into a temp table then linking to that instead of the query.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:de********************************@4ax.com...

On Fri, 21 Jan 2005 19:44:41 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:

SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
FROM (tblTxAcct INNER JOIN tblTxType ON
tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;

I use this query as a RecordSource for a Datasheet that displays Accounts,Types, and the number of Transactions in each Account. An Account cannotexist outside of a Type, but an Account does not have to have Transactions(hence the left join).

The problem is the datasheet is not editable and I need to let users edit
Account names. The Datasheet is editable if I omit the left join and use aDLookup inside the Datasheet cell to get the TxCount from qryTxAcctListCt -but that's slow and results in a delay populating the counts when the formis scrolled.

I'd rather not use a popup form for editing Account names, but I don't knowif it's possible to use the above query for a DataSheet RecordSource and
still have in-cell editing... suggestions?

Thanks in advance.

The problem is probably not the LEFT JOIN, but that I'm guessing
qryTxAcctListCt is a GROUP BY query. Access won't allow editing of any

query that includes a group by or aggregate subquery anywhere. If you don't have an aggregate anywhere, I can't see why it wouldn't be editable, but sometimes you can make a non-editable query editable by useing an inconsistent-updates
dynaset type. Other than that, you're pretty much stuck using something like DLookup.

[Albert D. Kallal]

Are the join fields used the primary keys?

I note that those fields are text, and perhaps they are not the primary key?

The join will only be editable if the join is done from a primary key on the
main table, to the field in the child table.

You should also expose the primary key of the child table in the query.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[deko]

> The problem is probably not the LEFT JOIN, but that I'm guessing

qryTxAcctListCt is a GROUP BY query. Access won't allow editing of any query that includes a group by or aggregate subquery anywhere.

10-4. That's the issue. Here is qryTxAcctListCt:

SELECT COUNT(Tx_ID) AS TxCount, [TxAcct_ID]
FROM tblTxJournal
GROUP BY [TxAcct_ID];

Thanks for the tip.

[Steve Jorgensen]

That "solution" creates its own large set of problems.

On Fri, 21 Jan 2005 16:16:36 -0600, "paii, Ron" <pa**@packairinc.com> wrote:

You can get around the GROUP BY problem by creating a query to dump the data
into a temp table then linking to that instead of the query.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:de********************************@4ax.com.. .

On Fri, 21 Jan 2005 19:44:41 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:

>SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
>Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
>FROM (tblTxAcct INNER JOIN tblTxType ON
>tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
>tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;
>
>I use this query as a RecordSource for a Datasheet that displaysAccounts, >Types, and the number of Transactions in each Account. An Accountcannot >exist outside of a Type, but an Account does not have to haveTransactions >(hence the left join).
>
>The problem is the datasheet is not editable and I need to let users edit
>Account names. The Datasheet is editable if I omit the left join and usea >DLookup inside the Datasheet cell to get the TxCount fromqryTxAcctListCt - >but that's slow and results in a delay populating the counts when theform >is scrolled.
>
>I'd rather not use a popup form for editing Account names, but I don'tknow >if it's possible to use the above query for a DataSheet RecordSource and
>still have in-cell editing... suggestions?
>
>Thanks in advance.
>

The problem is probably not the LEFT JOIN, but that I'm guessing
qryTxAcctListCt is a GROUP BY query. Access won't allow editing of any

query

that includes a group by or aggregate subquery anywhere. If you don't

have an

aggregate anywhere, I can't see why it wouldn't be editable, but sometimes

you

can make a non-editable query editable by useing an inconsistent-updates
dynaset type. Other than that, you're pretty much stuck using something

like

DLookup.

[deko]

> That "solution" creates its own large set of problems.

I ended up using a popup form on double-click of the Datasheet cell, which
is acceptable. The trade-off of in-cell editing for more efficient
populating of the count cell was well worth it. Using DLookup in Datasheet
cells is a hack...

[paii, Ron]

I agree that doing this on a form can cause problems, but can be manageable
if Group BY data is only for reference and users input is check before
saving a record.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:jg********************************@4ax.com...

That "solution" creates its own large set of problems.

On Fri, 21 Jan 2005 16:16:36 -0600, "paii, Ron" <pa**@packairinc.com> wrote:

You can get around the GROUP BY problem by creating a query to dump the datainto a temp table then linking to that instead of the query.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:de********************************@4ax.com.. .

On Fri, 21 Jan 2005 19:44:41 GMT, "deko"
<www.clearpointsystems.com@use_contact_form.com> wrote:

>SELECT tblTxAcct.TxAcctName, tblTxType.TxTypeName,
>Nz(qryTxAcctListCt.TxCount, 0) AS TxCt
>FROM (tblTxAcct INNER JOIN tblTxType ON
>tblTxAcct.TxType_ID=tblTxType.TxType_ID) LEFT JOIN qryTxAcctListCt ON
>tblTxAcct.TxAcct_ID=qryTxAcctListCt.TxAcct_ID;
>
>I use this query as a RecordSource for a Datasheet that displays

Accounts,

>Types, and the number of Transactions in each Account. An Account

cannot

>exist outside of a Type, but an Account does not have to have

Transactions

>(hence the left join).
>
>The problem is the datasheet is not editable and I need to let users edit >Account names. The Datasheet is editable if I omit the left join and use

a

>DLookup inside the Datasheet cell to get the TxCount from

qryTxAcctListCt -

>but that's slow and results in a delay populating the counts when the

form

>is scrolled.
>
>I'd rather not use a popup form for editing Account names, but I don't

know

>if it's possible to use the above query for a DataSheet RecordSource

and >still have in-cell editing... suggestions?
>
>Thanks in advance.
>

The problem is probably not the LEFT JOIN, but that I'm guessing
qryTxAcctListCt is a GROUP BY query. Access won't allow editing of any

query

that includes a group by or aggregate subquery anywhere. If you don't

have an

aggregate anywhere, I can't see why it wouldn't be editable, but sometimesyou

can make a non-editable query editable by useing an

inconsistent-updates dynaset type. Other than that, you're pretty much stuck using

somethinglike

DLookup.