Query uneditable due to pulling information from multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that runs VB code to generate a new query based on values
selected. These query is for entering attendance of a program. So based on
the program selected it runs a query of the people who were invited
(graduates or undergraduates and the current semester). The query being run
looks something like this:

SQLString2 = "SELECT [First Name], [Last Name], [Person].[ID], [Semester],
[Attend] FROM Person INNER JOIN Guest on Person.ID = Guest.id WHERE
[Semester] = '" + ![Semester] + "' ORDER BY [Last Name];"

I need the two tables because the semester information is stored in the
guest table. If I leave out the join with the guest table and generate a
query of all the people in the people table, everything works as planned.

This code used to work in older versions of Access, but I'm thinking in the
new version they have controls that stop you from editing queries that have
information from multiple tables. Has anyone else come across this problem?
Any help would be appreciated.
 
"=?Utf-8?B?QnJvb2tlIEJvdXJuaXF1ZQ==?=" <Brooke
(e-mail address removed)> wrote in

SQLString2 = "SELECT [First Name], " & _
" [Last Name], " & _
" [Person].[ID], " & _
" [Semester], " & _
" [Attend] " & _
"FROM Person INNER JOIN Guest " & _
" ON Person.ID = Guest.ID " & _
"WHERE [Semester] = '" + ![Semester] + "' " & _
"ORDER BY [Last Name];"

This code used to work in older versions of Access, but I'm thinking
in the new version they have controls that stop you from editing
queries that have information from multiple tables. Has anyone else
come across this problem? Any help would be appreciated.
Click to expand...

I cannot see anything wrong with this query. What exactly is not
happening -- do you get an error message or what? If you mean that the
recordset is not updateable, then you are definitely in Microsoft Black
Magic area, and there is a lot of inconsistency between different
versions of Access! In general, the trend is toward making more queries
updateable, but I can imagine that some scenarii could go backwards.
Inner joins can be a bit funny; you might try a LEFT JOIN instead. If
that doesn't work, try a different approach like

SELECT ID, FirstName, LastName, Attend
FROM Person
WHERE ID IN
( SELECT ID FROM Guest
WHERE Semester = "2003"
)
ORDER BY LastName;

Hope that helps


Tim F
 
Thanks for your help!

The problem was that everything was coming up right, but you could not check
off the attend boxes, however rewriting the query in your suggested way
solved this problem.

Thanks Again,
Brooke

Tim Ferguson said:
"=?Utf-8?B?QnJvb2tlIEJvdXJuaXF1ZQ==?=" <Brooke
(e-mail address removed)> wrote in

SQLString2 = "SELECT [First Name], " & _
" [Last Name], " & _
" [Person].[ID], " & _
" [Semester], " & _
" [Attend] " & _
"FROM Person INNER JOIN Guest " & _
" ON Person.ID = Guest.ID " & _
"WHERE [Semester] = '" + ![Semester] + "' " & _
"ORDER BY [Last Name];"

This code used to work in older versions of Access, but I'm thinking
in the new version they have controls that stop you from editing
queries that have information from multiple tables. Has anyone else
come across this problem? Any help would be appreciated.
Click to expand...

I cannot see anything wrong with this query. What exactly is not
happening -- do you get an error message or what? If you mean that the
recordset is not updateable, then you are definitely in Microsoft Black
Magic area, and there is a lot of inconsistency between different
versions of Access! In general, the trend is toward making more queries
updateable, but I can imagine that some scenarii could go backwards.
Inner joins can be a bit funny; you might try a LEFT JOIN instead. If
that doesn't work, try a different approach like

SELECT ID, FirstName, LastName, Attend
FROM Person
WHERE ID IN
( SELECT ID FROM Guest
WHERE Semester = "2003"
)
ORDER BY LastName;

Hope that helps


Tim F
Click to expand...
 
The problem was that everything was coming up right, but you could not
check off the attend boxes, however rewriting the query in your
suggested way solved this problem.
Click to expand...

Cool. Which way?


Tim F
 
Back
Top