Merging Linked Tables from Different Databases 1

[jsaliers]

I have four databases that are all interlinked. What I am trying to do is merge two tables from different databases by using a query. I listed the needed fields and how they should merge below:

Hours Table, HourTrack Database
Field 1 - ProgramNumber
Field 2 - ProgramName
Field 3 - Project
Field 4 - ProjectName
Field 5 - Engineer
Field 6 - WeekEnding
Field 7 - Hours

PD-JOB Table, PD-Lab Work Order Database
Field 1 - ProgramNumber
Field 2 - ProgramName
Field 3 - EngineeringProject
Field 4 - ProjectName
Field 5 - AssignedTo
Field 6 - DateCompleted
Field 7 - TotalHours

Field 1 from each table need to be in the same field in the query, field 2 from each in the same field, etc.

I do not know much about queries or SQL, but this was kinda dumped on me, and I would like to be able to complete this project, so I can move past it. Any help would be greatly appreciated.

Thanks in advance!!

Jon

 

[THoey]

Hey Jon, you will probably get a quicker answer if you define what you mean by "merge" the two tables together. I see a couple of fields that seem similar (both Field6's), but then again, maybe they aren't. Do you want one table with all those fields together? Maybe an example of the results...
Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[scriverb]

A Union query is what you need.

Select A.ProgramNumber as Field1, A.ProgramName as Field2, A.Project as Field3, A.ProjectName as Field4, A.Engineer as Field5, A.WeekEnding as Field6, A.Hours as Field7
FROM [Hours] A
Order By A.ProgramNumber
UNION
Select A.ProgramNumber as Field1, A.ProgramName as Field2, A.EngineeringProject as Field3, A.ProjectName as Field4, A.AssignedTo as Field5, A.DateCompleted as Field6, A.TotalHours as Field7
FROM [PD-Job];

This will give you a merged file of records sorted by Field1. I used those fields because they were convenient. You can change the As naming to anything you want but they must match between the two select portion of the query.

Good luck. Bob Scriver

 

[jsaliers]

Sorry about that. The first table, Hours in the HourTrack Database, is a database used to record hours spent by engineers on specific projects. The second table, PD-JOB in the PD-Lab Work Order Database, is a database used to record hours spent by our technicians and product development people on specific projects. These hours are stored in different tables for a good reason.

However, I need them in a single table, so that I can look at total hours spent by each person on a specific project. Each table has seven fields that store the same information, even though they have different information. I need these seven fields from each database to be merged into one table, containing seven fields, the fields being Program Number, Program Name, Project Number, Project Name, Worker, Date of Completion, and Total Hours Spent. As seen in my original post, each of the tables has these fields (with different field names). What I don't know how to do is to get the data from these seven important fields from each table, and put them into one single table

EX.
Hours contains the following records:
Prog# Prog.Nm. Proj.# Proj.Name Worker Date Hrs.
E100 CAD 47 Process Drawings Jon Saliers 03/06/03 11
E102 Maint. 53 Machine Repair Jason Burton 03/03/03 10

PD-JOB contains the following records:
Prog# Prog.Nm. Proj.# Proj.Name Worker Date Hrs.
E104 IT 148 Network Drops Jay Miller 03/06/03 19
E106 Audit 65 Testing Larry Ridge 03/03/03 8

After the query ran, the resulting table would look like this:

Prog# Prog.Nm. Proj.# Proj.Name Worker Date Hrs.
E100 CAD 47 Process Drawings Jon Saliers 03/06/03 11
E102 Maint. 53 Machine Repair Jason Burton 03/03/03 10
E104 IT 148 Network Drops Jay Miller 03/06/03 19
E106 Audit 65 Testing Larry Ridge 03/03/03 8

 

[jsaliers]

scriverb, I used what you posted, and renamed the As portions to match, but when I try to run it, I get an error:
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

This is what I have right now:

SELECT A.ProgramNumber as ProgramNumber, A.ProgramName as ProgramName, A.Project as ProjectNumber, A.ProjectName as ProjectName, A.Engineer as Worker, A.WeekEnding as Date, A.Hours as Hours
FROM [Hours] A
Order By A.ProgramNumber
UNION SELECT A.ProgramNumber as ProgramNumber, A.ProgramName as ProgramName, A.EngineeringProject as ProjectNumber, A.ProjectName as ProjectName, A.AssignedTo as Worker, A.DateCompleted as Date, A.TotalHours as Hours
FROM [PD-Job];

Any ideas? Thank you in advance!

Jon Saliers

 

[scriverb]

Here is the adjusted UNION query SQL:
SELECT A.ProgramNumber as ProgramNumber, A.ProgramName as ProgramName, A.Project as ProjectNumber, A.ProjectName as ProjectName, A.Engineer as Worker, A.WeekEnding as DateOfCompletion, A.TotalHoursSpent as Hours
FROM [Hours] A
Order By A.ProgramNumber
UNION SELECT A.ProgramNumber as ProgramNumber, A.ProgramName as ProgramName, A.EngineeringProject as ProjectNumber, A.ProjectName as ProjectName, A.AssignedTo as Worker, A.DateCompleted as DateOfCompletion, A.TotalHours as TotalHoursSpent
FROM [PD-Job];
Let's name this UNION query qryUnionTables. We will not make another query that takes qryUnionTables in as input and creates a table for you. It is a make table query. Just copy this SQL code into a blank SQL screen in a new query and save it as qryMTUnionTables:
SELECT * INTO tblUNIONtables
FROM qryUnionTables;
This query will make your final table called tblUNIONtables. Hopefully, this is what you need for your project. Get back if more help is needed.

Bob Scriver

 

[jsaliers]

Well, I at least got something this time. When I ran the query qryMTUnionTables, a box opened asking for a parameter for A.ProgramNumber, then did the same for each of the rest of the fields (A.ProgramName, A.EngineeringProject, A.ProjectName, A.AssignedTo, A.Weekending, and A.Hours). I just pressed enter to get through the boxes because they shouldn't have been there. Then it created a table, but a lot of the data was missing. In addition, there should have been a total of 13000+ records, but it only pasted in 5400 records. Any ideas?

I really appreciate the help you have given me thus far. Thank you very much, and thanks in advance!

Jon Saliers

 

[scriverb]

Boy, I must have been about ready for bed on that one. My mistake. Give this a try for the SQL for the UNION query.
SELECT A.ProgramNumber as ProgramNumber, A.ProgramName as ProgramName, A.Project as ProjectNumber, A.ProjectName as ProjectName, A.Engineer as Worker, A.WeekEnding as DateOfCompletion, A.Hours as TotalHoursSpent
FROM [Hours] A
Order By A.ProgramNumber
UNION SELECT B.ProgramNumber as ProgramNumber, B.ProgramName as ProgramName, B.EngineeringProject as ProjectNumber, B.ProjectName as ProjectName, B.AssignedTo as Worker, B.DateCompleted as DateOfCompletion, B.TotalHours as TotalHoursSpent
FROM [PD-Job] B;

Let me know if this one looks better. Bob Scriver

 

[jsaliers]

Actually, I started playing around with it, and got what I was looking for, using the stuff that you gave me previously. Here is the SQL string I used in the Union Query:

SELECT [ProgramNumber], [ProgramName], [Project] as [ProjectNumber], [ProjectName], [Engineer] as [Worker], [WeekEnding] as [DateOfCompletion], [Hours] as [TotalHoursSpent]
FROM [Hours]

UNION ALL SELECT [ProgramNumber], [ProgramName], [EngineeringProject] as [ProjectNumber], [ProjectName], [AssignedTo] as [Worker], [DateCompleted] as [DateOfCompletion], [TotalHours] as [TotalHoursSpent]
FROM [PD-Job]
ORDER BY [ProgramNumber], [ProjectNumber], [Worker], [DateOfCompletion];

Thank you again for all your help. It is much appreciated!!

Jon Saliers

 

[scriverb]

Great. I am glad that you finally got the recordset that you were looking for. Bob Scriver

 

[jsaliers]

Dredging up an old monster...

Everything is working fine with this query. However, I was asked to add the functionality to not include records with zero time attached. I am not good with SQL so I am looking for some help.

SELECT [ProgramNumber], [ProgramName], [Project] as [ProjectNumber], [ProjectName], [Engineer] as [Worker], [WeekEnding] as [DateOfCompletion], [Hours] as [TotalHoursSpent]
FROM [Hours]
WHERE [TotalHoursSpent] != 0
UNION ALL SELECT [ProgramNumber], [ProgramName], [EngineeringProject] as [ProjectNumber], [ProjectName], [AssignedTo] as [Worker], [DateCompleted] as [DateOfCompletion], [TotalHours] as [TotalHoursSpent]
FROM [PD-Job]
WHERE [TotalHoursSpent] != 0
ORDER BY [ProgramNumber], [ProjectNumber], [Worker], [DateOfCompletion];

I am not sure how to say not equals in SQL. Any help is appreciated. Thanks!!

Jon Saliers

 

[scriverb]

I will respond to this request a little later this afternoon. Bob Scriver

 

[scriverb]

Jon: When you say not include records with zero time attached just what do you mean. You are currently only showing with [TotalHoursSpent] = 0. Do you want to reverse this and make it [TotalHoursSpent] not equal to 0? If so then the following will do the job:

SELECT [ProgramNumber], [ProgramName], [Project] as [ProjectNumber], [ProjectName], [Engineer] as [Worker], [WeekEnding] as [DateOfCompletion], [Hours] as [TotalHoursSpent]
FROM [Hours]
WHERE [TotalHoursSpent] <> 0

UNION ALL

SELECT [ProgramNumber], [ProgramName], [EngineeringProject] as [ProjectNumber], [ProjectName], [AssignedTo] as [Worker], [DateCompleted] as [DateOfCompletion], [TotalHours] as [TotalHoursSpent]
FROM [PD-Job]
WHERE [TotalHoursSpent] <> 0
ORDER BY [ProgramNumber], [ProjectNumber], [Worker], [DateOfCompletion];

Let me know if that works for you.
Bob Scriver

 

[jsaliers]

Sorry about the confusion. I did mean not equals 0. I just wasn't sure about the syntax. I think it is java that not equals is written !=. It does work with your suggestion. Thanks again.

Jon