Input "Yes" into field if record exists in Excel file

[Autoeng]

In my db I have 1,000 plus records. Of these 500 will be used in an upcoming build. The db is populated by an ODBC connection from an AS400 system. I can make a Excel report in the AS400 system that will tell me of the 1,000 parts which 500 I need for the build but I cannot create an ODBC connection to do the same. I have added a table field (RequiredForBuild). It is either "Yes" or blank. How can I run a query of the Excel report to fill the field with yes if a part number appears in the Excel report?

 

[Fornatian]

Consider using a linked table to your excel file, then join it to your existing records table via the primary key. This will have the effect of only selecting matching records. Now convert it to an update query and set the fields update to "Yes".

Was that a relevant answer having read your question again?

 

[Autoeng]

So I need a "RequiredForBuild" field in the Excel file?

 

[namliam]

Not in the excel file but in the Access table...

 

[Autoeng]

Then how do I set it up so if record exists in Excel file to put Yes in Access table field "RequiredForBuild" or maybe I'm missing something?

 

[namliam]

I asume you have some kind of information in both the excel file and the access table that is unique to the Part eg. part number

First link the excel sheet to access (using the link table option)
Now you can use the excel sheet like a table
Now go to the query builder, add both the table and the excel (linked table) link on the part number

change the query type to update....
Type Yes in the upper row
In the 3rd row (update to) select your requered for build field.

Run the query, should work...

regards

 

[Autoeng]

Oh. Seems so simple now. I've never been very good with queries but thanklfully I have you guys to hold my hand through the process.

 

[Autoeng]

change the query type to update....
Type Yes in the upper row
In the 3rd row (update to) select your requered for build field.

Hmmm. Put what where? Your instructions don't really mesh with the query window. See attachment.

Also, with the update am I only updating the RequiredForBuild field therefore that is the only one that needs to be in the builder or do I need all of my tblMaterialDetails fields?

 

[namliam]

Sorry I got it screwed up, you got it right....

 

[Autoeng]

I'm getting a "Type mismatch in expression" error!

One more question and I promise to leave you alone on this query.

In the tables used in this query tblPS32List there is a field "Required", in tblMaterialsDetails there is a field "BuildQuantityRequired". tblps32List field "Required" holds a number. Is there a way to copy that number to tblMaterialsDetails field "BuildQuantityRequired"?

 

[Fornatian]

type tblps32List.Required in the UpdateTo row of the tblMaterialsDetails.BuildQuantityRequired

This will pull the corresponding field value from the matching record.

Don't run this straight away, use the 'View' icon (top right) to check the records are pulling correctly.