Single Query - from two queries "Removing duplicates"

[T1969]

What I am trying to achieve.



I want a single Query output (Combining Query3 with the changes from QChange).

• Not adding additional rows of data.
• Looking up on "Join".
• Keeping Query3 as an original file would also be a requirement.

I will have to update the file once a week.

The test file.
Sample data.zip

Query3 is a sample of the 120k lines of data for a production build.

Query3 - Join is the common linking item.



QChange - A list of items taken from Query3 and have had changes made in yellow to highlight the example on this forum.

[CJ_London]

not clear what the output is you require, but suspect you need something like this

SELECT q3.posting, q3.pegged,.....nz(qc.earlstart,q3.earlstart) EStart, nz(qc.workctr,q3.workctr) WCtr
FROM Query3 Q3 LEFT JOIN QChange QC ON Q3.Join=QC.Join


Note that join is a reserved word (in sql INNER JOUN, LEFT JOIN, RIGHT JOIN) so is likely to cause issues

You don't really need it, from the data supplied you would join on the two fields

....ON Q3.Pegged=QC.Pegged AND Q3.Op=QC.Op

[T1969]

Ajax,

Thanks for coming back to me. I will changed the name join (it is only Pegged & Op) combined.

What I am after is a single table of data.
Query3 overwritten by any changes in QChange = Total 36 lines as per Q3

I will try your suggestion & come back to you, hopefully it will work.

[CJ_London]

Note that assuming pegged and op are indexed (they should be if regularly used for joins, filters/criteria and finds) then using a calculated value will be unindexed and therefore significantly slower. However if you only have 120 rows, it probably wont make much difference

[T1969]

Ajax,

That work's & returns Exactly what I want. (I have changed "Join" to PeOp)
One issue and this again will be based on my lack of skill.

At the end of each section in Bold below (AS Expr1 & AS Expr2) when I was editing the SQL with the statements it would not allow me to add anything as your model above. So I left that blank. The system has corrected me and added those titles. I can edit Expr1 but when I change Expr2 there is no data at all for that column when run again ?


SELECT Query3.Posting, Query3.WorkCtr, Nz(QChange.EarlStart,Query3.EarlStart) AS Expr1, Nz(QChange.WorkCtr,Query3.WorkCtr) AS Expr2
FROM Query3 LEFT JOIN QChange ON Query3.PeOp = QChange.PeOp;

[CJ_London]

Expr1 etc is the name for the column. in the query grid it is presented as

Expr2: Nz(QChange.WorkCtr,Query3.WorkCtr)

All the query grid does is provide a visual view of the sql code. change it in the grid and you change the underlying sql

note that if you change exp2 to something else, that something a) must be a valid name and b) not be the same as an existing field in the tables used in the query

valid names do not have spaces, non alpha numeric characters or be a reserved word. However putting square brackets round it usually solves the problem (e.g. [my field name with spaces])

see this link for a list of reserved words

https://support.office.com/en-us/art...7-da237c63eabe

[T1969]

If I change in design view =
Expr1 - It changes to a new name OK & Keeps data.
Expr2 - If I change this it shows no data in that column when I run it. Do I have to close out the statement in some way in SQl as it's the last one.

[T1969]

Think I may have sorted the problem.

I created the original file by editing the SQL and have had that issue.

Recreating the same report in design view (builder) it works just fine.

Again Thanks for your help on this one Ajax. Much appreciated.