Solved

ACCESS 2003 Updateable query

Posted on 2012-04-10
2
247 Views
Last Modified: 2012-06-27
Why can't we update the data in the query when it runs.  It is not updateable.  It is based on two tables?

SELECT tblAbsentData.Date, tblAbsentData.UserID, tblAbsentData.Name, tblAbsentData.[Unplanned PDay Hours], tblAbsentData.[Occ Illness Hours], tblAbsentData.[Total STD/FML Hours], tblAbsentData.Team
FROM tblAbsentData INNER JOIN tblAbsentDate ON tblAbsentData.Date = tblAbsentDate.AbsentDate
WHERE (((tblAbsentData.Team)=[Forms]![frmVerifyTime]![cboTeam]) AND ((tblAbsentData.Uploaded)=No))
ORDER BY tblAbsentData.Name;



THANKS
0
Comment
Question by:eyes59
2 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 37828964
There are a number of reasons why a query might not be updateable.

Best list I've found is at:  http://www.allenbrowne.com/ser-61.html

This is the item that I think gets most of us:

The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
0
 
LVL 21
ID: 37829009
<<Why can't we update the data in the query when it runs.  It is not updateable.  It is based on two tables?>>

It is common that multiple table queries are not updateable.

For a query to be upateable with multiple table you need to have the join using the primary key on one side. This is normally a One-to-many relationship.   In your case tblAbsentData.Date  or AbsentDate will need top be the primary key for the table.

Note: Date is the name of a VBA fucntion *reserved word)  and should not be avoided as field names.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 33
Some AHK commands fail in Microsoft OneNote 5 37
Convert Access formula to SQL 5 14
word 2016 1 0
This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now