cannot write to database query

I have created a query using a table and another query but cannot write into 
it.  I dont get an error message at all just a bleep.  I have tried doing the 
query using the 3 tables that I need but the same thing happens.  Each 
query/table concerned has an ID field which is linked to each other in the 
relationships area.  

0
Utf
2/19/2008 12:34:00 PM
access.queries 6343 articles. 1 followers. Follow

12 Replies
711 Views

Similar Articles

[PageSpeed] 20

That means that you have created a query that is not updatable.  Beyond that 
we would need to know more about your table structure and query.

As a guess you may need to use a Main form and subform(s) to control the 
data entry in this situation.

In Access Help type the following in the Answer Wizard tab
   When can I update data from a query
Select that from the list for an explanation of some of the causes and 
alternative solutions.

Access MVP Allen Browne has summarized the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM 
clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or 
the database is on read-only media (e.g. CD-ROM, network drive without write 
privileges.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Asherman" <Asherman@discussions.microsoft.com> wrote in message 
news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
>I have created a query using a table and another query but cannot write 
>into
> it.  I dont get an error message at all just a bleep.  I have tried doing 
> the
> query using the 3 tables that I need but the same thing happens.  Each
> query/table concerned has an ID field which is linked to each other in the
> relationships area.
> 


0
John
2/19/2008 1:37:04 PM
Thanks John

What I failed to mention is that this database was fully functioning last 
month but since then the linked tables have had to be re linked to the sql 
database.  A collegue did suggest that I compact and repair the database but 
this did not work.  However whilst trying to find a solution I did a 
replicate it, which fixed the problem, but when I tried it on a similar 
database it did not work.  Someone else told me that it looked as if the 
relationships needed refreshing as some of them may still be linked 
incorrectly even thought they look OK.  I dont quite understand that if 
anyone has any suggestions?!  In the end instead of linking the tables for 
the other database, I imported the ones I needed but still have the same 
problem.

"John Spencer" wrote:

> That means that you have created a query that is not updatable.  Beyond that 
> we would need to know more about your table structure and query.
> 
> As a guess you may need to use a Main form and subform(s) to control the 
> data entry in this situation.
> 
> In Access Help type the following in the Answer Wizard tab
>    When can I update data from a query
> Select that from the list for an explanation of some of the causes and 
> alternative solutions.
> 
> Access MVP Allen Browne has summarized the reasons:
> 
> Query results will be read-only if any of the following apply:
> .. It has a GROUP BY clause (totals query).
> .. It has a TRANSFORM clause (crosstab query).
> .. It contains a DISTINCT predicate.
> .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
> (performs aggregation).
> .. It involves a UNION.
> .. It has a subquery in the SELECT clause.
> .. It uses JOINs of different directions on multiple tables in the FROM 
> clause.
> .. The query's Recordset Type property is set to Snapshot.
> .. The query is based on another query that is read-only (stacked query.)
> .. Your permissions are read-only (Access security.)
> .. The database is opened read-only, or the file attributes are read-only, or 
> the database is on read-only media (e.g. CD-ROM, network drive without write 
> privileges.)
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia.
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
> >I have created a query using a table and another query but cannot write 
> >into
> > it.  I dont get an error message at all just a bleep.  I have tried doing 
> > the
> > query using the 3 tables that I need but the same thing happens.  Each
> > query/table concerned has an ID field which is linked to each other in the
> > relationships area.
> > 
> 
> 
> 
0
Utf
2/19/2008 2:06:00 PM
I would suggest you provide more details as asked by John "Beyond that we 
would need to know more about your table structure and query."

Are there primary key fields in the tables and does Access identify them? 
What are the SQL views of significant queries?

-- 
Duane Hookom
Microsoft Access MVP


"Asherman" wrote:

> Thanks John
> 
> What I failed to mention is that this database was fully functioning last 
> month but since then the linked tables have had to be re linked to the sql 
> database.  A collegue did suggest that I compact and repair the database but 
> this did not work.  However whilst trying to find a solution I did a 
> replicate it, which fixed the problem, but when I tried it on a similar 
> database it did not work.  Someone else told me that it looked as if the 
> relationships needed refreshing as some of them may still be linked 
> incorrectly even thought they look OK.  I dont quite understand that if 
> anyone has any suggestions?!  In the end instead of linking the tables for 
> the other database, I imported the ones I needed but still have the same 
> problem.
> 
> "John Spencer" wrote:
> 
> > That means that you have created a query that is not updatable.  Beyond that 
> > we would need to know more about your table structure and query.
> > 
> > As a guess you may need to use a Main form and subform(s) to control the 
> > data entry in this situation.
> > 
> > In Access Help type the following in the Answer Wizard tab
> >    When can I update data from a query
> > Select that from the list for an explanation of some of the causes and 
> > alternative solutions.
> > 
> > Access MVP Allen Browne has summarized the reasons:
> > 
> > Query results will be read-only if any of the following apply:
> > .. It has a GROUP BY clause (totals query).
> > .. It has a TRANSFORM clause (crosstab query).
> > .. It contains a DISTINCT predicate.
> > .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
> > (performs aggregation).
> > .. It involves a UNION.
> > .. It has a subquery in the SELECT clause.
> > .. It uses JOINs of different directions on multiple tables in the FROM 
> > clause.
> > .. The query's Recordset Type property is set to Snapshot.
> > .. The query is based on another query that is read-only (stacked query.)
> > .. Your permissions are read-only (Access security.)
> > .. The database is opened read-only, or the file attributes are read-only, or 
> > the database is on read-only media (e.g. CD-ROM, network drive without write 
> > privileges.)
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia.
> > 
> > -- 
> > John Spencer
> > Access MVP 2002-2005, 2007-2008
> > Center for Health Program Development and Management
> > University of Maryland Baltimore County
> > ..
> > 
> > "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> > news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
> > >I have created a query using a table and another query but cannot write 
> > >into
> > > it.  I dont get an error message at all just a bleep.  I have tried doing 
> > > the
> > > query using the 3 tables that I need but the same thing happens.  Each
> > > query/table concerned has an ID field which is linked to each other in the
> > > relationships area.
> > > 
> > 
> > 
> > 
0
Utf
2/19/2008 2:23:05 PM
Hope this helps, as I am still very much a novice in this area.

The tables are ordinary tables that I have imported from a sql database that 
I have read only access to.  I can change the data within these tables.  The 
UserID  in the dbo_UserDetails table is the primary key and is indexed.  This 
is linked by a relationship to the StudentID in the other table, but only 
where the joined fields in both tables are equal.   The StudentID is also a 
primary key and indexed.


The select query is as shown below, I cannot change the information in the 
query:

SELECT dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName, 
[lastname]+", "+[preffirstname] AS [Full Name], 
dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName, 
dbo_MIS_CORE_VIEW_StudentListSimplified.YearGrpID, 
IIf([yeargrpid]=1,10,IIf([yeargrpid]=2,8,IIf([yeargrpid]=3,11,IIf([yeargrpid]=4,9,IIf([yeargrpid]=5,7,0))))) AS [Yr Group], dbo_UserDetails.DateOfExit, *
FROM dbo_MIS_CORE_VIEW_StudentListSimplified INNER JOIN dbo_UserDetails ON 
dbo_MIS_CORE_VIEW_StudentListSimplified.StudentID = dbo_UserDetails.UserID
WHERE (((dbo_UserDetails.PrefFirstName)<>"Emarkbook") AND 
((dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName)<>"LEAVER") AND 
((dbo_UserDetails.UserType)=1))
ORDER BY dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName;

Is there anything else you may need to understand my problem?



"Duane Hookom" wrote:

> I would suggest you provide more details as asked by John "Beyond that we 
> would need to know more about your table structure and query."
> 
> Are there primary key fields in the tables and does Access identify them? 
> What are the SQL views of significant queries?
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Asherman" wrote:
> 
> > Thanks John
> > 
> > What I failed to mention is that this database was fully functioning last 
> > month but since then the linked tables have had to be re linked to the sql 
> > database.  A collegue did suggest that I compact and repair the database but 
> > this did not work.  However whilst trying to find a solution I did a 
> > replicate it, which fixed the problem, but when I tried it on a similar 
> > database it did not work.  Someone else told me that it looked as if the 
> > relationships needed refreshing as some of them may still be linked 
> > incorrectly even thought they look OK.  I dont quite understand that if 
> > anyone has any suggestions?!  In the end instead of linking the tables for 
> > the other database, I imported the ones I needed but still have the same 
> > problem.
> > 
> > "John Spencer" wrote:
> > 
> > > That means that you have created a query that is not updatable.  Beyond that 
> > > we would need to know more about your table structure and query.
> > > 
> > > As a guess you may need to use a Main form and subform(s) to control the 
> > > data entry in this situation.
> > > 
> > > In Access Help type the following in the Answer Wizard tab
> > >    When can I update data from a query
> > > Select that from the list for an explanation of some of the causes and 
> > > alternative solutions.
> > > 
> > > Access MVP Allen Browne has summarized the reasons:
> > > 
> > > Query results will be read-only if any of the following apply:
> > > .. It has a GROUP BY clause (totals query).
> > > .. It has a TRANSFORM clause (crosstab query).
> > > .. It contains a DISTINCT predicate.
> > > .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
> > > (performs aggregation).
> > > .. It involves a UNION.
> > > .. It has a subquery in the SELECT clause.
> > > .. It uses JOINs of different directions on multiple tables in the FROM 
> > > clause.
> > > .. The query's Recordset Type property is set to Snapshot.
> > > .. The query is based on another query that is read-only (stacked query.)
> > > .. Your permissions are read-only (Access security.)
> > > .. The database is opened read-only, or the file attributes are read-only, or 
> > > the database is on read-only media (e.g. CD-ROM, network drive without write 
> > > privileges.)
> > > 
> > > -- 
> > > Allen Browne - Microsoft MVP.  Perth, Western Australia.
> > > 
> > > -- 
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2008
> > > Center for Health Program Development and Management
> > > University of Maryland Baltimore County
> > > ..
> > > 
> > > "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> > > news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
> > > >I have created a query using a table and another query but cannot write 
> > > >into
> > > > it.  I dont get an error message at all just a bleep.  I have tried doing 
> > > > the
> > > > query using the 3 tables that I need but the same thing happens.  Each
> > > > query/table concerned has an ID field which is linked to each other in the
> > > > relationships area.
> > > > 
> > > 
> > > 
> > > 
0
Utf
2/20/2008 10:51:02 AM
In addition to the previous message with the sql details, I have exported the 
two tables into a new database and create the query as per the original one - 
everything works perfectly and I can amend it, however if I copy or export 
the original query into the new database then the old problem re ocuurs.

This is getting confusing but I bet there is a simple explanation.


"Duane Hookom" wrote:

> I would suggest you provide more details as asked by John "Beyond that we 
> would need to know more about your table structure and query."
> 
> Are there primary key fields in the tables and does Access identify them? 
> What are the SQL views of significant queries?
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Asherman" wrote:
> 
> > Thanks John
> > 
> > What I failed to mention is that this database was fully functioning last 
> > month but since then the linked tables have had to be re linked to the sql 
> > database.  A collegue did suggest that I compact and repair the database but 
> > this did not work.  However whilst trying to find a solution I did a 
> > replicate it, which fixed the problem, but when I tried it on a similar 
> > database it did not work.  Someone else told me that it looked as if the 
> > relationships needed refreshing as some of them may still be linked 
> > incorrectly even thought they look OK.  I dont quite understand that if 
> > anyone has any suggestions?!  In the end instead of linking the tables for 
> > the other database, I imported the ones I needed but still have the same 
> > problem.
> > 
> > "John Spencer" wrote:
> > 
> > > That means that you have created a query that is not updatable.  Beyond that 
> > > we would need to know more about your table structure and query.
> > > 
> > > As a guess you may need to use a Main form and subform(s) to control the 
> > > data entry in this situation.
> > > 
> > > In Access Help type the following in the Answer Wizard tab
> > >    When can I update data from a query
> > > Select that from the list for an explanation of some of the causes and 
> > > alternative solutions.
> > > 
> > > Access MVP Allen Browne has summarized the reasons:
> > > 
> > > Query results will be read-only if any of the following apply:
> > > .. It has a GROUP BY clause (totals query).
> > > .. It has a TRANSFORM clause (crosstab query).
> > > .. It contains a DISTINCT predicate.
> > > .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
> > > (performs aggregation).
> > > .. It involves a UNION.
> > > .. It has a subquery in the SELECT clause.
> > > .. It uses JOINs of different directions on multiple tables in the FROM 
> > > clause.
> > > .. The query's Recordset Type property is set to Snapshot.
> > > .. The query is based on another query that is read-only (stacked query.)
> > > .. Your permissions are read-only (Access security.)
> > > .. The database is opened read-only, or the file attributes are read-only, or 
> > > the database is on read-only media (e.g. CD-ROM, network drive without write 
> > > privileges.)
> > > 
> > > -- 
> > > Allen Browne - Microsoft MVP.  Perth, Western Australia.
> > > 
> > > -- 
> > > John Spencer
> > > Access MVP 2002-2005, 2007-2008
> > > Center for Health Program Development and Management
> > > University of Maryland Baltimore County
> > > ..
> > > 
> > > "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> > > news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
> > > >I have created a query using a table and another query but cannot write 
> > > >into
> > > > it.  I dont get an error message at all just a bleep.  I have tried doing 
> > > > the
> > > > query using the 3 tables that I need but the same thing happens.  Each
> > > > query/table concerned has an ID field which is linked to each other in the
> > > > relationships area.
> > > > 
> > > 
> > > 
> > > 
0
Utf
2/20/2008 11:53:01 AM
OK now I am confused.

I created a new database and exported the two tables into it, then 
exported/copied the query.  The same problem arose.  However when I did a new 
query with the same parameters in the new database everything worked and I 
could amend it. So I tried doing a new query in the original database with 
the same parameters and I can amend that one.

HELP! 


"Asherman" wrote:

> Hope this helps, as I am still very much a novice in this area.
> 
> The tables are ordinary tables that I have imported from a sql database that 
> I have read only access to.  I can change the data within these tables.  The 
> UserID  in the dbo_UserDetails table is the primary key and is indexed.  This 
> is linked by a relationship to the StudentID in the other table, but only 
> where the joined fields in both tables are equal.   The StudentID is also a 
> primary key and indexed.
> 
> 
> The select query is as shown below, I cannot change the information in the 
> query:
> 
> SELECT dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName, 
> [lastname]+", "+[preffirstname] AS [Full Name], 
> dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName, 
> dbo_MIS_CORE_VIEW_StudentListSimplified.YearGrpID, 
> IIf([yeargrpid]=1,10,IIf([yeargrpid]=2,8,IIf([yeargrpid]=3,11,IIf([yeargrpid]=4,9,IIf([yeargrpid]=5,7,0))))) AS [Yr Group], dbo_UserDetails.DateOfExit, *
> FROM dbo_MIS_CORE_VIEW_StudentListSimplified INNER JOIN dbo_UserDetails ON 
> dbo_MIS_CORE_VIEW_StudentListSimplified.StudentID = dbo_UserDetails.UserID
> WHERE (((dbo_UserDetails.PrefFirstName)<>"Emarkbook") AND 
> ((dbo_MIS_CORE_VIEW_StudentListSimplified.TutGrpName)<>"LEAVER") AND 
> ((dbo_UserDetails.UserType)=1))
> ORDER BY dbo_UserDetails.LastName, dbo_UserDetails.PrefFirstName;
> 
> Is there anything else you may need to understand my problem?
> 
> 
> 
> "Duane Hookom" wrote:
> 
> > I would suggest you provide more details as asked by John "Beyond that we 
> > would need to know more about your table structure and query."
> > 
> > Are there primary key fields in the tables and does Access identify them? 
> > What are the SQL views of significant queries?
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "Asherman" wrote:
> > 
> > > Thanks John
> > > 
> > > What I failed to mention is that this database was fully functioning last 
> > > month but since then the linked tables have had to be re linked to the sql 
> > > database.  A collegue did suggest that I compact and repair the database but 
> > > this did not work.  However whilst trying to find a solution I did a 
> > > replicate it, which fixed the problem, but when I tried it on a similar 
> > > database it did not work.  Someone else told me that it looked as if the 
> > > relationships needed refreshing as some of them may still be linked 
> > > incorrectly even thought they look OK.  I dont quite understand that if 
> > > anyone has any suggestions?!  In the end instead of linking the tables for 
> > > the other database, I imported the ones I needed but still have the same 
> > > problem.
> > > 
> > > "John Spencer" wrote:
> > > 
> > > > That means that you have created a query that is not updatable.  Beyond that 
> > > > we would need to know more about your table structure and query.
> > > > 
> > > > As a guess you may need to use a Main form and subform(s) to control the 
> > > > data entry in this situation.
> > > > 
> > > > In Access Help type the following in the Answer Wizard tab
> > > >    When can I update data from a query
> > > > Select that from the list for an explanation of some of the causes and 
> > > > alternative solutions.
> > > > 
> > > > Access MVP Allen Browne has summarized the reasons:
> > > > 
> > > > Query results will be read-only if any of the following apply:
> > > > .. It has a GROUP BY clause (totals query).
> > > > .. It has a TRANSFORM clause (crosstab query).
> > > > .. It contains a DISTINCT predicate.
> > > > .. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause 
> > > > (performs aggregation).
> > > > .. It involves a UNION.
> > > > .. It has a subquery in the SELECT clause.
> > > > .. It uses JOINs of different directions on multiple tables in the FROM 
> > > > clause.
> > > > .. The query's Recordset Type property is set to Snapshot.
> > > > .. The query is based on another query that is read-only (stacked query.)
> > > > .. Your permissions are read-only (Access security.)
> > > > .. The database is opened read-only, or the file attributes are read-only, or 
> > > > the database is on read-only media (e.g. CD-ROM, network drive without write 
> > > > privileges.)
> > > > 
> > > > -- 
> > > > Allen Browne - Microsoft MVP.  Perth, Western Australia.
> > > > 
> > > > -- 
> > > > John Spencer
> > > > Access MVP 2002-2005, 2007-2008
> > > > Center for Health Program Development and Management
> > > > University of Maryland Baltimore County
> > > > ..
> > > > 
> > > > "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> > > > news:E90C4D66-A306-482C-8D6B-A8AC3CD61CBC@microsoft.com...
> > > > >I have created a query using a table and another query but cannot write 
> > > > >into
> > > > > it.  I dont get an error message at all just a bleep.  I have tried doing 
> > > > > the
> > > > > query using the 3 tables that I need but the same thing happens.  Each
> > > > > query/table concerned has an ID field which is linked to each other in the
> > > > > relationships area.
> > > > > 
> > > > 
> > > > 
> > > > 
0
Utf
2/20/2008 12:10:00 PM
One possibility

Check the properties of the OLD query.
-- Recordset Type should not be Snapshot (not updatable)

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Asherman" <Asherman@discussions.microsoft.com> wrote in message 
news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
> OK now I am confused.
>
> I created a new database and exported the two tables into it, then
> exported/copied the query.  The same problem arose.  However when I did a 
> new
> query with the same parameters in the new database everything worked and I
> could amend it. So I tried doing a new query in the original database with
> the same parameters and I can amend that one.
>
> HELP!
>
>
> "Asherman" wrote:
>
>> >> > > > 


0
John
2/20/2008 12:49:58 PM
Thanks John

I cannot see anything about the recordset in the query properties, it just 
has the general tab showing the name, description and if it is hidden etc.  
Should I be looking somewhere else?

"John Spencer" wrote:

> One possibility
> 
> Check the properties of the OLD query.
> -- Recordset Type should not be Snapshot (not updatable)
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
> > OK now I am confused.
> >
> > I created a new database and exported the two tables into it, then
> > exported/copied the query.  The same problem arose.  However when I did a 
> > new
> > query with the same parameters in the new database everything worked and I
> > could amend it. So I tried doing a new query in the original database with
> > the same parameters and I can amend that one.
> >
> > HELP!
> >
> >
> > "Asherman" wrote:
> >
> >> >> > > > 
> 
> 
> 
0
Utf
2/20/2008 1:29:02 PM
Yes, you need to look elsewhere.

Open up the query in design view
Double-click in the upper area in the blank space (not on a table).
The properties window should now show
-- Description
-- Default View
-- Output all fields
-- a whole bunch more properties for the query

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Asherman" <Asherman@discussions.microsoft.com> wrote in message 
news:BAA01A7B-C2D6-4415-887D-9673A68D53EB@microsoft.com...
> Thanks John
>
> I cannot see anything about the recordset in the query properties, it just
> has the general tab showing the name, description and if it is hidden etc.
> Should I be looking somewhere else?
>
> "John Spencer" wrote:
>
>> One possibility
>>
>> Check the properties of the OLD query.
>> -- Recordset Type should not be Snapshot (not updatable)
>>
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
>> news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
>> > OK now I am confused.
>> >
>> > I created a new database and exported the two tables into it, then
>> > exported/copied the query.  The same problem arose.  However when I did 
>> > a
>> > new
>> > query with the same parameters in the new database everything worked 
>> > and I
>> > could amend it. So I tried doing a new query in the original database 
>> > with
>> > the same parameters and I can amend that one.
>> >
>> > HELP!
>> >
>> >
>> > "Asherman" wrote:
>> >
>> >> >> > > >
>>
>>
>> 


0
John
2/20/2008 1:57:34 PM
You learn something new every day.

I have checked the properties and the record set is a dynaset.



"John Spencer" wrote:

> Yes, you need to look elsewhere.
> 
> Open up the query in design view
> Double-click in the upper area in the blank space (not on a table).
> The properties window should now show
> -- Description
> -- Default View
> -- Output all fields
> -- a whole bunch more properties for the query
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> news:BAA01A7B-C2D6-4415-887D-9673A68D53EB@microsoft.com...
> > Thanks John
> >
> > I cannot see anything about the recordset in the query properties, it just
> > has the general tab showing the name, description and if it is hidden etc.
> > Should I be looking somewhere else?
> >
> > "John Spencer" wrote:
> >
> >> One possibility
> >>
> >> Check the properties of the OLD query.
> >> -- Recordset Type should not be Snapshot (not updatable)
> >>
> >> -- 
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2008
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
> >> news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
> >> > OK now I am confused.
> >> >
> >> > I created a new database and exported the two tables into it, then
> >> > exported/copied the query.  The same problem arose.  However when I did 
> >> > a
> >> > new
> >> > query with the same parameters in the new database everything worked 
> >> > and I
> >> > could amend it. So I tried doing a new query in the original database 
> >> > with
> >> > the same parameters and I can amend that one.
> >> >
> >> > HELP!
> >> >
> >> >
> >> > "Asherman" wrote:
> >> >
> >> >> >> > > >
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/20/2008 3:54:03 PM
Well, at this point all I can say is

Score
Computer   1
Human(s)   0

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Asherman" <Asherman@discussions.microsoft.com> wrote in message 
news:1A3F7751-0833-4D1F-9777-2527D08C4557@microsoft.com...
> You learn something new every day.
>
> I have checked the properties and the record set is a dynaset.
>
>
>
> "John Spencer" wrote:
>
>> Yes, you need to look elsewhere.
>>
>> Open up the query in design view
>> Double-click in the upper area in the blank space (not on a table).
>> The properties window should now show
>> -- Description
>> -- Default View
>> -- Output all fields
>> -- a whole bunch more properties for the query
>>
>> -- 
>> John Spencer
>> Access MVP 2002-2005, 2007-2008
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
>> news:BAA01A7B-C2D6-4415-887D-9673A68D53EB@microsoft.com...
>> > Thanks John
>> >
>> > I cannot see anything about the recordset in the query properties, it 
>> > just
>> > has the general tab showing the name, description and if it is hidden 
>> > etc.
>> > Should I be looking somewhere else?
>> >
>> > "John Spencer" wrote:
>> >
>> >> One possibility
>> >>
>> >> Check the properties of the OLD query.
>> >> -- Recordset Type should not be Snapshot (not updatable)
>> >>
>> >> -- 
>> >> John Spencer
>> >> Access MVP 2002-2005, 2007-2008
>> >> Center for Health Program Development and Management
>> >> University of Maryland Baltimore County
>> >> ..
>> >>
>> >> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
>> >> news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
>> >> > OK now I am confused.
>> >> >
>> >> > I created a new database and exported the two tables into it, then
>> >> > exported/copied the query.  The same problem arose.  However when I 
>> >> > did
>> >> > a
>> >> > new
>> >> > query with the same parameters in the new database everything worked
>> >> > and I
>> >> > could amend it. So I tried doing a new query in the original 
>> >> > database
>> >> > with
>> >> > the same parameters and I can amend that one.
>> >> >
>> >> > HELP!
>> >> >
>> >> >
>> >> > "Asherman" wrote:
>> >> >
>> >> >> >> > > >
>> >>
>> >>
>> >>
>>
>>
>> 


0
John
2/20/2008 4:40:30 PM
Thanks John for the first laugh of the day.

At least by re writing the query the thing works now. I think I will try to 
find another way to record this info for next Sept, I dont think I can go 
through this again.

many thanks

:)

