Union - Priority

[T1969]

I am looking to combine two lists of data and replace data in list 1 with new data from list 2

List 1- The full list of items in manufacture with start finish dates.
&
List 2- A smaller list of identical item numbers but different dates.

What I want to achieve is a UNION of the two list's with "2" taking the priority.

list - 1
Asset NO.....Build type.....Date
1.....general.....24/05/2017
2.....general.....27/06/2017
3.....general.....4/4/2017

List - 2
Asset NO.....Build type.....Date
2.....general.....08/09/2018

Output I am looking for
Asset NO.....Build type.....Date
1.....general.....24/05/2017
2.....general.....08/09/2018
3.....general.....4/4/2017

Does it prioritise the first list in the SQL or is it random.

Alternatively is there a better way.



Thanks in advance

[orange]

It appears you want to update records in list1 with data values from List2.
Then use the revised/updated List1 in your real processing/work.

[JoeM]

If all the Asset Numbers in List - 2 are already found in List - 1, there is no need for a Union Query.
Simple do a Left Join from List - 1 to List - 2.
That SQL code would look something like:

SELECT [List - 1].[Asset NO], 
Nz([List - 2]![Build type],[List - 1]![Build type]) AS Build_Type, 
Nz([List - 2]![Date],[List - 1]![Date]) AS Date1
FROM [List - 1] 
LEFT JOIN [List - 2] 
ON [List - 1].[Asset NO] = [List - 2].[Asset NO];

[T1969]

One problem solved but another one has arisen.

I have managed to achieve my results i want from combining the two tables as below.

SELECT [Export For MRPC].[Posting Date], [Export For MRPC].[PeggedRqmt ], [Export For MRPC].[Material ], [Export For MRPC].[PgRqmtQty ], [Export For MRPC].[Reqmnts ], [Export For MRPC].[Earl/start], [Export For MRPC].[LatestFin/], [Export For MRPC].[Material description ], [Export For MRPC].[Op# ], [Export For MRPC].[Operation Text], [Export For MRPC].[Work Ctr], [Export For MRPC].Contract, [Export For MRPC].[SO Description], [Export For MRPC].[Pool Group], [Export For MRPC].Cell, [Export For MRPC].[Pegged&Op], Nz([MRPC Changes].[WIP/Planned],[Export For MRPC].[WIP/Planned]) AS [WIP/Planned], [Export For MRPC].AP, [Export For MRPC].QTR, [Export For MRPC].Year
FROM [Export For MRPC] LEFT JOIN [MRPC Changes] ON [Export For MRPC].[Pegged&Op] = [MRPC Changes].[Pegged&Op];


My Problem now is:
The file is to big to export to excel due to clip board capacity 65k lines.
I have been importing the data to Excel OK.
Once I create the above Query I cannot see that query or any others if I create them off it, when looking into the database from the import screen from Excel
All other the older quires not linked to the above data are visible & can be imported ok.

Any ideas on what I am doing wrong

[ssanfu]

No offense meant, but this dB has a very poor naming convention.

There are spaces in the object names, spaces at the end of the fields, special characters are in names ("&", "/", "#") and at least one reserved word (field name "Year").

[Export For MRPC].[PeggedRqmt ] - spaces in the names

[Export For MRPC].[Earl/start] - slash in name
[MRPC Changes].[WIP/Planned]

[Export For MRPC].[Op# ] - hash in name

[Export For MRPC].[Pegged&Op] - ampersand in name

The file is to big to export to excel due to clip board capacity 65k lines.

HOW have you been exporting to Excel? Docmd.TransferSpreadsheet??

[T1969]

ssanfu,

Thanks for the feedback. I have taken it as a positive and corrected the database. Docmd is the method I am using to pull the access data across with.

As its my first database I will take any advise if it improves my skill level.

[T1969]

OK I am still struggling to overwrite data across - back to original question. Sorry for being such a newb.



SELECT Query3.Posting, Query3.PeggedRqmt, Query3.Material, Query3.PgRqmtQty, Query3.Reqmnts, Query3.EarlStart, Query3.LatestFin, Query3.GC, Query3.[Material description], Query3.MRP, Query3.Op, Query3.Operationtext, Query3.Plnt, Query3.[Sales ord], Query3.Soite, Query3.WorkCtr
FROM Query3 LEFT JOIN QChange ON Query3.Join = QChange.Join;


I am trying to update the master records (Query3) with items from (QChange) with the common item being (Join)

As I have it set in the eaxmple it just outputs (Query3) list with no changes from (QChange)

[JoeM]

You need to use the NZ function like I showed up in post #3.
So, for each field, you would use a calculated expression like this:

Nz([QChange].[Soite],[Query3].[Soite])

What this says is return the Soite field from QChange.
However, if it cannot because there is no matching record between the Query3 and QChange objects, then return the value from Query3 instead.
Repeat for all fields (except the one you are joining on).

[T1969]

JoeM,

Where would I have to create the NZ command. In the SQL or Design view. Again apologies for my knowledge gap.

[T1969]

Have played with a smaller selection of the data and it does not change any of the records in (Query3) to the ones in (QChange) what am I doing wrong please ??

Select Nz([Query3]![Posting],[QChange]![Posting]) AS Posting, Nz([Query3]![PeggedRqmt],[QChange]![PeggedRqmt]) AS PeggedRqmt, Nz([Query3]![WorkCtr],[QChange]![WorkCtr]) AS WorkCtr
FROM [Query3] LEFT JOIN [QChange] ON [Query3].Join = [QChange].Join

[ssanfu]

I am REALLY confused!!

In Post #1, you said:

I am looking to combine two lists of data and replace data in list 1 with new data from list 2
What I want to achieve is a UNION of the two list's with "2" taking the priority.

You are talking about 2 lists and a UNION of the 2 lists.

In Post #4

FROM [Export For MRPC] LEFT JOIN [MRPC Changes] ON

Now you provide SQL with two different tables(?)........
And later:

The file is to big to export to excel due to clip board capacity 65k lines.

What file???

In Post #7, you say

OK I am still struggling to overwrite data across -

and provide SQL with a select query based on two QUERIES??

SELECT ..... FROM Query3 LEFT JOIN QChange ON

In Post #9,

Have played with a smaller selection of the data and it does not change any of the records in (Query3) to the ones in (QChange) what am I doing wrong please ??

To update the data in "Query3" (master) with the data in "QChange", you MUST use an UPDATE query, not a SELECT query.
If you want to (new) add records from "QChange" INTO "Query3" (master), you MUST use an APPEND query.


So what are you trying to do? Please clarify.........

[T1969]

ssanfu,

What I am after is.

Query3 - Master list
QChange - The change list (it will have extracts of Query3 with amendments)

I want to overwrite any changes from QChange into Query3.

I want to end up with:
A new list - No additional items.
Any changes are on the combined list driven by QChange inputs.
From your post above more aligned to the update Query.

Sorry for the confusion in the thread.
I am trying to replicate data at home to resolve. My initial thoughts were I needed to Union the data but it seems there are other better ways to achieve it. Thought I had an answer at work with the query hence my post my file is to big to export, but that has opened another issue I should not of combined the two on this thread, sorry.

[T1969]

I will try & explain & give some sample data.

Query3 data - This could be thousands of entries.



QChange - The common number is on the right Join. I have highlighted changes in yellow on this list. This will be a shorter list of the Query3 with changes made manually on the sheet.


Output I am looking for is to update the records in Query3. Not adding any more lines just updating the whole line as there could be various changes.

I would send a compressed example file of the above but cant work out how or if it can be added.