Compare records, update, delete and/or replace based on results of comparison

[angloman]

I don't know exactly where to post this. Can it be done with a query? Does it need to be done through VBA? I'm not sure.



My database imports a production order from our production planning software. It's a simple import that brings in production number, product code and quantity required. The table has a primary key for each record which links to another table that stores information about quantity used and other tracking information we need.

So we might have a table with the following structure:

PK	PRODUCTION #	PRODUCT CODE	QTY
100	123456	WIDGET_A	50
101	123456	WIDGET_B	25
101	123456	WIDGET_C	75

Everything works great. But I've been told that at times we will adjust the production order. Originally I thought that I could just delete the existing data, and re-import the new production information. However, this will delete all of the transactional data that links to the PK in this table. The main change is usually in the quantity of the raw materials. But they may also change some of the actual raw materials. So here's what I need to try to do:

Compare all of the products in my db versus our production planning db. If there is a difference in quantity, update the quantity to the amount in our production planning db. If a raw material in my db doesn't exist in the production planning db, delete it from my db. If a raw material exists in the production planning db and not in mine, import it to my db. This way we preserve the transactional data for the raw materials which haven't changed and update the production order on my db with all the correct raw materials and quantities.

This seems really complicated. Any idea how to go about doing this?

[orange]

What exactly is your database for?

My understanding of your post/description:

You have a database (I'll call it Local) that imports data from a Production database.
You are trying to keep your (Local) database in sync with Production, and you know that Production can change things after you have imported data. So at any point in time your database may not be in sync with Production.
You only find out about the "out of sync" condition after the fact.


You might consider just querying the Production database when necessary, instead of having Local.

I worked in an area where we had a corporate online database dealing with finances and grants and contribution programs. The analysts needed consistent data for planning and analysis. The online system was operating in real time and values were always changing. To satisfy the analysts, we provided ReadOnly Point in Time databases -at MonthEnd, QuarterEnd, CalendarYearEnd and FiscalYearEnd for their use.

Good luck.

[angloman]

Thanks for the suggestion. The purpose of this database is to facilitate with LOT tracking using a barcode gun. Right now everything is being done manually on printed sheets and it's incredibly prone to errors. With this system, workers can import their production order, see what they need to pick, pick the raw material, scan the respective LOT number and indicate how much quantity they've used.

It's not really possible to connect live to the system. At least I don't see how it would be possible given how everything works right now. What I really want is to add a Refresh button which will perform the steps I described above, comparing the local db against the production db for that specific production order. The workers would only press that button if their manage has told them that he's made changes to the order. Most of the time, he will change the size of the production which may increase or decrease the amount of raw materials required. Other times, he may substitute one raw material for another, which is where I would need my local db to detect that a raw material is no longer needed, delete it, and import the new one.

[ItsMe]

...from our production planning software...

Is it not understood what the planning software is or how it spits out this report? It may be possible that nobody has access to it because it is proprietary to some scanning hardware or something.

Can you at least tell us how the text file is created? Where is this text file? Does it magically appear in a shared folder? Is it emailed to someone?

Any information you can provide about the process of how the data is originally stored and how the report is generated will help us to help you.

[orange]

I haven't worked with barcode scanners, but there are people on the forum who have.

So you are working with pick Lists for projected/planned production.
But management or customer changes may modify actual production.

So you or "pickers" get their initial PICK LIST, you gather raw materials, then later you get modifications.
What happens to the physical items that were picked, and forwarded, but in fact were not used?
All modifications are in the Production System. In fact, the production system has the initial pick list and the modified pick list. (or the original is updated, so only the actual production raw materials and quantity are recorded.)

If the Production number remains the same (original and all modifications), then I could see some sort of numbering at your end (such as rev 1, rev 2... final) which would represent transactions where changes in materials or quantities. And the Final should match the actual Production.
That would seem to give you all the facts regarding raw materials and quantity for each modification.

[angloman]

Honestly what I really need to know is how to achieve what I am asking. I don't think it's beneficial to go through all the details of how our company works. Let's just leave it at me understanding what's required for our needs. The fact that I'm using barcode scanners is irrelevant to this conversation.

Our production software uses an access database, which is how I was able to import the production order into my local db in the first place.

In my head, I need 3 steps to accomplish my goal:

Step 1 - Check if local raw material exists on production db. If not, delete local raw material.
Step 2 - Check if local quantity equals production db quantity. If not, update local quantity to production quantity.
Step 3 - Check if there are raw materials on the production db which aren't on the local db. If there are, import them to the local db.

I have no idea how to execute these steps. I would like help to figure out how to do this.

[ItsMe]

Step 1 - Check if local raw material exists on production db. If not, delete local raw material.
Step 2 - Check if local quantity equals production db quantity. If not, update local quantity to production quantity.
Step 3 - Check if there are raw materials on the production db which aren't on the local db. If there are, import them to the local db.

Link to the DB and create a query.

[angloman]

Link to the DB and create a query.

Care to elaborate a little bit more... I already have a link to the db. How would I structure a query, or queries to achieve the steps listed above?

[orange]

Can you zip and post a copy of your local database ?
Or show some jpgs of your table(s) design(s).

What is the affect if you simply delete your local info for Production X, and simply replace or refresh the data from the Production database?

My comments on your current processes was trying to understand your environment. If changes always come from Production, then it seems a refresh /re-import from Production would always be in sync/current.

Good luck with your project.

[angloman]

So the problem is that if I delete Production X and refresh the data from the production database, I will lose all of the tracking data because it is linked to the PK of the production table. Given that this database is the only place that tracking data will be stored, the guys will lose all the information about the quantities and lot numbers they have already pulled.

Here are some screenshots from my db.







There are two scenarios I'm trying to address:

1. The manager changes the quantity of the raw materials required. In this case, the existing tracking data is still valid. I just need to update the quantity field. The guys just need to add or remove certain raw materials in accordance with the new quantity data.

-Where records with production code, raw material code and step are the same in both local and production db, find changes in QUANTITY and update to match production db

2. A substitute raw material needs to be used. In that case, let's say that instead of regular sea salt, we're going to use organic sea salt because we ran out of the other. The manager will change the raw material code in the production order in the production db, and I need it to be updated on the local db. In this situation, the existing tracking data can be deleted, so the old record can be deleted and a new record can be created. The way I see this playing out is the guys get their production order, go to pick sea salt and see that they don't have enough. They tell their manager who then changes the recipe so that they can use organic sea salt instead. It's the manager who has to make decisions when it comes to substituting raw materials.

-Where records with production code, product code and step exist in local db but not in production db, delete the record
-Where records with production code, product code and step exist in production db and not in local, import to local