"John Spencer" wrote:

> Well, at this point all I can say is
> 
> Score
> Computer   1
> Human(s)   0
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Asherman" <Asherman@discussions.microsoft.com> wrote in message 
> news:1A3F7751-0833-4D1F-9777-2527D08C4557@microsoft.com...
> > You learn something new every day.
> >
> > I have checked the properties and the record set is a dynaset.
> >
> >
> >
> > "John Spencer" wrote:
> >
> >> Yes, you need to look elsewhere.
> >>
> >> Open up the query in design view
> >> Double-click in the upper area in the blank space (not on a table).
> >> The properties window should now show
> >> -- Description
> >> -- Default View
> >> -- Output all fields
> >> -- a whole bunch more properties for the query
> >>
> >> -- 
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2008
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
> >> news:BAA01A7B-C2D6-4415-887D-9673A68D53EB@microsoft.com...
> >> > Thanks John
> >> >
> >> > I cannot see anything about the recordset in the query properties, it 
> >> > just
> >> > has the general tab showing the name, description and if it is hidden 
> >> > etc.
> >> > Should I be looking somewhere else?
> >> >
> >> > "John Spencer" wrote:
> >> >
> >> >> One possibility
> >> >>
> >> >> Check the properties of the OLD query.
> >> >> -- Recordset Type should not be Snapshot (not updatable)
> >> >>
> >> >> -- 
> >> >> John Spencer
> >> >> Access MVP 2002-2005, 2007-2008
> >> >> Center for Health Program Development and Management
> >> >> University of Maryland Baltimore County
> >> >> ..
> >> >>
> >> >> "Asherman" <Asherman@discussions.microsoft.com> wrote in message
> >> >> news:5B31AABE-1BF6-4414-8B14-D6811845B1BF@microsoft.com...
> >> >> > OK now I am confused.
> >> >> >
> >> >> > I created a new database and exported the two tables into it, then
> >> >> > exported/copied the query.  The same problem arose.  However when I 
> >> >> > did
> >> >> > a
> >> >> > new
> >> >> > query with the same parameters in the new database everything worked
> >> >> > and I
> >> >> > could amend it. So I tried doing a new query in the original 
> >> >> > database
> >> >> > with
> >> >> > the same parameters and I can amend that one.
> >> >> >
> >> >> > HELP!
> >> >> >
> >> >> >
> >> >> > "Asherman" wrote:
> >> >> >
> >> >> >> >> > > >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
2/21/2008 8:58:00 AM
Reply:

