Query to return results from two tables

[Guest]

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table has a
selection box to choose a users name (UserName), then in a Call Details form
it displays the User's login ID and their Branch name from the Users table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by a
link to both UserName fields. I want the query to return all records from the
Calls table which match the Branch name entered as the only Criteria, but I
can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP

 

[Jeff Boyce]

How? As in "how have you joined the two tables?" (i.e., on what field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

 

[Guest]

Hi

I have joined the tables on the UserName field in the query, SQL as follows:

SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserID = Users.UserID
WHERE (((Users.Branch)="Ely") AND ((Calls.[Call Closed])=False));

This should show all open calls for the Ely branch (and there are some) but
nothing is returned. If I remove all criteria, all records are shown but the
branch name is not included in that listing either....


Martyn

How? As in "how have you joined the two tables?" (i.e., on what field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table has a
selection box to choose a users name (UserName), then in a Call Details form
it displays the User's login ID and their Branch name from the Users table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by a
link to both UserName fields. I want the query to return all records from the
Calls table which match the Branch name entered as the only Criteria, but I
can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP

 

[Jeff Boyce]

Martyn

I believe you've joined on the UserID field, not the UserName.

If there's a chance the IDs in your Calls table are not present in your
Users table, you'd need to use a "directional" join (i.e., edit the join's
property to display all of one table and any of the other that matches).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

I have joined the tables on the UserName field in the query, SQL as follows:

SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserID = Users.UserID
WHERE (((Users.Branch)="Ely") AND ((Calls.[Call Closed])=False));

This should show all open calls for the Ely branch (and there are some) but
nothing is returned. If I remove all criteria, all records are shown but the
branch name is not included in that listing either....


Martyn

How? As in "how have you joined the two tables?" (i.e., on what field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table

has
a

selection box to choose a users name (UserName), then in a Call

Details
form

it displays the User's login ID and their Branch name from the Users table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by a
link to both UserName fields. I want the query to return all records

from
the

Calls table which match the Branch name entered as the only Criteria,

but
I

can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP

 

[Guest]

Hi Jeff

I've corrected the join to the UserName field, but still no go - nothing is
returned in the datasheet view of the query & therefore something about the
design of the tables or query I must have done wrong. If I remove the
criteria, all calls are again listed but the branch name column is still
blank. I tried altering the join type to see if that made a difference but no
luck there either.

SQL for the query is now:
SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserName = Users.UserName

Any other ideas where I might have slipped up?

Thanks
Martyn
WHERE (((Users.Branch) Like "Ely") AND ((Calls.[Call Closed])=False));

Martyn

I believe you've joined on the UserID field, not the UserName.

If there's a chance the IDs in your Calls table are not present in your
Users table, you'd need to use a "directional" join (i.e., edit the join's
property to display all of one table and any of the other that matches).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

I have joined the tables on the UserName field in the query, SQL as follows:

SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserID = Users.UserID
WHERE (((Users.Branch)="Ely") AND ((Calls.[Call Closed])=False));

This should show all open calls for the Ely branch (and there are some) but
nothing is returned. If I remove all criteria, all records are shown but the
branch name is not included in that listing either....


Martyn

How? As in "how have you joined the two tables?" (i.e., on what field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table has
a
selection box to choose a users name (UserName), then in a Call Details
form
it displays the User's login ID and their Branch name from the Users table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by a
link to both UserName fields. I want the query to return all records from
the
Calls table which match the Branch name entered as the only Criteria, but
I
can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP

 

[Guest]

Could it be something to do with the properties of the fields set up in the
main Calls table? How should these be linked to the corresponding fields in
the Users table (if at all?) ...

Martyn

Hi Jeff

I've corrected the join to the UserName field, but still no go - nothing is
returned in the datasheet view of the query & therefore something about the
design of the tables or query I must have done wrong. If I remove the
criteria, all calls are again listed but the branch name column is still
blank. I tried altering the join type to see if that made a difference but no
luck there either.

SQL for the query is now:
SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserName = Users.UserName

Any other ideas where I might have slipped up?

Thanks
Martyn
WHERE (((Users.Branch) Like "Ely") AND ((Calls.[Call Closed])=False));

Martyn

I believe you've joined on the UserID field, not the UserName.

If there's a chance the IDs in your Calls table are not present in your
Users table, you'd need to use a "directional" join (i.e., edit the join's
property to display all of one table and any of the other that matches).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

I have joined the tables on the UserName field in the query, SQL as follows:

SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserID = Users.UserID
WHERE (((Users.Branch)="Ely") AND ((Calls.[Call Closed])=False));

This should show all open calls for the Ely branch (and there are some) but
nothing is returned. If I remove all criteria, all records are shown but the
branch name is not included in that listing either....


Martyn




:

How? As in "how have you joined the two tables?" (i.e., on what field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table has
a
selection box to choose a users name (UserName), then in a Call Details
form
it displays the User's login ID and their Branch name from the Users table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by a
link to both UserName fields. I want the query to return all records from
the
Calls table which match the Branch name entered as the only Criteria, but
I
can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP

 

[Jeff Boyce]

I'm afraid you'll have to tell us if there is a relationship between these
tables. In a well normalized Access database, the entities you are
interested in (?Users?, ?Calls?) store data and may be related to each
other.

Could you provide more description about the tables and fields, and how YOU
believe they are related?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Microsoft Registered Partner
https://partner.microsoft.com/

Could it be something to do with the properties of the fields set up in the
main Calls table? How should these be linked to the corresponding fields in
the Users table (if at all?) ...

Martyn

Hi Jeff

I've corrected the join to the UserName field, but still no go - nothing is
returned in the datasheet view of the query & therefore something about the
design of the tables or query I must have done wrong. If I remove the
criteria, all calls are again listed but the branch name column is still
blank. I tried altering the join type to see if that made a difference but no
luck there either.

SQL for the query is now:
SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserName = Users.UserName

Any other ideas where I might have slipped up?

Thanks
Martyn
WHERE (((Users.Branch) Like "Ely") AND ((Calls.[Call Closed])=False));

Martyn

I believe you've joined on the UserID field, not the UserName.

If there's a chance the IDs in your Calls table are not present in your
Users table, you'd need to use a "directional" join (i.e., edit the join's
property to display all of one table and any of the other that matches).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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


Hi

I have joined the tables on the UserName field in the query, SQL as
follows:

SELECT Calls.Call_Ref, Calls.[Scania Call Ref], Calls.[Call Date],
Calls.UserName, Users.Branch, Calls.[Problem Summary], Calls.[Call Closed]
FROM Calls LEFT JOIN Users ON Calls.UserID = Users.UserID
WHERE (((Users.Branch)="Ely") AND ((Calls.[Call Closed])=False));

This should show all open calls for the Ely branch (and there are some)
but
nothing is returned. If I remove all criteria, all records are shown but
the
branch name is not included in that listing either....


Martyn




:

How? As in "how have you joined the two tables?" (i.e., on what
field(s)?)

Please post the SQL statement of your query...


--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


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

Hi

New to this & can't seem to find exactly what I'm looking for in the
discussions. I have two simple tables: Calls & Users. The Calls table
has
a
selection box to choose a users name (UserName), then in a Call
Details
form
it displays the User's login ID and their Branch name from the Users
table
which I've achieved by using text boxes & the expression
=[UserName].Column(3) to display the Branch name.

My question is: I now have a query based on the two tables & joined by
a
link to both UserName fields. I want the query to return all records
from
the
Calls table which match the Branch name entered as the only Criteria,
but
I
can't get it to return anything...what am I doing wrong?

Thanks
Martyn
Office 2000, Windows XP