Union Query not working when using INNER JOIN

[sp00k]

Hello, I have a union query that is grabbing last name(from the employee table) and also grabbing card number and expiry date(from cardholder table) It seems to work, but only when I have one UNION ALL. Anymore and I get this error "The Microsoft Office Access database engine could not find object ". Make sure the object exist and that you spell its name and path name correctly"

code:

SELECT tblEmployees.[LastName], tblEmployeeHolder.[ESSO#] as [Card Number], tblEmployeeHolder.ESSOExpiry as Expiry, "ESSO" as CardType
FROM tblEmployeeHolder
INNER JOIN tblEmployees ON
tblEmployeeHolder.hldName = tblEmployees.ID
WHERE [ESSO#] is not NULL

UNION ALL
SELECT tblEmployees.[LastName], [Sunoco#], SunocoExpiry, "Sunoco" as CardType
FROM tblEmployeeHolder 
INNER JOIN tblEmployees ON
tblEmployeeHolder.hldName = tblEmployees.ID
WHERE [Sunoco#] is not NULL

UNION ALL
SELECT tblEmployees.[LastName], [Staples#], StaplesExpiry, "Staples" as CardType
FROM tblEmployeeHolder 
INNER JOIN tblEmployees ON
tblEmployeeHolder.hldName = tblEmployees.ID
WHERE [Staples#] is NOT NULL

So it works fine, when I'm only selecting the top two or bottom two(two is the limit), or if I don't use the inner join(but i need to so I know the employee name) So i don't believe anything is spelled incorrectly

 

[boblarson]

Welcome to Access World Forums!

Well, one thing is blatently obvious - you do not have a properly normalized database here. You should not have fields for each card in the same table. You should have RECORDS in a table for that. That would solve the problem that you have of needing union queries.

Second, you should not be using special Characters in your field or object names. # is an access reserved character and is a date delimiter. Rename your fields to get rid of it.

Third, ID should really be numeric, not text and it is apparent that you have it as text if you are comparing it to hldName.

 

[sp00k]

It was a typo. fixed now I believe.
For the above poster. I do know the tables aren't normalized( didn't really want a table for each card, but if I need to do that then I will), the hldName is a primary key because it uses the lastname, firstname, and id from the employee table. I use # but I remembered to put the [] around them, but in the future I'll remember not to use them.

 

[MSAccessRookie]

It was a typo. fixed now I believe.
For the above poster. I do know the tables aren't normalized( didn't really want a table for each card, but if I need to do that then I will), the hldName is a primary key because it uses the lastname, firstname, and id from the employee table. I use # but I remembered to put the [] around them, but in the future I'll remember not to use them.

I do not think you need a table for each card. You need a table for ALL cards with a Primary Key to be used as a Foreign Key to other tables.

 

[sp00k]

I do not think you need a table for each card. You need a table for ALL cards with a Primary Key to be used as a Foreign Key to other tables.

Well I do have a ID primary key for the cardHolder table. I should put cardHolderID field in the employee table? and then create a relationship between cardHolder.ID and employee.cardHolderID(not too sure on this), after that is done I can inner join the tables?
edit: The employee ID key is a bit weird, people have deleted and made new employees so the ID is not in order, will this affect the FK?

 

[sp00k]

I do not think you need a table for each card. You need a table for ALL cards with a Primary Key to be used as a Foreign Key to other tables.

Well I do have a ID primary key for the cardHolder table. I should put cardHolderID field in the employee table? and then create a relationship between cardHolder.ID and employee.cardHolderID(not too sure on this), after that is done I can inner join the tables?

edit: My foreign key is my hldName(which actually uses the employee primary ID)

 

[MSAccessRookie]

Well I do have a ID primary key for the cardHolder table. I should put cardHolderID field in the employee table? and then create a relationship between cardHolder.ID and employee.cardHolderID(not too sure on this), after that is done I can inner join the tables?

edit: My foreign key is my hldName(which actually uses the employee primary ID)

Maybe you want/need at least two more tables:

tblEmployeeCards
    EmployeeCardID (PK)
    EmployeeID (FK From tblEmployee)
    CardType (FK From tblCardTypes)
    CardNumber
    CardExpirY
    {Other Information as required}
 
tblCardType
    CardType (PK)
    Card Descr

The addition of tblCardType is a suggestion that will make it much easier to be preparded in advance of when management requests the ability to track more types of credit cards.

 

[sp00k]

I'm kind of following, an Employee can have more than one card type, so every time i make a employeeCard they will have multiple types of cards. So I dont think i can use card type as a fk... not sure though

 

[MSAccessRookie]

I'm kind of following, an Employee can have more than one card type, so every time i make a employeeCard they will have multiple types of cards. So I dont think i can use card type as a fk... not sure though

Once you understand the process, you can get acess to use any Foreign Key that is needed.

 

[sp00k]

I made a new database, and it works fine in the new one. I linked the Employee to the EmployeeCard by using Employee PK and Employee Card EmpID(using a lookup to select the first and last name in Employee) and it works fine. I have no idea why it isn't working for my other database