Excel query help with union select

  • Thread starter Thread starter newbie
  • Start date Start date
N

newbie

First of all i want to thank the people here who have been answering
my posts. I am learning alot as a beginner. This forum is very active
and has some talented people.

Now to the question, I am trying to get a union select to work on a
past problem i worked on. The problem is that when i copy and paste in
my code i dont seem to understand how to seamlessly paste in code
without an error. Can someone tell me what i am doing wrong? There is
something wrong with Defect$.

Here is what i have, below, but near the top there is an error but i
cant see it? while in ms excel using query i get the error,DEFECT$ is
not a valid name. Make sure that it does not include invalid
characters
or punctuation and that is is not too long


SELECT `Employees$`.ID, `Employees$`.Employee_Last_Name, `Employees
$`.Employee_First_Name, `Employees$`.`Starting Week`
FROM `C:\Users\Andrew\Downloads\11+Employee+Start
+Dates.xlsx`.`Employees$` `Employees$`
UNION ALL
SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F4, `Production$`.F4, `DefectRates$`.F4
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F5, `Production$`.F5, `DefectRates$`.F5
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F6, `Production$`.F6, `DefectRates$`.F6
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F7, `Production$`.F7, `DefectRates$`.F7
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F8, `Production$`.F8, `DefectRates$`.F8
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F9, `Production$`.F9, `DefectRates$`.F9
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F10, `Production$`.F10, `DefectRates$`.F10
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F11, `Production$`.F11, `DefectRates$`.F11
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F12, `Production$`.F12, `DefectRates$`.F12
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F13, `Production$`.F13, `DefectRates$`.F13
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F14, `Production$`.F14, `DefectRates$`.F14
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F15, `Production$`.F15, `DefectRates$`.F15
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F16, `Production$`.F16, `DefectRates$`.F16
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F17, `Production$`.F17, `DefectRates$`.F17
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F18, `Production$`.F18, `DefectRates$`.F18
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F19, `Production$`.F19, `DefectRates$`.F19
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F20, `Production$`.F20, `DefectRates$`.F20
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F21, `Production$`.F21, `DefectRates$`.F21
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F22, `Production$`.F22, `DefectRates$`.F22
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F23, `Production$`.F23, `DefectRates$`.F23
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F24, `Production$`.F24, `DefectRates$`.F24
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F25, `Production$`.F25, `DefectRates$`.F25
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID

UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F26, `Production$`.F26, `DefectRates$`.F26
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID


UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F27, `Production$`.F27, `DefectRates$`.F27
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID


UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F28, `Production$`.F28, `DefectRates$`.F28
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID


UNION ALL

SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name,
`Defects$`.F29, `Production$`.F29, `DefectRates$`.F29
FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates
$` `DefectRates$`
WHERE `Defects$`.ID = `Production$`.ID
AND `DefectRates$`.ID = `Production$`.ID
 
A UNION ALL query requires all fields in each subquery to match - they must
exist in all of the queries and be of the same type. Your very first SELECT
query pulls different field (and a different number of fields) than the rest
of your SELECT queries.
 
I will play with the variables to match defect $ and see how that
goes.
I will report back.

Andrew
 
Back
Top