• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

Query Two Tables with the same Field name

I have a query with two tables, both tables have one field that has the same field name which is "TYPE". One table has "Full-time" and the other table has "Part-time". Both tables also have a field "Name Employee".

How do I define this in the query so that it looks at "Name Employee" and then based on the employee will show one column with either Full-time or Part-time.

Currently I have three columns, Name Employee, TYPE-Full-time & TYPE-Part-time.

I want only two columns, Name Employee & TYPE and in the single TYPE column it will either have Full-time or Part-time
0
CMILLER
Asked:
CMILLER
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
als315Commented:
Do you have separate table with a list of all employees?
It will be better to have only one table, but in your case you can use union query.
0
 
peter57rCommented:
(No points)
What als315 is saying is to use ...

Select [Name employee], "Full-Time" as Type from fulltimetablename
union all
Select [Name employee], "Part-Time" as Type from parttimetablename
Order by [Name employee]
0
 
CMILLERAuthor Commented:
I am not sure yet if I can combine some tables. Long story short is I am a 17 yr Sys Admin but I am new to databases. I have inherited a large Access 2007 DB and still figuring my way around it. I am looking to get it migrated into our Ent SQL but that my take some time.

peter57r: I will try that today to see if that works.

-Thanks
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
hnasrCommented:
First comment is fine giving the main idea, second is a clarification and this one is a simplification.

SELECT tble1.* FROM tbl1
UNION ALL
SELECT tble2.* FROM tbl2;
0
 
CMILLERAuthor Commented:
hnasr,

I am getting this error.

Syntax error in FROM clause. (Error 3131)
0
 
CMILLERAuthor Commented:
peter57r,

I am getting this error.

Syntax error in FROM clause
0
 
als315Commented:
You should have same table name. Now you have tble1.* and From tbl1.
If tables are tbl1 and tbl2, syntax will be:
SELECT tbl1.* FROM tbl1
UNION ALL
SELECT tbl2.* FROM tbl2;
But you should also ad¿ column with type if it is missing in your tables.
0
 
hnasrCommented:
als315!,

Thanks , typo.

CMILLER,

Did you use your table names?

SELECT yourTable1.* FROM yourTable1
UNION ALL
SELECT yourTable2.* FROM yourTable2;
0
 
CMILLERAuthor Commented:
Got it working Thanks to Both of you!
0
 
hnasrCommented:
Welcome!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now