Comparison Query with mismatched columns

[gaker10]

So I have two tables that are linked to sheets in Excel. Every so often, I will have 14 columns of data in sheet1, and ten columns of data in sheet2. I want sheet1 to be updated to sheet2, but I need those four extra columns to be maintained if data in sheet1 is also in sheet2. Sheet2 is acting like an "update" here, so some data in sheet1 might not be affected. This is comparable to a "merge", where if info in sheet1 is not in sheet2, delete it from sheet1. If info in sheet1 is also in sheet2, it is unaffected. If info in sheet2 is not in sheet1, add it to sheet1. All of this is happening while my extra four columns of data are maintained, which will be uniquely identified to a line of data by all ten columns of info. Some columns are blank in certain spots.

I made a truncate query for sheet1 so I could do some sort of comparison, but now I need to perform some sort of VLOOKUP in Access to re-append my four columns to the correct record.



Is this possible? I feel like it is simple but I can't wrap my head around it.

[June7]

Cannot edit spreadsheet through link.

Editing spreadsheet from Access would involve complex VBA that opens Excel objects and manipulates them in VBA.

Why are you maintaining duplicate data?

[gaker10]

Relevant data is time sensitive, and will be edited from another user via a shared workbook until it is not present in the update sheet (sheet2). I found out that you can attach an export command to a button from access to Excel, so that solves that part of the problem.

[June7]

And the other part is how to figure out what to export?

[gaker10]

In a sense, yes. At first I thought I was trying to work with a Union Query, but that gives me unwanted data from my sheet1. Just to clarify, sheet1 and sheet2 have the first 10 columns in common. sheet1 has 4 or so (for testing purposes, i think it realistically has about 10), extra columns. People will type stuff in these extra columns, like notes. Every week or so, data will be imported into sheet2 from an outside database, and sheet1 will essentially be updated with sheet2. Here are the comparison conditions:

1) If a record is in sheet1 but NOT sheet2, delete it from sheet1 completely
2) If a record is in sheet1 AND sheet2, keep all info the same, and keep any "note" columns (post 10 columns)
3) If a record is in sheet2 and NOT sheet1, add the record and its fields to sheet1, including blank notes fields to be typed into by users

Then, sheet1 will be exported to a separate Excel sheet than the sheet it is linked to.

[June7]

As I said, using Access to edit these sheets would be complicated.

Can use queries to determine what edits need to be done. Such as Find Unmatched query - there is a wizard for that.

[gaker10]

If I can avoid Access that would actually be preferable, but I personally could not think of a less complex way to merge in Excel. Is the wizard you speak of in Access or Excel? And where?

[June7]

Query wizard in Access.

Create tab > Query Wizard

[gaker10]

Sorry for the delayed response, but I see how that Unmatched Query works now. How do I take that result and turn it into a "delete" query on my sheet1?

[gaker10]

Also, an Unmatched Query will only let me cross reference one field, when really there is no key field. I might have the same values for field1 ten times, but each possible field combo is going to be unique, so really when I do an unmatched, I need to compare 10 columns.

[June7]

What do you mean by 'values for field1 ten times'? If you have to match the same value in 10 fields, this is not a normalized data structure and will cause much frustration.

[gaker10]

So my first ten columns are: PartNumber, SONumber, SOLine, CUST-ID, Cust-PO, ClassCode, Cust-Line, Qty, PromisedDate, and ShipDate. I can have the same value in my PartNumber field several times, as well as 8 other columns, but there will always be at least one field that is different between every record. For example:

PartNumber	SONumber	SOLine	Cust-ID	Cust-PO	ClassCode	Cust-Line	QtyOrdered	DatePromised	ShipDate	extrainfo1	extrainfo2	extrainfo3	extrainfo4
xxx	10496	001	999	126579		1-1	6		10/17/2014
xxx	10500	001	999	126579		2-2	6		2/18/2015
xxx	10497	001	999	126579		1-2	6		3/2/2015
xxx	10501	001	999	126579		2-3	6		6/10/2015
xxx	10498	001	999	126579		1-3	6		7/3/2015
xxx	10502	001	999	126579		2-4	1		10/5/2015

In this example, PartNumber is the same for all lines, but SONumber is different, SOLine is the same etc. If ever there is a record where EVERY field is identical, it is a duplication error. The most extreme case is every field is identical except for one. So, lines 1 and 2 could have every field the same except for ShipDate, or except for CustLine etc. This is taken from my sheet1 btw, the extrainfo columns are test columns (there might be more). My sheet2 looks exactly like this sheet except it does not have the extra info columns. So, I need to merge these two sheets based on the conditions given in post #5. While this merge is occurring, I need to maintain any data in the extrainfo columns for any records meeting criteria #2 of post #5. For now, I do not think anything will change in the criteria #2 records as far as the first ten columns.

[June7]

Could set compound index on the multiple fields to prevent duplicate records.

Bing: Access find unmatched multiple columns
Review http://www.access-programmers.co.uk/...ad.php?t=80439

[gaker10]

SELECT MRP.*
FROM MRP LEFT JOIN Master ON (Master.ShipDate=MRP.ShipDate) AND (Master.DatePromised=MRP.DatePromised) AND (Master.QtyOrdered=MRP.QtyOrdered) AND (Master.CustLine=MRP.CustLine) AND (Master.ClassCode=MRP.ClassCode) AND (Master.CustPO=MRP.CustPO) AND (Master.CustID=MRP.CustID) AND (Master.SOLine=MRP.SOLine) AND (Master.SONumber=MRP.SONumber) AND (Master.PartNumber=MRP.PartNumber)
WHERE Master.PartNumber Is Null;

Just to verify my logic here, I want this query to look in the Master table for records that do not match any records in the MRP table based on the ten column matching, right? MRP table is the table that updates the Master table via post #5 criteria. It's a little hard to tell by the result, I did do some record tests to see what happens and it looks to work so far.

[gaker10]

Also, my extra columns do not carry over even when I include them in the query.