Trying to return specific fields from multiple tables (1 Viewer)

C

ctbfalcon

Registered User.
Local time
Today, 11:57
Joined
Nov 29, 2007
Messages
11
OK so here is a working query:

Code: 
SELECT  Assets.*
FROM    Assets
WHERE (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE   Assets.BarcodeNumber = LCAMdump.T_TAG
        ))                           =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT BuildingName
                FROM    Building_Names
                WHERE   ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
        ))                                    =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
        ))                   =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
        ))                          =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
        ))                              =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT FirstName
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT LastName
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT SSO
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT UserID
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
        ))                                =False));

It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.

I tried this but it now gives back over 220 repeating results.

Code: 
SELECT  Assets.BarcodeNumber       ,
        Employees.UserID           ,
        Building_names.BuildingName,
        Assets.Floor               ,
        Assets.BuildingLocation    ,
        Assets.DeskLocation        ,
        Employees.FirstName        ,
        Employees.LastName         ,
        Employees.SSO
FROM    Assets   ,
        Employees,
        Building_Names
WHERE (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE   Assets.BarcodeNumber = LCAMdump.T_TAG
        ))                           =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT BuildingName
                FROM    Building_Names
                WHERE   ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
        ))                                    =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
        ))                   =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
        ))                          =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
        ))                              =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT FirstName
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT LastName
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT SSO
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
        ))                                =False))
     OR (((EXISTS
        (SELECT *
        FROM    LCAMdump
        WHERE ((SELECT UserID
                FROM    Employees
                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
        ))                                =False));

I am sure it something simple but I am a novice at this so please help me. :D
 
Last edited:
C

ctbfalcon

Registered User.
Local time
Today, 11:57
Joined
Nov 29, 2007
Messages
11
ok found my problem

Code: 
SQL= "" & _
"SELECT  Assets.BarcodeNumber       , " & _
"        Employees.UserID           , " & _
"        Building_Names.BuildingName, " & _
"        Assets.Floor               , " & _
"        Assets.BuildingLocation    , " & _
"        Assets.DeskLocation        , " & _
"        Employees.FirstName        , " & _
"        Employees.LastName         , " & _
"        Employees.SSO " & _
"FROM    Building_Names " & _
"        INNER JOIN (Employees " & _
"                INNER JOIN Assets " & _
"                ON      Employees.EmployeeID  = Assets.EmployeeID) " & _
"        ON      Building_Names.BuildingNameID = Assets.BuildingNameID " & _
"WHERE (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE   Assets.BarcodeNumber = LCAMdump.T_TAG " & _
"        ))                           =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((SELECT BuildingName " & _
"                FROM    Building_Names " & _
"                WHERE   ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING) " & _
"        ))                                    =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR]) " & _
"        ))                   =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2]) " & _
"        ))                          =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1]) " & _
"        ))                              =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((SELECT FirstName " & _
"                FROM    Employees " & _
"                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST) " & _
"        ))                                =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((SELECT LastName " & _
"                FROM    Employees " & _
"                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST) " & _
"        ))                                =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((SELECT SSO " & _
"                FROM    Employees " & _
"                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO) " & _
"        ))                                =False)) " & _
"     OR (((EXISTS " & _
"        (SELECT * " & _
"        FROM    LCAMdump " & _
"        WHERE ((SELECT UserID " & _
"                FROM    Employees " & _
"                WHERE   Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN) " & _
"        ))                                =False));"
 
You must log in or register to reply here.

Similar threads

V
Trying to have a button on a form open a report with info from a list box
Replies
18
Views
734
moke123
moke123
R
Multiple Selection from A List Box, to a Table
Replies
2
Views
149
MajP
MajP
S
List all employees missing a specific record from another table
Replies
4
Views
424
CJ_London
C
G
Help! Using checkboxes and search box to search multiple fields with subform
Replies
7
Views
899
MarkK
MarkK
B
How to query when multiple fields source value from a single field in another table
Replies
1
Views
703
brharrii
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom