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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rey Obrero (Capricorn1)Commented:
oops, sorry did not refresh.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ellisitoAuthor Commented:
Was the problem with the projects table that some of the fields had lookup tables/combo boxes?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.