I broke a Query :-(

I have this query that I tested  on sample DB now I went to re write it on the live DB and its asking me for a parameter value? I keep looking at both tables to see that they match from what I see it's OK.

Here is my update query:
UPDATE Projects INNER JOIN [Imported Excel Projects] ON Projects.ProjectNo = [Imported Excel Projects].ProjectNo SET Projects.ProjectNo = [Imported Excel Projects].[ProjectNo], Projects.Notes = [Imported Excel Projects].[Notes], Projects.RooferID = [Imported Excel Projects].[RooferID], Projects.FacilityID = [Imported Excel Projects].[FacilityID], Projects.SalespersonID = [Imported Excel Projects].[SalespersonID], Projects.[Site Visit Status] = [Imported Excel Projects].[Site Visit Status], Projects.[Sructural Analysis] = [Imported Excel Projects].[Structural Analysis];

After I run it i get:
Enter Parameter Value : Imported Excel Projects.ProjectNo

Thanks
Screenshot design view
ellisitoAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
ellisito,
there is something wrong in your table Projects

see this db
the original tables  are now named ProjectsX and Imported Excel ProjectsX
 * the query that updates the original ProjectsX  table is Query4 (still giving the error)

tables P and E were created using a make table query
  * the query that updates table P is Query2 (no Problem)

tables Projects and Imported Excel Projects were created by make table queries
 * the query that updates table P is Query1 (no Problem)

interesting is query3 (no problem)
 * table Projects is updated using the original tables [Imported Excel ProjectsX]


GS-Solar.accdb
0
 
JohnLBevanCommented:
I'm not sure, but it could be that you're updating the ProjectNo field which is the same field you're joining on.  Since these will be the same anyway, you don't need to include it in the set part of the statement, so there's no harm in removing it.

UPDATE [Projects] 
INNER JOIN [Imported Excel Projects] 
ON [Projects].[ProjectNo] = [Imported Excel Projects].[ProjectNo] 
SET [Projects].[Notes] = [Imported Excel Projects].[Notes]
, [Projects].[RooferID] = [Imported Excel Projects].[RooferID]
, [Projects].[FacilityID] = [Imported Excel Projects].[FacilityID]
, [Projects].[SalespersonID] = [Imported Excel Projects].[SalespersonID]
, [Projects].[Site Visit Status] = [Imported Excel Projects].[Site Visit Status]
, [Projects].[Sructural Analysis] = [Imported Excel Projects].[Structural Analysis];

Open in new window


Cheers,

JB
0
 
Rey Obrero (Capricorn1)Commented:
try this, copy and paste

UPDATE Projects INNER JOIN [Imported Excel Projects] ON Projects.ProjectNo = [Imported Excel Projects].ProjectNo SET Projects.Notes = [Imported Excel Projects].[Notes], Projects.RooferID = [Imported Excel Projects].[RooferID], Projects.FacilityID = [Imported Excel Projects].[FacilityID], Projects.SalespersonID = [Imported Excel Projects].[SalespersonID], Projects.[Site Visit Status] = [Imported Excel Projects].[Site Visit Status], Projects.[Sructural Analysis] = [Imported Excel Projects].[Structural Analysis];


or just remove the first column of your update query
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
oops, sorry did not refresh.
0
 
cheers4beersCommented:
You don't need to include the 'Projects.ProjectNo = [Imported Excel Projects].[ProjectNo]' in the UPDATE statement.  Since you are joining the 2 tables on those columns already, the values will be the same.
0
 
ellisitoAuthor Commented:
Even with removing the projects column ill get the error on the notes field as well. Here is the basic concept of what these series of query's do so you guys understand my method (unless someone has a better way of doing this):

Query 1 - Clear the temp table
Query 2 - Append the linked Excel file to the temp table
Query 3 - Update the primary table with the data from the temp table (this is the query that is giving problems)
Query 4 - Add new records to the primary table from the temp table.

In plain English, im trying to update a table in access with what ever changes come from an excel sheet. The only reason i use the ProjectNo field in my query to identify which record has the updates on em.
0
 
Rey Obrero (Capricorn1)Commented:
if that is the case, check the  column names of the excel file..

if column names in excel are changing, it will be best to import the excel file in a tempTable instead of using a  linked excel file. then, add the records to the final table.
0
 
ellisitoAuthor Commented:
I've done that. Via the query and manually. Here is a screenshot of both the tempTable and th finalTable.

Hope this helps

DB-Sample.jpg
0
 
Rey Obrero (Capricorn1)Commented:
better if you upload a copy of the db and the excel file..


try doing this first,
delete the query that you created.
do a compact and repair of the db

recreate the query. do this by using a few field first, test and if it is successful add the rest of the fields.
0
 
ellisitoAuthor Commented:
Test: Failed :/

Here is the DB. Ignore the linked table since all we are working with is the Projects table & the imported excel table. I'm really curious to see what the issue is.
GS-Solar-Database---Beta---Copy.accdb
0
 
ellisitoAuthor Commented:
Was the problem with the projects table that some of the fields had lookup tables/combo boxes?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.