Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

ACCESS 2003 Updateable query

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
eyes59
Asked:
eyes59
1 Solution
 
Dale FyeCommented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<<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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now