I need to enter data/records at the end of the result table.

[testaccessuser]

Hi all,

I had a query..gives the result perfectly...

However, what i could do before..is that i could enter new records at the end of the result table and they would be stored into the table.

But now i am unable to do so

And i just tested, i can d this on a new query..but not the old one

Please tell me what should i do to reverse the change
I need to enter data/records at the end of the result table.

 

[testaccessuser]

Is it because the new query has only 1 table in it and the old query has multiple tables in it?

 

[Joe4]

Is it because the new query has only 1 table in it and the old query has multiple tables in it?

I am sure that plays into it, but that, in and of itself, is NOT the reason. Plenty of multi-table queries are updateable.
However, there are plenty of conditions that would make a multi-table query NOT updateable. Allen Browne lists a myriad of reasons here: Microsoft Access tips: Why is my query read-only?

Does your query fall into any of those situations?
If you do not think it does and are still unsure why it is not updateable, post the SQL code for your query here so we can see what it is doing.

 

[testaccessuser]

I am sure that plays into it, but that, in and of itself, is NOT the reason. Plenty of multi-table queries are updateable.
However, there are plenty of conditions that would make a multi-table query NOT updateable. Allen Browne lists a myriad of reasons here: Microsoft Access tips: Why is my query read-only?

Does your query fall into any of those situations?
If you do not think it does and are still unsure why it is not updateable, post the SQL code for your query here so we can see what it is doing.

Here is the query

SELECT [Class master].[Class ID], [Class master].[Class Name], [Class master].[Date], [Staff Table].[Staff ID], [Staff table].[Name], [nominations].[Attendance], [staff table].[Phone Number]
FROM nominations, [Staff table], [Class master]
WHERE [nominations].[class name]=forms![Fill Attendance]!Combo29 And [Staff table].[staff id]=[nominations].[staff id] And [Class master].[class name]=forms![Fill Attendance]!Combo29;

• It doesn't have a GROUP BY clause.
  
• It doesn't have a TRANSFORM clause
  
• It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause = NO
  
• It contains a DISTINCT predicate = NO
  
• It involves a UNION = I am using multiple tables but not using the union function
  
• It has a subquery in the SELECT clause = no subquery
  
• It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables. = NO JOIN
  
• The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields. = I can check this....
  
• The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties. = I cannot see the Recordset property in the query's properties....
  
• The query is based on another query that is read-only (stacked query.) = NO
  
• Your permissions are read-only (Access security.) = I dont think so
  
• 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.) = NO
  
• The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.) = I will check this
  
• The fields that the query outputs are Calcluated fields (Access 2010.) = Not calculated

 

[testaccessuser]

Also,

My form properties:
recordset type: dynaset
data entry: yes
Allow addtions,, delettions, edits, filters,
record lock: no


Also,

I have 4 tables.
All have a single combination as the primary key or a a combination of 2 columsns as a primary key.

What shoudl i do now?

 

[Joe4]

If you query does contain multiple tables (which it does), it SHOULD have Joins in it (which it does not).
Look closely at that statement:

[LIST]
[*]It uses [B]JOINs[/B] of different directions on multiple tables in the FROM clause. Remove some tables. = NO JOIN
[/LIST]

It is not saying don't use joins, it is saying don't using Joins from different directions on the SAME table (i.e. don't have a Right Join and Left Join going from the same table).

Try re-writing your query with the proper Joins created between tables and see if that makes a difference.
If not, post the SQL code of that new query here.

 

[testaccessuser]

This doesnt work

SELECT Attendance.[Class Name], Attendance.[Staff ID], [staff table].[Name], Attendance.Attendance
FROM Attendance, [staff table]
WHERE Attendance.[class name]=forms![Fill Attendance]!Text7 And [staff table].[staff id]=Attendance.[staff id];

This works

SELECT Attendance.[Class Name], Attendance.[Staff ID], Attendance.Attendance
FROM Attendance
WHERE Attendance.[class name]=forms![Fill Attendance]!Text7

WHY!!!

 

[Joe4]

Your second query is based on a single table.
Your first query is based on two tables, but you have not defined a join.
As I said, please use joins in your queries!!!
It is standard procedure to do so in Access.

I believe it would look something like this:

[COLOR=#333333]SELECT Attendance.[Class Name], Attendance.[Staff ID], [staff table].[Name], Attendance.Attendance[/COLOR]
[COLOR=#333333]FROM Attendance
[/COLOR]INNER JOIN [staff table]
ON [COLOR=#333333][staff table].[staff id]=Attendance.[staff id][/COLOR]
[COLOR=#333333]WHERE Attendance.[class name]=forms![Fill Attendance]!Text7;[/COLOR]

 

[testaccessuser]

Thanks Joe4!!!

As i said before, i am new to Access.
I will definitely keep this code and use it for future reference.

Thanks once again
:D

 

[Joe4]

If you are new to Access and are planning on using it, I would highly recommend picking up a good introductory book, especially on query writing.
Access is very powerful, but not as intuitive as Excel, so it is easy to get on the wrong track and get frustrated very quickly.