Non updatable recordset help

  • Thread starter Thread starter Matt Williamson
  • Start date Start date
M

Matt Williamson

I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM [dbo_SECURITY] As Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created, but I can't
figure out how to return the data I need and allow it to be updatable as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
 
Aggregate queries are by definition non-updateable. There is no way for the
engine to know which record to update. It's just the nature of the beast.
 
Is there any way to create a new query that uses the data returned from this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Roger Carlson said:
Aggregate queries are by definition non-updateable. There is no way for the
engine to know which record to update. It's just the nature of the beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Matt Williamson said:
I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM [dbo_SECURITY] As Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created, but I can't
figure out how to return the data I need and allow it to be updatable as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
Click to expand...
Click to expand...
 
Even if you include the aggregate function in a sub query, Access will not
allow you to update it. You need to come up with some way to query the data
without the aggregate. Could you describe your data in a bit more detail?
It's hard to know how to help you without more information.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Matt Williamson said:
Is there any way to create a new query that uses the data returned from this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Roger Carlson said:
Aggregate queries are by definition non-updateable. There is no way for the
engine to know which record to update. It's just the nature of the beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Matt Williamson said:
I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM [dbo_SECURITY]
Click to expand...
As
Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created, but I can't
figure out how to return the data I need and allow it to be updatable as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
Click to expand...
Click to expand...
Click to expand...
 
Lynn-

I eliminated the aggregate function entirely but manually taking the Tickers
returned from the query and sticking them all in a big In Statement. The sql
follows:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In
("CPL","ESE","FDX","FLT","FSR","GREY","GTW","HMT","HON","HOT","HSB","IACI","
ISO","K","LNT","MEL","MMC","NEXL","NI","NMK","NNC","NT","NWL","PA","PCL","PH
G","PMI","PZL","REI","RGS","RIG","RLAE","SCG","SFI","SRP","SSC","TRI","TXU",
"UN","UNH","UTR","VOD","WARP","WH","WST","WTC","YLH")))
ORDER BY dbo_SECURITY.TICKER;

I still am not able to update the query. What am I missing?

TIA

Matt



Lynn Trapp said:
Even if you include the aggregate function in a sub query, Access will not
allow you to update it. You need to come up with some way to query the data
without the aggregate. Could you describe your data in a bit more detail?
It's hard to know how to help you without more information.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Matt Williamson said:
Is there any way to create a new query that uses the data returned from this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Roger Carlson said:
Aggregate queries are by definition non-updateable. There is no way
Click to expand...
for
the
engine to know which record to update. It's just the nature of the beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM
Click to expand...
Click to expand...
[dbo_SECURITY]
As
Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created, but I
can't
figure out how to return the data I need and allow it to be
Click to expand...
Click to expand...
updatable
as
Click to expand...
Click to expand...
 
I just tried to open the dbo_SECURITY table by itself and I cannot edit it
either. That would explain why my previous query didn't work. Now I just
need to figure out why this table isn't editable.


Matt Williamson said:
Lynn-

I eliminated the aggregate function entirely but manually taking the Tickers
returned from the query and sticking them all in a big In Statement. The sql
follows:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In
("CPL","ESE","FDX","FLT","FSR","GREY","GTW","HMT","HON","HOT","HSB","IACI","ISO","K","LNT","MEL","MMC","NEXL","NI","NMK","NNC","NT","NWL","PA","PCL","PHG","PMI","PZL","REI","RGS","RIG","RLAE","SCG","SFI","SRP","SSC","TRI","TXU",
"UN","UNH","UTR","VOD","WARP","WH","WST","WTC","YLH")))
ORDER BY dbo_SECURITY.TICKER;

I still am not able to update the query. What am I missing?

TIA

Matt



Lynn Trapp said:
Even if you include the aggregate function in a sub query, Access will not
allow you to update it. You need to come up with some way to query the data
without the aggregate. Could you describe your data in a bit more detail?
It's hard to know how to help you without more information.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Matt Williamson said:
Is there any way to create a new query that uses the data returned
Click to expand...
from
this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Aggregate queries are by definition non-updateable. There is no way for
the
engine to know which record to update. It's just the nature of the beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM
Click to expand...
[dbo_SECURITY]
As
Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not
Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created,
Click to expand...
Click to expand...
but
I updatable
Click to expand...
Click to expand...
 
But if you are only using the aggregate function in the where clause as the OP
is, then the query is updatable. Take a look at the find duplicates queries
that are built by the wizard as an example.

You are correct that you can't use aggregate functions in the main query and get
an updatable query.

Roger said:
Aggregate queries are by definition non-updateable. There is no way for the
engine to know which record to update. It's just the nature of the beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Matt Williamson said:
I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM [dbo_SECURITY] As Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created, but I can't
figure out how to return the data I need and allow it to be updatable as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
Click to expand...
Click to expand...
 
