Newbie needs help with updating individual records in a table from another table

[Lateral]

Hi Guys

I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.

I have attached a screen shot (parts.jpg) showing the structure.

I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.

You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system.

I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.

Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......

I'm sure that there is a simple way to do this but as I'm a newbie, I'm struggling...

I appreciate any help you can provide.

Regards
Greg

 

[plog]

I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table

If Web_Parts has all the valid information, why not just use it? Why worry about finding differences, when in the end you want to end up with Web_Parts?

 

[Lateral]

Hi Plog

Because, the "Parts" table may have additional records added to it manually that I do not want to override and the PartId (Key) field needs to remain the same for data integrity as these records are reference by other tables.

 

[plog]

In that instance I would keep their records seperate. That means you have Web_Parts and Parts with no overlap. Then when you want a master list of all products you create a UNION query (http://www.w3schools.com/sql/sql_union.asp):

SELECT * FROM Parts
UNION ALL
SELECT * FROM Web_Parts


Then when you need to use all of the parts, you reference that query.

 

[Lateral]

Hi Plog

Thanks for that. It looks as though the Union function enables me to keep the data separate and simply concatenates the tables together which is good idea.

I still have the issue of "updating" the Web_Parts table with new or modify data.

Would I need to create an "Update" query to do the updating of the data and an Append query to add?

Is there a single query that basically allows me to :

"if the record exists that I'm trying to add then simply update/replace or if it doesn't exist simply add the record to the table?"

I had a read of the "Update" query function but it looks as if id doesn't do the second part...namely appending the new record if it doesn't exist...

Any ideas?

Regards
Greg

 

[plog]

You never mentioned updating Web_Parts. Now, I'm confused as where the valid data is, and which table contains the correct information.

I thought Parts was additional data to Web_Parts, holding parts not in there. If there is overlap between the two tables, how do you determine the correct data?

 

[Lateral]

Hi Plog,

Sorry for the confusion. I'll try to explain it better.

Some time ago I developed a desktop Access 2007 application based upon a a Microsoft Access Template (her is the link to the original template that I have modified) http://office.microsoft.com/en-au/templates/work-order-management-database-TC001018467.aspx

This application has been used for many months and the user (a friend of mine) has been adding records to the Part table and creating Workorders that have those "parts records" attached to them via the PartID key.

The Parts table contains about 50 to 60 records of the various parts that my friend sells.


I have since developed a web based shopping website for him so that he can display all of his products and services to his customers and potential customers.

This has been a great success.

It has gotten to the stage where we now have almost 600 products (parts) and services on the website.

I need to have all of the 600 products included in the Workorder application so that he can use them in the creation of Workorders.

I had envisaged the following workflow (if that's the correct terminology):

1. Export the required products/services records from the Website (MySQL) databases into a CSV file

2. Massage the header information of the CSV file to match the field names contained in the Parts table.

3. Using the Access Import CSV wizard, import the CSV file into the Parts table using the Append option. This is to retain any existing records that were used on any Workorders.

I have added to new fields to the Parts table to identify the records that come from the Website. The fields are Web_Category and Web_Product_Id.

The Web_Category contains the name (Text) of the category that the product is linked to and the Web_Product_Id contains the numeric unique ID of the product record from the MYSQL table.

I had then assumed that once the initial import was done, the Parts table would now contain all of the available products and services so that he could then create Workorders based upon them.

I had also assumed that once the initial import was done, that the Website would be the main repository of all product/service information and that all new products/services or existing products/servcies modified that it would be done to the Website and that all I needed to do was to do another export to a CSV file and write a query that would simply read the data from the CSV file (I would probably either import it to a temporary table or "link" it to a temporary table) that would look through the exported data, check to see if the record already exists in the Parts table by checking to see if the Web_Product_Id exists, and if it does, replace the data in the record. If the record does not exist in the Parts table then append it to the Parts table as this must be a new record.

So, I hope that provides some clarity.

I need to keep this as simple as possible.

Thanks again for your help.

Regards
Greg














double entry o

 

[Lateral]

Update:

I now have the Update function working. It checks to see if the "Web_Product_Id" are the same between the Parts and Web_Parts tables and if so, it updates the records in the Parts table with the fields content from the matching record in the Web_Parts table.

Now I'm pulling my hair out with the Insert function to insert ALL records from the Web_Parts table that don't exist in the Parts table...ie: the Web_Product_Id doesn't exist....

I'm almost there and just need a bit more help Plog.

Regards
Greg

Oh, on a side note, the application is split (FE and BE) and when I created the Web_Parts table in the BE, the FE doesn't see it....what do I need to do so that the FE sees the new Web_Parts table???

 

[plog]

For the insertion you will need an APPEND query that uses a LEFT JOIN between the two tables. Here's what I would do:


First make a copy of your database--always step one when mass changing data.

Take your Update query and copy it. Then work with the copy.

Change the query to a SELECT query.

One by one, right click on the links between the tables and click on 'Join Properties'.

Change the option to the one that shows All from the table from which you will be appending. Probably show all from WebParts.

In the bottom portion delete all the fields.

In the bottom, bring down just the fields from WebPArts you want in Parts. Then also bring down the primary key field from Parts.

Underneath the primary key from Parts put 'Is Null' in the criteria underneath it and uncheck the box to have it show.

Run the query. This will show you all the records that will go into Parts. Make sure it seems right.

If it seems right, change the SELECT query to an APPEND and have it append the records into Parts. Run it.

 

[Lateral]

Hi Plog

I just tried what you suggested and managed to get the first part working ie: displaying the correct new records that will be appended! So far so good.

Have a look at the attached screen shots append001, 002,003.

When I changed the query from Select to Append by clicking the Append option on the top tool bar and it prompts me to enter the name of the table that I am appending to, ie: Parts and also change the format of the grid at the bottom of the design screen. When I run the Append query, I receive an error as per the following screen shots:

append005, 006

I feel that we are almost there!

Regards
Greg

 

[plog]

Its the PartID field from the Parts table. You need to make the Append To value beneath it to blank--you are not appending this value to the table. If you need to move the PArtID from the Web_Parts table, you would need to bring that field down.

 

[Lateral]

Absolutely frigging wonderful!!!

It works!

Thanks Plog, I really appreciate this.

All I need to do now is tidy it all up and add it to a Button on a form and I'm good to go!

Oh, on a side note, the application is split (FE and BE) and when I created the Web_Parts table in the BE, the FE doesn't see it....what do I need to do so that the FE sees the new Web_Parts table???

Thanks

Regards
Greg

 

[plog]

You need to add create a linked table in the front end to Web_Parts.

 

[Lateral]

Hi Plog,

I was trying to use the Link Table Manager as I though that that was the most logical thing to use but it appears that I was wrong. I fugured out to use the "External Data" tab and link to the backend database and then select the Web_Parts table...all done mate. Thanks again.

Regards
Greg