Work around for 'un-editable' recordset which utilizes the maxaggregate function

[Kelii]

Hello,

So I have a form which shows all items available for sale, when it was
last sold, where it was last sold, and whether it is active or
inactive.

I would like to be able to edit the active/inactive field (its a yes/
no or boolean type field); however, I am not able to make these edits
because of the max aggregate function used on the sale date.

My question is: does anyone have a work around to this issue?

Note: that I did look through help "When can I update data from a
query" and none of the scenarios seemed to match my situation.

So here's the full background:
tblItemDetails:
Item_Descriptio n_ID (key - 1:M to tblSalesDetails )
Active_Status (boolean)

tblSalesDetails :
Sales_ID (key - M:1 to tblSalesMaster)
Item_Descriptio n_ID (key - M:1 to tblItemDetails)
Count_Sold

tblSalesMaster:
Sales_ID (key - 1:M to tblSalesDetails )
Company_Locatio n
Sale_Date

Query:
SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status, tblSalesMaster. Company_Locatio n,
Max(tblSalesMas ter.Sale_Date) AS MaxOfSale_Date
FROM tblSalesMaster INNER JOIN (tblItemDetails INNER JOIN
tblSalesDetails ON tblItemDetails. Item_Descriptio n_ID =
tblSalesDetails .Menu_Descripti on_ID) ON tblSalesMaster. Sales_ID =
tblSalesDetails .Sales_ID
GROUP BY ... etc ...

[Allen Browne]

The GROUP BY clause guarantees a read-only result.

First, try it without the GROUP BY, and see if the results are editable:

SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status,
tblSalesMaster. Company_Locatio n,
tblSalesMaster. Sale_Date
FROM tblSalesMaster
INNER JOIN (tblItemDetails
INNER JOIN tblSalesDetails
ON tblItemDetails. Item_Descriptio n_ID = tblSalesDetails .Menu_Descripti on_ID)
ON tblSalesMaster. Sales_ID = tblSalesDetails .Sales_ID

If that't not editable, there's no point going further.

It is is, try adding a subquery to the WHERE clause.
Something along these lines:

SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status,
tblSalesMaster. Company_Locatio n,
tblSalesMaster. Sale_Date
FROM tblSalesMaster
INNER JOIN (tblItemDetails
INNER JOIN tblSalesDetails
ON tblItemDetails. Item_Descriptio n_ID = tblSalesDetails .Menu_Descripti on_ID)
ON tblSalesMaster. Sales_ID = tblSalesDetails .Sales_ID
WHERE tblSalesMaster. Sale_Date =
(SELECT Max(Dupe.Sale_D ate) AS MaxOfSale_Date
FROM tblSalesMaster AS Dupe
WHERE Dupe.Sales_ID = tblSalseMaster. Sales_ID)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelii" <ke****@yahoo.c omwrote in message
news:60******** *************** ***********@59g 2000hsb.googleg roups.com...

>
So I have a form which shows all items available for sale, when it was
last sold, where it was last sold, and whether it is active or
inactive.

I would like to be able to edit the active/inactive field (its a yes/
no or boolean type field); however, I am not able to make these edits
because of the max aggregate function used on the sale date.

My question is: does anyone have a work around to this issue?

Note: that I did look through help "When can I update data from a
query" and none of the scenarios seemed to match my situation.

So here's the full background:
tblItemDetails:
Item_Descriptio n_ID (key - 1:M to tblSalesDetails )
Active_Status (boolean)

tblSalesDetails :
Sales_ID (key - M:1 to tblSalesMaster)
Item_Descriptio n_ID (key - M:1 to tblItemDetails)
Count_Sold

tblSalesMaster:
Sales_ID (key - 1:M to tblSalesDetails )
Company_Locatio n
Sale_Date

Query:
SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status, tblSalesMaster. Company_Locatio n,
Max(tblSalesMas ter.Sale_Date) AS MaxOfSale_Date
FROM tblSalesMaster INNER JOIN (tblItemDetails INNER JOIN
tblSalesDetails ON tblItemDetails. Item_Descriptio n_ID =
tblSalesDetails .Menu_Descripti on_ID) ON tblSalesMaster. Sales_ID =
tblSalesDetails .Sales_ID
GROUP BY ... etc ...

[rquintal]

On Sep 27, 12:01*am, Kelii <kel...@yahoo.c omwrote:

Hello,

So I have a form which shows all items available for sale, when it was
last sold, where it was last sold, and whether it is active or
inactive.

I would like to be able to edit the active/inactive field (its a yes/
no or boolean type field); however, I am not able to make these edits
because of the max aggregate function used on the sale date.

My question is: does anyone have a work around to this issue?

Note: that I did look through help "When can I update data from a
query" and none of the scenarios seemed to match my situation.

So here's the full background:
tblItemDetails:
Item_Descriptio n_ID (key - 1:M to tblSalesDetails )
Active_Status (boolean)