This looks like a linked table from SQL Server. Do you have permission to
edit it? Does the table have a primary key that Access knows about?

--
Duane Hookom
MS Access MVP


Matt Williamson said:
I just tried to open the dbo_SECURITY table by itself and I cannot edit it
either. That would explain why my previous query didn't work. Now I just
need to figure out why this table isn't editable.


Matt Williamson said:
Lynn-

I eliminated the aggregate function entirely but manually taking the Tickers
returned from the query and sticking them all in a big In Statement. The sql
follows:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In
Click to expand...
("CPL","ESE","FDX","FLT","FSR","GREY","GTW","HMT","HON","HOT","HSB","IACI","ISO","K","LNT","MEL","MMC","NEXL","NI","NMK","NNC","NT","NWL","PA","PCL","PHG","PMI","PZL","REI","RGS","RIG","RLAE","SCG","SFI","SRP","SSC","TRI","TXU",
"UN","UNH","UTR","VOD","WARP","WH","WST","WTC","YLH")))
ORDER BY dbo_SECURITY.TICKER;

I still am not able to update the query. What am I missing?

TIA

Matt



Lynn Trapp said:
Even if you include the aggregate function in a sub query, Access will not
allow you to update it. You need to come up with some way to query the data
without the aggregate. Could you describe your data in a bit more detail?
It's hard to know how to help you without more information.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Is there any way to create a new query that uses the data returned from
this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Aggregate queries are by definition non-updateable. There is no
Click to expand...
way
for
the
engine to know which record to update. It's just the nature of the
beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a query in access 97 that I'd like to be able to update, but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM [dbo_SECURITY]
As
Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND ((dbo_SECURITY.SECID) Not
Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being created,
Click to expand...
but
I
can't
figure out how to return the data I need and allow it to be updatable
as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
Click to expand...
Click to expand...
Click to expand...
 
Duane-

It was missing a primary key definition. I created a new database, linked it
to that table and set a primary key and I am able to update it fine.

Thanks

Matt


Duane Hookom said:
This looks like a linked table from SQL Server. Do you have permission to
edit it? Does the table have a primary key that Access knows about?

--
Duane Hookom
MS Access MVP


Matt Williamson said:
I just tried to open the dbo_SECURITY table by itself and I cannot edit it
either. That would explain why my previous query didn't work. Now I just
need to figure out why this table isn't editable.


The
sql
Click to expand...
("CPL","ESE","FDX","FLT","FSR","GREY","GTW","HMT","HON","HOT","HSB","IACI","ISO","K","LNT","MEL","MMC","NEXL","NI","NMK","NNC","NT","NWL","PA","PCL","PHG","PMI","PZL","REI","RGS","RIG","RLAE","SCG","SFI","SRP","SSC","TRI","TXU",
"UN","UNH","UTR","VOD","WARP","WH","WST","WTC","YLH")))
ORDER BY dbo_SECURITY.TICKER;

I still am not able to update the query. What am I missing?

TIA

Matt



Even if you include the aggregate function in a sub query, Access
Click to expand...
will
not
allow you to update it. You need to come up with some way to query the
data
without the aggregate. Could you describe your data in a bit more detail?
It's hard to know how to help you without more information.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Is there any way to create a new query that uses the data returned from
this
one to make an updatable recordset? I tried breaking out the aggregate
funtion into a separate query, but I get the same results.

TIA

Matt

Aggregate queries are by definition non-updateable. There is no way
for
the
engine to know which record to update. It's just the nature of the
beast.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a query in access 97 that I'd like to be able to
Click to expand...
update,
but
currently I cannot.

The SQL for the query is:

SELECT dbo_SECURITY.TICKER, dbo_SECURITY.SECID, dbo_SECURITY.ISSUER,
dbo_SECURITY.ISSUE
FROM dbo_SECURITY
WHERE (((dbo_SECURITY.TICKER) In (SELECT [TICKER] FROM
[dbo_SECURITY]
As
Tmp
GROUP BY [TICKER] HAVING Count(*)>1 )) AND
Click to expand...
((dbo_SECURITY.SECID)
Not
Like
("05565UAA7*")) AND ((Left([SECID],4)) Not In
("GNMA","FNMA","FHLM","TIPS","TPS0")))
ORDER BY dbo_SECURITY.TICKER;

I believe it has to do with the Tmp table that is being
Click to expand...
created,
but
I
can't
figure out how to return the data I need and allow it to be
updatable
as
well. The dbo_Security table is in a sybase 11.5 database.

I'd appreciate any advise on this..

TIA

Matt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top