Similar Artilces:

Xpath Query #3
Hi All Couldn't find an xpath specifig UG, so I'm posting this here. I have XML in the following structure: <Assignments> <Assignment> <ID>123</ID> <Description>Test Assignment 1</Description> <DueDate>2006/10/10</DueDate> <Tasks> <Task> <ID>1</ID> <Description>Task 1</Description> </Task> <Task> <ID>2</ID> <Description>Task 2</Descri...

Database replication and removing names of old databases
When you start the process of synchronising one database with another, you are asked which databse you wish to synchronsie with. The dialogue box shows ALL names of databases that you have previoulsy synchronsied with and this list can become quite long (an dyou cannot always see the full names)- is there any way of getting rid of the history as it were. I realise ofcourse you can browse to the right database. =?Utf-8?B?SmFjayBhdCBSZWxheGVk?= <JackatRelaxed@discussions.microsoft.com> wrote in news:1CB9EAEE-51D3-435D-9103-1BB1D81AB851@microsoft.com: > When you start the process...

Converting SQL database to Access database
I am not too familiar with either of these products. Can someone tell me if an SQL database can be viewed and manipulated through Access? If not how can I convert the SQL database into an Access database. Thanks. Chirvan You can use the Access Table linking feature to view tables from SQL server via ODBC. If you have a unique index on the table in SQL Server, then you can manipulate the data. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Chirvan" <cedross@apos.on.ca> wrote in message news:071301c37de8$09e...