tblSalesDetails :
Sales_ID (key - M:1 to tblSalesMaster)
Item_Descriptio n_ID (key - M:1 to tblItemDetails)
Count_Sold

tblSalesMaster:
Sales_ID (key - 1:M to tblSalesDetails )
Company_Locatio n
Sale_Date

Query:
SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status, tblSalesMaster. Company_Locatio n,
Max(tblSalesMas ter.Sale_Date) AS MaxOfSale_Date
FROM tblSalesMaster INNER JOIN (tblItemDetails INNER JOIN
tblSalesDetails ON tblItemDetails. Item_Descriptio n_ID =
tblSalesDetails .Menu_Descripti on_ID) ON tblSalesMaster. Sales_ID =
tblSalesDetails .Sales_ID
GROUP BY ... etc ...

I've worked around this problem by
1) unbinding the field from the control on the form
2) use a dlookup() in the on Current Event of the form to retrieve the
value
3) update the table using a SQL update query triggered in the
AfterUpdate event of the control. (yes it fires on unbound controls)
4) repeating the Dlookup()

[Kelii]

I'll just modify the above comments with one idea that popped into my
head after thinking about this all night.

I could create a temporary table (again a slow process over a network)
that stores the item, sale date, and sale location. Then rebuild the
query based on the permanent items table and the temporary table.

Although I haven't tested this idea, I think it would be considerably
faster.

My only hesitation here is that I don't like building temporary
tables. To me, it feels like I'm creating clutter, ableit temporary,
and it leaves open the possibility for inadequate error handling to
remove the temp table when the form is closed.

Anyhow, thoughts are welcome.

kelii

[rquintal]

On Sep 27, 4:55*pm, Kelii <kel...@yahoo.c omwrote:

Allen,

P.S. rquintal - thank you as well for your suggestion. In my
experience, unbinding a check box in a continuous form results in a
series of check boxes that do not work properly; namely, if you check
the box for one record, all the records become checked and vice versa.
Would be interested if you understand differently or if I
misinterpreted your post.

You are absolutely right. Unbound controls on continuous forms show
the value calculated for the current row on all rows. I should have
determined that the form was Continuous by the phrase 'shows all items
', as opposed to my interpretation as a form with an all rows
recordset.

However, sometimes leaving the control bound but doing the update via
a query, then undoing the change in the control's before update might
work.

[Kelii]

Allen,

Thanks for your post. I didn't realize a list of reserved words
existed. I've bookmarked and will refer to this in the future. It
would sure be a huge bummer if I had to go back and revise my
queries / code / forms etc... for the whole Class thing.

I am now building a separate form with highly similar functionality to
the form discussed in this post. I'm going to try the temp table
method and see how it works.

rquintal,

You know I should probably have mentioned the continuous form aspect
of the problem. Unlike others, I tend to leave important facts out of
my posts ... apologies.

Roger,

I'm up for trying any suggestion and will give your's a shot.

Thanks again,

kelii

[Bob Quintal]

Kelii <ke****@yahoo.c omwrote in
news:60df8351-71f2-4f22-a218-42ff2590f1d5@
59g2000hsb.goog legroups.com
:

Hello,

So I have a form which shows all items available for sale, when it
was last sold, where it was last sold, and whether it is active or
inactive.

I would like to be able to edit the active/inactive field (its a
yes/ no or boolean type field); however, I am not able to make
these edits because of the max aggregate function used on the sale
date.

My question is: does anyone have a work around to this issue?

Note: that I did look through help "When can I update data from a
query" and none of the scenarios seemed to match my situation.

So here's the full background:
tblItemDetails:
Item_Descriptio n_ID (key - 1:M to tblSalesDetails )
Active_Status (boolean)

tblSalesDetails :
Sales_ID (key - M:1 to tblSalesMaster)
Item_Descriptio n_ID (key - M:1 to tblItemDetails)
Count_Sold

tblSalesMaster:
Sales_ID (key - 1:M to tblSalesDetails )
Company_Locatio n
Sale_Date

Query:
SELECT tblItemDetails. Item_Descriptio n_ID,
tblItemDetails. Active_Status, tblSalesMaster. Company_Locatio n,
Max(tblSalesMas ter.Sale_Date) AS MaxOfSale_Date
FROM tblSalesMaster INNER JOIN (tblItemDetails INNER JOIN
tblSalesDetails ON tblItemDetails. Item_Descriptio n_ID =
tblSalesDetails .Menu_Descripti on_ID) ON tblSalesMaster. Sales_ID =
tblSalesDetails .Sales_ID
GROUP BY ... etc ...

I've worked around this problem by
1) unbinding the field from the control on the form
2) use a dlookup() in the on Current Event of the form to retrieve
the value
3) update the table using a SQL update query triggered in the
AfterUpdate event of the control. (yes it fires on unbound controls)
4) repeating the Dlookup()

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **