Solved How to show right name in the UNION ALL query?

[yunhsuan]

Hello~
I created two tables, containing No, Day, ProjectName and Site. The contents of ProjectName come from another table, called "Project".
Then, I combined this two tables by fucntion "UNION ALL" in query.

SELECT Table_1.[Day], Table_1.[ProjectName], Table_1.[Site], Table_1.[No]
FROM Table_1
UNION ALL
SELECT Table_2.[Day], Table_2.[ProjectName], Table_2.[Site], Table_2.[No]
FROM Table_2
ORDER BY Day;

But, the result shows No but not ProjectName in ProjectName field. How can it show ProjectName instead of No in the result of query?
Here is my test file: https://reurl.cc/px5WZx
Thanks in advance!

 

[CJ_London]

can't download - I'm told the site is unsafe. Please attach your file here

note that Day and No are reserved words and should not be used for field names. If you do, you can get unexplained errors which may be the case here

 

[pbaldy]

Each of your SELECT statements would join to the project table, along the lines of

SELECT Table_1.[Day], Projects.[ProjectName], Table_1.[Site], Table_1.[No]
FROM Table_1 INNER JOIN Projects ON Table_1.ProjectName = Projects.ProjectName

I made a guess about the field name that links them.

 

[Gasman]

Is projectname a table lookup?

 

[Pat Hartman]

This is just one of the many reasons that experts do not use table level lookups. Their implementation is spotty at best and since they are merely a crutch, you don't need to use them. You can just do things the old-fashioned way using joins as Paul suggested.

Use combos on forms or even reports, if you want but NEVER on tables.

 

[plog]

I created two tables, containing No, Day, ProjectName and Site

That's a sign of poor table structure. Are those the only fields in both tables? If not, what are the other fields?

What's the difference between Table1 and Table2?

 

[yunhsuan]

can't download - I'm told the site is unsafe. Please attach your file here

note that Day and No are reserved words and should not be used for field names. If you do, you can get unexplained errors which may be the case here

Sorry...I do not know I can directly upload file here. This is the file.

 

[yunhsuan]

Each of your SELECT statements would join to the project table, along the lines of

SELECT Table_1.[Day], Projects.[ProjectName], Table_1.[Site], Table_1.[No]
FROM Table_1 INNER JOIN Projects ON Table_1.ProjectName = Projects.ProjectName

I made a guess about the field name that links them.

I tried this, the result showed error.

SELECT Table_1.Day, Table_1.ProjectName, Table_1.Site, Table_1.[No]
FROM Project INNER JOIN Table_1 ON Project.ProjectName = Table_1.ProjectName
UNION ALL
SELECT Table_2.Day, Table_2.ProjectName, Table_2.Site, Table_2.[No]
FROM Project INNER JOIN Table_2 ON Project.ProjectName = Table_2.ProjectName;

Then, I tried this:

SELECT Table_1.Day, Table_1.ProjectName, Table_1.Site, Table_1.[No]
FROM Project INNER JOIN Table_1 ON Project.[No] = Table_1.ProjectName
UNION ALL
SELECT Table_2.Day, Table_2.ProjectName, Table_2.Site, Table_2.[No]
FROM Project INNER JOIN Table_2 ON Project.[No] = Table_2.ProjectName;

The ProjectName field still show No.
How can I edit this error?

 

[yunhsuan]

Is projectname a table lookup?

Yes!

 

[yunhsuan]

This is just one of the many reasons that experts do not use table level lookups. Their implementation is spotty at best and since they are merely a crutch, you don't need to use them. You can just do things the old-fashioned way using joins as Paul suggested.

Use combos on forms or even reports, if you want but NEVER on tables.

Excuse me~
I'm a newbie. I don't understand "old-fashioned way" in the answer very well.
Is it possible to give me more precise explanation?

 

[yunhsuan]

Each of your SELECT statements would join to the project table, along the lines of

SELECT Table_1.[Day], Projects.[ProjectName], Table_1.[Site], Table_1.[No]
FROM Table_1 INNER JOIN Projects ON Table_1.ProjectName = Projects.ProjectName

I made a guess about the field name that links them.

I think I succeed by this:

Project.ProjectName AS ProjectName

 

[CJ_London]

your uploaded file only contains table_1 and that table does not contain the fields 'projectname' or 'no'. But I see you have marked the thread as solved so I guess it doesn't matter

 

[Gasman]

I think I succeed by this:

Project.ProjectName AS ProjectName

No, not really, as it was already called ProjectName in the first place?
Your problem was likely due to your table lookup in table1, where you only really hold the key for the record in ProjectName table. The lookup just shows you the linked name and hides it true value. This is why most people do not use them, as there are other issues with them as well. Search on here for lookup fields.

 

[pbaldy]

I tried this, the result showed error.

SELECT Table_1.Day, Table_1.ProjectName, Table_1.Site, Table_1.[No]
FROM Project INNER JOIN Table_1 ON Project.ProjectName = Table_1.ProjectName
UNION ALL
SELECT Table_2.Day, Table_2.ProjectName, Table_2.Site, Table_2.[No]
FROM Project INNER JOIN Table_2 ON Project.ProjectName = Table_2.ProjectName;

Then, I tried this:

SELECT Table_1.Day, Table_1.ProjectName, Table_1.Site, Table_1.[No]
FROM Project INNER JOIN Table_1 ON Project.[No] = Table_1.ProjectName
UNION ALL
SELECT Table_2.Day, Table_2.ProjectName, Table_2.Site, Table_2.[No]
FROM Project INNER JOIN Table_2 ON Project.[No] = Table_2.ProjectName;

The ProjectName field still show No.
How can I edit this error?

In your working SQL that still shows the ID:

SELECT Table_1.Day, Table_1.ProjectName, Table_1.Site, Table_1.[No]
FROM Project INNER JOIN Table_1 ON Project.[No] = Table_1.ProjectName

Table_1.ProjectName should be Project.ProjectName, or whatever the field name is in the project table that you want displayed.