Linking Tables

[spyldbrat]

I am have a problem linking two Queries.

I have Query 1 which has the employee number. Query 2 also has an employee number. When I run Q1, it runs fine. However, when I link the Q2, the Query results are blank.

Any ideas as to why this is happening?

[ssanfu]

No idea.
Would have to see the SQL of the two queries to even begin to have an idea.........

[spyldbrat]

If you can tell me how to upload screens shots, I can do it.

[ssanfu]

Open the query in design view.
Switch to SQL view

Copy.... paste
Include the query names for each query...

Example: It will look like this...

query name: qryCGF
SQL:
SELECT CCPTags.Tag_ID_PK, CCPTags.TagName, CCPTags.PlantArea, CCPTags.InstrumentTag, CCPTags.DataType
FROM CCPTags
WHERE (((CCPTags.PlantArea)="cgf"));

[spyldbrat]

I am trying to add the data of Query 2 to Query 1

This is Query 1:
SELECT [Final Export].EmployeeNumber, [Final Export].FirstName, [Final Export].LastName, [Final Export].[Beg Date], [Final Export].[End Date], [Final Export].[Violation #1], [Final Export].[Violation #2], [Final Export].[Violation #3], [Final Export].[Violation #4], [Final Export].[Violation #5], [Final Export].[Violation #6], [Final Export].[Violation #7], [Final Export].[Violation #8], [Final Export].[Violation #9], [Final Export].[Violation #10], [Final Export].[Violation #11], [Final Export].[Violation #12], [Final Export].[Violation #13], [Final Export].[Violation #14], [Final Export].[Violation #15]
FROM [Final Export];

This is Query 2:
SELECT [Employee Master].EmployeeNumber, [Employee Master].FirstName, [Employee Master].LastName, [Employee Master].EmailAddress, [Employee Master].Address1, [Employee Master].Address2, [Employee Master].City, [Employee Master].State, [Employee Master].Zip
FROM [Employee Master];

[ssanfu]

First, from the little info I see, I would have to say that you have some design problems.
1) you have spaces in object names. Object names should be alphanumeric. No punctuation or special characters (underscore is the exception).
"Employee Master" should be "EmployeeMaster" or "Employee_Master".
"Violation #1" should be "Violation1" or "Violation_1"

2) You have repeating fields (ex.[Violation #1], [Violation #2], [Violation #3],....: ; this is a sign of a non-normalized table structure. What happens when there is a 16th violation, or 17th or 18th??? You end up changing the tables, queries, forms, reports and possible a lot of code. (The current structure is know as "committing spreadsheet")

3) you have duplicated data in the two tables: "FirstName", "LastName".



OK, so this is my attempt to join your two queries

SELECT [Employee Master].EmployeeNumber, [Employee Master].FirstName, [Employee Master].LastName, [Employee Master].EmailAddress, [Employee Master].Address1, [Employee Master].Address2, [Employee Master].City, [Employee Master].State, [Employee Master].Zip, [Final Export].[Beg Date], [Final Export].[End Date], [Final Export].[Violation #1], [Final Export].[Violation #2], [Final Export].[Violation #3], [Final Export].[Violation #4], [Final Export].[Violation #5], [Final Export].[Violation #6], [Final Export].[Violation #7], [Final Export].[Violation #8], [Final Export].[Violation #9], [Final Export].[Violation #10], [Final Export].[Violation #11], [Final Export].[Violation #12], [Final Export].[Violation #13], [Final Export].[Violation #14], [Final Export].[Violation #15]
FROM [Employee Master] INNER JOIN [Final Export] ON [Employee Master].EmployeeNumber = [Final Export].EmployeeNumber;

Create a new query, switch to SQL view and paste in the SQL above.



You can try by
1) create a new query
2) in design view, add the two tables
3) link the tables on the EmployeeNumber fields
4) drag down the fields you want to see in the result set
execute the query......

Switch to SQL view to see what the query looks like.