asked on

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

JohnLBevan

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

Rey Obrero (Capricorn1)

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

Rey Obrero (Capricorn1)

oops, sorry did not refresh.

cheers4beers

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.

ellisito

ASKER

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.

Rey Obrero (Capricorn1)

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.

ellisito

ASKER

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

Rey Obrero (Capricorn1)

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.

ellisito

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ellisito

ASKER

Was the problem with the projects table that some of the fields had lookup tables/combo boxes?