Query joining tables

E

EdLeeYoung

I need to bring information from two tables together...creating a single
record for each "Service Number". But what I'm trying is not working for me.
This adds "Jacks" information to "Directory" records where the number is
found in both tables (and yes I know I have redundant fields). If a "Service
Number" from "Jacks" is not found in "Direcory" that information is lost.

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
ON Directory.[Service Number] = UsedJacks.[Service Number];


Help!
 
J

Jeff Boyce

Your description of the "Jacks" table doesn't include a [Service Number]
field, but your SQL statement refers to a "UsedJacks" table, and does
include reference to a [Service Number] field in that table.

I'm confused!

Regards

Jeff Boyce
<Office/Access MVP>
 
E

EdLeeYoung

sorry. Maybe you could help with my query now?

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc, Service Number

Jeff Boyce said:
Your description of the "Jacks" table doesn't include a [Service Number]
field, but your SQL statement refers to a "UsedJacks" table, and does
include reference to a [Service Number] field in that table.

I'm confused!

Regards

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
I need to bring information from two tables together...creating a single
record for each "Service Number". But what I'm trying is not working for
me.
This adds "Jacks" information to "Directory" records where the number is
found in both tables (and yes I know I have redundant fields). If a
"Service
Number" from "Jacks" is not found in "Direcory" that information is lost.

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
ON Directory.[Service Number] = UsedJacks.[Service Number];


Help!
Click to expand...
Click to expand...
 
J

Jeff Boyce

I'm still not seeing where "UsedJacks" in your SQL is coming from.

What happens if you start over, create a new query, select the two tables,
join them on the [Service Number], change the properties of the join to
"ALL" of Directory and "ANY" related Jacks?

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
sorry. Maybe you could help with my query now?

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc, Service Number

Jeff Boyce said:
Your description of the "Jacks" table doesn't include a [Service Number]
field, but your SQL statement refers to a "UsedJacks" table, and does
include reference to a [Service Number] field in that table.

I'm confused!

Regards

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
I need to bring information from two tables together...creating a single
record for each "Service Number". But what I'm trying is not working
for
me.
This adds "Jacks" information to "Directory" records where the number
is
found in both tables (and yes I know I have redundant fields). If a
"Service
Number" from "Jacks" is not found in "Direcory" that information is
lost.

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
ON Directory.[Service Number] = UsedJacks.[Service Number];


Help!
Click to expand...
Click to expand...
Click to expand...
 
E

EdLeeYoung

ok another mistake in my description I said "Jacks" when the table is called
"UsedJacks". <b>Please forget my description<b> and look at the query. It
only pulls all records from the Directory table but only part of the records
from the OTHER table.

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
I'm still not seeing where "UsedJacks" in your SQL is coming from.

What happens if you start over, create a new query, select the two tables,
join them on the [Service Number], change the properties of the join to
"ALL" of Directory and "ANY" related Jacks?

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
sorry. Maybe you could help with my query now?

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc, Service Number

Jeff Boyce said:
Your description of the "Jacks" table doesn't include a [Service Number]
field, but your SQL statement refers to a "UsedJacks" table, and does
include reference to a [Service Number] field in that table.

I'm confused!

Regards

Jeff Boyce
<Office/Access MVP>

I need to bring information from two tables together...creating a single
record for each "Service Number". But what I'm trying is not working
for
me.
This adds "Jacks" information to "Directory" records where the number
is
found in both tables (and yes I know I have redundant fields). If a
"Service
Number" from "Jacks" is not found in "Direcory" that information is
lost.

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
ON Directory.[Service Number] = UsedJacks.[Service Number];


Help!
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I may still not be understanding, but here goes...

Open the query in design mode. Select the line linking Directory and
UsedJacks. Change the line's (the join's) property to include all records
from Directory and "Any" matching records (on Service Number) from
UsedJacks. Or, the other way around, if that's what you're after.

By the way, your SQL returns [Service Number] twice, once from each table.
You probably don't need the second one.

Regards

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
ok another mistake in my description I said "Jacks" when the table is
called
"UsedJacks". <b>Please forget my description<b> and look at the query. It
only pulls all records from the Directory table but only part of the
records
from the OTHER table.

SELECT Directory.FullName, Directory.[Service Number], Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
I'm still not seeing where "UsedJacks" in your SQL is coming from.

What happens if you start over, create a new query, select the two
tables,
join them on the [Service Number], change the properties of the join to
"ALL" of Directory and "ANY" related Jacks?

Jeff Boyce
<Office/Access MVP>

EdLeeYoung said:
sorry. Maybe you could help with my query now?

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc, Service Number

:

Your description of the "Jacks" table doesn't include a [Service
Number]
field, but your SQL statement refers to a "UsedJacks" table, and does
include reference to a [Service Number] field in that table.

I'm confused!

Regards

Jeff Boyce
<Office/Access MVP>

I need to bring information from two tables together...creating a
single
record for each "Service Number". But what I'm trying is not working
for
me.
This adds "Jacks" information to "Directory" records where the
number
is
found in both tables (and yes I know I have redundant fields). If a
"Service
Number" from "Jacks" is not found in "Direcory" that information is
lost.

I have 2 tables:
Directory - FullName, Service Number, User, Office, Group
Jacks - Jack, Bldg Num, Room, Floor, Bldg Desc

SELECT Directory.FullName, Directory.[Service Number],
Directory.User,
Directory.Office, Directory.Group,
UsedJacks.[Bldg Num], UsedJacks.[Bldg Desc], UsedJacks.Floor,
UsedJacks.Room, UsedJacks.Jack, UsedJacks.[Service Number]
FROM Directory LEFT JOIN UsedJacks
ON Directory.[Service Number] = UsedJacks.[Service Number];


Help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find Record with multi combo boxes 0
getting all entries from main table and matching from another 1
Flag a value in a group... 4
Stacked vs Multiple Inner Joins 10
Left Join Query 2
Multiple LEFT Joins w/ COUNT... getting really wierd results 1
Left Join to Huge Table with Duplicates 2
Slow queries when joining local to SQL Server tables 18

Top