By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Can query with left join be made editable?

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.
Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
> 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.
Nov 13 '05 #5

P: n/a
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.


Nov 13 '05 #6

P: n/a
> 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...
Nov 13 '05 #7

P: n/a
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.

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.