Database relationships
In Tools->Relationships there is a setup for database relationships. In form editing, clicking outside the form area, (data)RecordSource, we have also access what seems to be a similar setup. What is the difference between them? Thanks H. Martins The Relationships window opened from the Tools menu is the place where you define and view the relationships between your various tables. It is a great way to stay on top of all the information about how your table connect to each other, as well as the way you create the relationships with referential integrity. Personally, I find that so...

Query design question?
Query1 retrieves data for 2,000 records from a series of linked tables that have about 100,000 recordset Query2 retrieves data for 5,000 records from another series of linked tables that has about a 100,000 recordset as well. Query1 and Query2 are linked on a customer ID (e.g.) Should there be a simple join between Query1 and Query2 on Customer ID, or would there there be a benefit to limiting the records in Query2 first? Yes, SQL Server app - have an old stopwatch, but didn't know if some general guidelines applied. "Jerry Whittle" <JerryWhittle@discussions.micros...

Entourage and Database Utility
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop My Entourage program indicated that the database needed to be rebuilt. Database Utility successfully rebuild theEntourage database but every time it completed the exercise, Database Utility would request that the process be repeated. I have now repeated this at least six times! In addition, the option of setting database preferences could not be activated. In order to address this headache, I have also deleted the Microsoft references in the Library Preferences (and with that lost my well esta...

Limiting processing power for a specific query
I have a query (within an SSRS report) that does some very complex calculations across a large amount of data. This report takes a considerable amount of time to run (around 5 minutes). The amount of time is not the problem. The problem is that the SQL Server ends up being completely bogged down while this report runs and for a while afterwards as well. The other users (100+) that are accessing the database through my application are pretty much unable to use the system because the server's CPU usage is up around 100% until the report is finished. I was wondering if someone c...

XML updating,querying question #2
I use a TreeView WebControl Menu which i installed from microsoft. But the TreeView XML that can be loaded with the Webcontrol is very specific.For example (The root as to be TREENODES!!):- <?xml version="1.0" encoding="utf-8"?> <TREENODES> <TreeNode Text="Contracts"> <TreeNode Text="Contract Pads" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="Points Deduction Report" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="" NavigateUrl=...

Query question
Hi, I am stuck with a programming problem and I'm wondering if anyone can help me. Running SQL 2005. Here's a sample of my data: CREATE TABLE #temp (class varchar(1), referral varchar(2)) INSERT INTO #temp VALUES ('A', 'BB') INSERT INTO #temp VALUES ('A', 'AA') INSERT INTO #temp VALUES ('B', 'BB') INSERT INTO #temp VALUES ('B', 'CC') INSERT INTO #temp VALUES ('C', 'AA') INSERT INTO #temp VALUES ('C', 'BB') INSERT INTO #temp VALUES ('A', 'DD...

Table name through query
Hi All, I wanted to know if its possible to find the names of all the tables in a database. i remember in Oracle, query 'Select * from desc;" returns all the table name which have been created. Is it possible in access too? Thanks, Navin Navin, Try this: SELECT [Name], [Type] FROM MSysObjects WHERE ([Type] = 1 OR [Type] = 4) AND (Left([Name],4) <> "MSys" AND Left([Name],1) <> "~") ORDER BY [Name] Those tables with Type = 4 are linked tables. Regards, Graham R Seach Microsoft Access MVP Sydney, Australia "navin" <navin.narayana@gm...

Query-based DL based on Group Membership
I need to create a query-based distribution list based on security group members. Is this possible? If so, how? In news:626C54E1-41E6-416B-932A-BFFF69BDB8CA@microsoft.com, Leni1 <Leni1@discussions.microsoft.com> typed: > I need to create a query-based distribution list based on security > group members. Is this possible? If so, how? Why not just mail-enable the security group, if you want everyone in it to be a member of the DL anyway? In the QBDG, use the query: (&(objectClass=user)(mailnickname=*)(memberOf=distinguishedName of Security Group)) *can get distinguished...

Automate dynamic Charts from Macro Web Query
Hello all Excel users- I am a bit new to the list and cannot find a solution online as of yet I am doing a web query to bring in information and then running a sor on the information. I want to take the data (static columns wit Dynamic rows) and plot the information in stacked 3D column graph. The data is CPU-Utilization breakdown by percent ranges across multipl servers: Servename Month 0%-9% 10%-19% 20%-29%.....100% Server 1 January .33 .19 .46 0 Now I can't figure a way to accomodate for additional servers on th chart when it gets added. How do ...

Query the top top ten by category?
I have a table that has a list of occurences by store locations. I use a query to count those occurences and then produce a top ten report. I am currently producing that report by market. In order to do this I have built independent queries for each market, which are the source for subreports, that are all on the same report. In effect I get one report with the top ten occurences by market. I now need to move to a regional report. The issue I have is that there are 70 regions. And I produce three unique reports. I only have 6 markets so I had no real issue producing 18 different...

Access Deleted my 2007 database : ( Please help
Hi and PLEASE help! I am using Access 2007 in Vista. The database was originally created in Access 2000. Today when I closed the database I had some type of message about the database being read-only and a problem with saving the file. I'm sorry, that's all I remember about the message. If there was a cancel button I clicked it, but I don't remember what the buttons said. I just remember I thought it was strange because there was nothing read-only about my database. Then it just disappeared!!! I can't find it anywhere. I can't find anything new or any different Acce...

There is a problem with the Office database
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I've been running Office 2008 Home & Student Edition on a 3 year old MBP just fine for the last year. Today, I upgraded to a nice new unibody MBP, after which Office continued to work just fine. Then I tried to upgrade to the "full" Office, for which my employer has recently bought a volume license. No matter what I do, I get "There is a problem with the Office database" after installation. I've tried uninstalling with the "Remove Office" tool, emptying Trash and rebooting, then...

Forms used to run query
I created a form from where the information is selected to run the query. The problem is that I want to pull information from two different columns inthe query. In other words, I have a query that pulls information from 5 differente tables. I need to query the info by building/department and by full name column. I created a form from where the user can select the building to run the query, but I also want to get the employees full name, only employees located in that building so that I can run a report based on that employee. I hope I make sense! :) Any help would be greatly ...

why does my parameter query repeat the prompt?
I have a query with three parameters. The query asks for each parameter and then repeats itself before returning the data. Probably because the query is being run more than once -- perchance, is this query the RecordSource of a subreport or subform? -- Ken Snell <MS ACCESS MVP> "nickaf" <nickaf@discussions.microsoft.com> wrote in message news:F895CCAD-125C-4350-A8A0-FE469FA58DBC@microsoft.com... >I have a query with three parameters. The query asks for each parameter and > then repeats itself before returning the data. OR have you applied a filt...

Query 04-20-10
Good Morning I have table with which has difference categorys which depend on the name of the form selected. How do I get this to show up when a form is selected. Example have a form called Task Order - categories for this are Request, Issue, Quotation Acceptance. Another form is called Technical Query with categories Technical query/Resolution, Alternative Proposal, Notification of CE, Early Warning. There are a total of 5 forms and only 4 Categories. Thanks Heaps Ness -- Vanessa Nuttall Pavement Maintenance Technican You need a combo box with the rowsource type ...

Query or Retrieve Email Activites Problem
I'm trying to query the email activities that show under the history of an object, specifically an account. Ive had success retrieving activities that are not under the history but I can't seem to find the exact method to get the ones in the history. Any help would be extremely appreciated. Ive used the the activity.RetrieveByObject method and the query.ExecuteQuery method with no luck. Thanks Cory I have figured out what my problem was..... The activities were actually associated to a case that was associated with the account(but was still showing in the history of the account...

Conditional Formatting
In general I was wondering if conditional formatting has any detrimental impact on database performance. Is this something I should use to highlight particular exceptions or data that falls outside the bounds of a certain range or is there something else that will work better? Terrence, Use Conditional Formatting quite frequently and have never noticed any slow down. The only issue has ever been been if you want more than three conditions then you'll have to use VBA to *simulate* the built in functionality. -- Gina Whipp 2010 Microsoft MVP (Access) "I fee...

Manning Database
To be honest I am not sure if this is possible, I have a plan that I want to produce, but cannot seem to get it to work. Here goes.... I work for the Military (so apologies if I don't go into too much detail) and I want to produce a database of manpower for a unit 500 strong - fairly low key stuff. Each position has a unique number and a Position ID assigned to it - each person has a unique service number and personal details. There should only be one person in each of the positions at any one time. People do come and go, and change positions with promotions/postings etc. I woul...

SBS 2008 exchange database restore problem!
Hi Im having a big issue at my hands and would be very grateful for some quick help here! I have on a vmware ESXi server a SBS 2008 premium OS installed. This virtual server crashed in a veeam backup, it was some sort of snapshot problem etc, anyway after this the server rebooted and when trying to log on to the server it said somerhing like "no server available to handle your logon request". The only way to get in to the server is in safe mode. So what i now have is a backup that is a couple of days old, im trying to restore this right now (to a new virtual mach...

Query
I have a history table that has ID, Stage, and Date. I'm trying to figure out how to calculate the date diff from records that are between one stage and another. For example, ECO# STAGE DATE ECO 25 INITIAL 5/1/2006 ECO 25 IMPLEMENT 5/1/2006 ECO 25 INCOPORATE 5/3/2006 ECO 25 FINAL 5/5/2006 ECO 100 INITIAL 3/1/2006 ECO 100 IMPLEMENT 3/2/2006 ECO 100 INCOPORATE 3/15/2006 ECO 100 FINAL 3/16/2006 The end result would be ECO 25, 2 days and ECO 100, 13 days Any suggestions? Amytdev How about something like: SELECT A.ID, DateDiff("d",A.Date, B.Date) From Mytable as A, Mytable AS B ...

Linking a table to a form within one database
I want to create a "hyperlink" between a table and a form. I just want to click on one field in the table and connect it to the corresponding form page. it seems like it should be simple but I can not figure out what to do!!!! Help!!!!! Hi - If you are asking if you can open a table in datasheet view, and then click on a field to open a form, then you can't do this in Access. Access works by opening a form which has a table as its record source. All fields of the table are available to be used in the form, using form controls. If I have misinterpreted you...

Form Based Parameter Query
Hi All, If I simply enter the values into the criteria on the QBE grid it would be "1C" Or "2U" Or "3M", this works as intended. However I need to be able to reproduce this criteria via a form. I am using a list box to provide the criteria and it works with individual criteria i.e. just "1C" but I cannot reproduce the OR statement within the list box in a way that is correctly picked by the query. If it makes any difference I am typing the value into the list box. Any ideas please. Ta. Bill Duane, Thanks for the response but that is not do...