HELP COMPARING DATA FROM ONE SOURCE TO UPDATE ANOTHER

[HerickP]

Hi Everybody,<br><br>My problem is the following: I keep an access table where I have data that holds information about orders ( the fields are: Customer Name, PO Number, Date shipped, Date Received, Status)... Field &quot;Status&quot; is always &quot;In Transit&quot;, since the order is still in the way to the customer, and &quot;Date Received&quot; is blank. These orders are shipped to a Warehouse, and from there to the customer. when the orders get to the warehouse, these POs are checked in in a Excel spreadsheet, and they will contain the Delivered date. But the problem comes here: This information will be sent to me in a Excel Spreadsheet, and as soon as i get it, what I have been done is print it out, look all the Pos, and one by one I will manually enter the date received and change the status &quot;In Transit&quot; to &quot;Delivered&quot; in my Access Table. What I would like to know is a way where I could take this spreasheet, somehow compare to my table, and have it automatically match with POs on the table, update the field &quot;Date Received&quot; with the delivery date, and change &quot;status&quot; to Delivered. <br><br><br>Sorry for the Long explanation, just wanted to make everything clear. Thanks!!!

 

[elizabeth]

You can import the Excel spreadsheet to an Access table. Then you can write an <b>update</b> query to change the records with matching POs to &quot;Delivered&quot; in your own table. (I think this is what you meant.)<br><br>1. Import the Excel file into Access (File/Get External Data/Import - change &quot;Files of Type&quot; to Excel file, etc.)<br>2. Make a copy of your existing Access table/<br>3. Design a new update query. Using your own table and field names, it should look something like this, assuming you can enter today's date for rec'd date:<br><i>UPDATE tblAccess RIGHT JOIN tblExcel ON tblAccess.PONumber = tblExcel.PONumber SET tblAccess.Status = &quot;delivered&quot;, tblAccess.RecdDate = Date();</i><br>4. Run the query to see if you get the right results. If so, just delete all the records from tblExcel and next time you import it, it can be into the existing table that you created in this round.<br><br>

 

[elizabeth]

p.s. step #2 was a backup in case your query screws up your table!

 

[HerickP]

Well, for receiving date, i want the date they have provided me in the Spreadsheet sent to me....and &quot;DELIVERD&quot; will be the new status from field &quot;STATUS&quot; in my table, which was &quot;In Transit&quot; before.

 

[HerickP]

When I run the query, I get an error:<br><br>&quot;Syntax Error (missing operator) in query expression 'tblaccess.PONumber = tblexcel.PONumber. tblaccess and tblexcel are the names u have given in the example, and I replaced this with my table names.

 

[HerickP]

just to make clear, this is what I have in SQL view:<br><br><br>UPDATE tblRCC3 RIGHT JOIN rcc3rcv ON tblRCC3.PO Number = rcc3rcv.PO Number SET tblRCC3.Status = &quot;DELIVERED&quot;, tblRCC3.Received Date = Date();<br><br>tblRCC3 is my access table;<br>rcc3rcv is the spreadsheet imported.<br><br>PO Number = Field in the table<br>Status = Field in the Table<br>Received Date =&nbsp;&nbsp;Field in the table

 

[elizabeth]

Normally you want to avoid creating object names with spaces in them. If you don't want to rename the fields with spaces in them (like PO Number, Received Date), you need to put opening and closing brackets around each one like this:<br><br><i>UPDATE tblRCC3 RIGHT JOIN rcc3rcv ON tblRCC3.[PO Number] = rcc3rcv.[PO Number] SET tblRCC3.Status = &quot;DELIVERED&quot;, tblRCC3.[Received Date] = Date();</i><br><br>However, Date() is for today's date. Here's the correct model if you want to put the tblExcel date into the tblAccess date:<br><br>UPDATE tblAccess RIGHT JOIN tblExcel ON tblAccess.PONumber = tblExcel.PONumber SET tblAccess.Status = &quot;delivered&quot;, tblAccess.RecdDate = tblExcel.RecdDate;

 

[HerickP]

U r Awsome!!!!!! Two Questions: In the SQL statement, whats the diff between the &quot;RIGHT JOIN&quot; and &quot;INNER JOIN&quot;????? And Also, how to prevent access from updating the records in the access table that already have status &quot;Delivered&quot; and the receiving date? Because I think, running the query, All the POs that match will be replaced, but I want to keep the ones in there already with the status &quot;Delivered&quot; and the receiving date intact.

 

[gol4]

Question! Why a right join? I think he would want to only bring in records that have a match (inner join)in the access table. Wouldn't a right join bring in records from the exceltbl that may not exist in the access table?

 

[elizabeth]

Thanks gol4 for pointing that out. HerickP if you have records in Excel that you would not want to add to the Access table, go to an inner join instead.&nbsp;&nbsp;To see the difference between join types, put the query into design view, right click on the connecting line between the two tables, and select Join Properties. An inner join matches tales where the joined rows are equal. A left or right (outer) join takes all the records from one table even if they are not represented in the other. You may want to go to an inner join, and to answer your other question, you need to put something in your selection criteria (query grid view) or where clause (SQL view) such as:<br><br>UPDATE tblAccess RIGHT JOIN tblExcel ON tblAccess.PONumber = tblExcel.PONumber SET tblAccess.Status = &quot;delivered&quot;, tblAccess.[Date] = [tblExcel].[date]<br>WHERE (((tblAccess.Status) Is Null));<br><br>I generally don't like to test on nulls as someone might delete a field's value manually and leave a blank space in there or something, so think about what condition you can count on with the records you don't want to update.