SQL Error when trying to product multi-table query...

[Goobz]

Okay.. Here's my problem...

Tables: Redmond Employee Master, Australia Employee Master, European
Employee Master
Common Fields: TM/TMNum, TMName/DisplayName, Wireless/Cellular01
Data: All data is 100% different between TM/TMNum on all tables. So
the TMNum in Red is different than TMNum in Aust, etc., so I don't
think I can do option 2 or option 3 on the relationship status...

Here's what I am trying to do...

One specific query that will show me the following info within all 3
tables.
TMNum >0, TMName=Not Null, Phone (See Order By Code), PhoneBook=-1

I still am not 100% good with relationships, although I am learning
quite a bit between this website and the book(s) I am reading!

Here's the SQL that is giving me errors...

SELECT [Employee Master].TM, Australia.[TM Number], DN.DisplayName,
[CountryCode] & " " & [NPA] & " " & [NXX] & " " & [DN.DN] AS PhoneNum
FROM ((DN LEFT JOIN [Employee Master] ON DN.TMNum = [Employee
Master].TM) INNER JOIN Australia ON [Employee Master].TM = Australia.
[TM Number]) INNER JOIN [Employee Master] AS [European Phone List] ON
[Employee Master].TM = [European Phone List].TM
WHERE ((([Employee Master].TM)>0) AND ((Australia.[TM Number])>0) AND
((DN.DisplayName) Is Not Null) AND ((DN.PhoneBook)=-1))
ORDER BY [CountryCode] & " " & [NPA] & " " & [NXX] & " " & [DN.DN];

 

[Jeff Boyce]

You have three tables, with XMaster, YMaster & ZMaster.

What will you do when you need to add "A", "B", and "C" ... add three more
tables? That's going to be a lot of work, because you'll also have to
modify your forms, queries, reports, ...

What about one table (Master) and a single additional field (Location)?

Your table design looks like a spreadsheet. Access is not a spreadsheet on
steroids.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Goobz]

You have three tables, with XMaster, YMaster & ZMaster.

What will you do when you need to add "A", "B", and "C" ... add three more
tables?  That's going to be a lot of work, because you'll also have to
modify your forms, queries, reports, ...

What about one table (Master) and a single additional field (Location)?

Your table design looks like a spreadsheet.  Access is not a spreadsheeton
steroids.

Good luck!

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Okay.. Here's my problem...

Tables: Redmond Employee Master, Australia Employee Master, European
Employee Master
Common Fields: TM/TMNum, TMName/DisplayName, Wireless/Cellular01
Data: All data is 100% different between TM/TMNum on all tables. So
the TMNum in Red is different than TMNum in Aust, etc., so I don't
think I can do option 2 or option 3 on the relationship status...

Here's what I am trying to do...

One specific query that will show me the following info within all 3
tables.
TMNum >0, TMName=Not Null, Phone (See Order By Code), PhoneBook=-1

I still am not 100% good with relationships, although I am learning
quite a bit between this website and the book(s) I am reading!

Here's the SQL that is giving me errors...

SELECT [Employee Master].TM, Australia.[TM Number], DN.DisplayName,
[CountryCode] & " " & [NPA] & " " & [NXX] & " " & [DN.DN] AS PhoneNum
FROM ((DN LEFT JOIN [Employee Master] ON DN.TMNum = [Employee
Master].TM) INNER JOIN Australia ON [Employee Master].TM = Australia.
[TM Number]) INNER JOIN [Employee Master] AS [European Phone List] ON
[Employee Master].TM = [European Phone List].TM
WHERE ((([Employee Master].TM)>0) AND ((Australia.[TM Number])>0) AND
((DN.DisplayName) Is Not Null) AND ((DN.PhoneBook)=-1))
ORDER BY [CountryCode] & " " & [NPA] & " " & [NXX] & " " & [DN.DN];- Hide quoted text -

- Show quoted text -

That's almost basically what the last idiots have done... I have
already started on reworking the database over the holiday. I've
already gotten rid of the replica, and already split the database,
etc...

 

[Pat Hartman]

To get past your immediate problem, use a union query. But then combine the
tables as Jeff suggested. Just add a column to the table so you can
identify the region. You can they use queries to separate the data as
needed.