Problems with joins... (i think)

[melinda.pluma]

I have a database that I think is crippled, the poor thing never had a
chance. I cannot have 3 tables in any of my queries. I think it's
because of how they are related. This is the SQL of the query that
won't run.

SELECT [Remote Access Users].[Install Date], [Remote Access
Users].Branch, [Remote Access Users].[E Number], [Remote Access Users].
[FC #], [Remote Access Users].[First Name], [Remote Access Users].
[Last Name], [Remote Access Users].UserID, [Remote Access
Users].Active, ISP.ISP, [Remote Access Users].Birthdate, [Remote
Access Users].[Order Entry], [Remote Access Users].[OE Date], [Remote
Access Users].[Transfer Date], [Remote Access Users].[Rcvd 517],
[Remote Access Users].Notes, [Remote Access Users].[Branch Location],
[Remote Access Users].[Branch Location], [Branch List].[Wire Symbol]
FROM (ISP RIGHT JOIN [Remote Access Users] ON ISP.[FC #] = [Remote
Access Users].[FC #]) INNER JOIN [Branch List] ON (ISP.ID = [Branch
List].ID) AND ([Remote Access Users].Branch = [Branch List].[Branch
Number])
WHERE ((([Remote Access Users].[Last Name]) Like [Please Enter Last
Name])) OR ((([Remote Access Users].[FC #])=[Please Enter FC #]));


Thanks so much for your help... Lol and please dumb this down as much
as possible!

 

[John W. Vinson]

I have a database that I think is crippled, the poor thing never had a
chance. I cannot have 3 tables in any of my queries. I think it's
because of how they are related. This is the SQL of the query that
won't run.

SELECT [Remote Access Users].[Install Date], [Remote Access
Users].Branch, [Remote Access Users].[E Number], [Remote Access Users].
[FC #], [Remote Access Users].[First Name], [Remote Access Users].
[Last Name], [Remote Access Users].UserID, [Remote Access
Users].Active, ISP.ISP, [Remote Access Users].Birthdate, [Remote
Access Users].[Order Entry], [Remote Access Users].[OE Date], [Remote
Access Users].[Transfer Date], [Remote Access Users].[Rcvd 517],
[Remote Access Users].Notes, [Remote Access Users].[Branch Location],
[Remote Access Users].[Branch Location], [Branch List].[Wire Symbol]
FROM (ISP RIGHT JOIN [Remote Access Users] ON ISP.[FC #] = [Remote
Access Users].[FC #]) INNER JOIN [Branch List] ON (ISP.ID = [Branch
List].ID) AND ([Remote Access Users].Branch = [Branch List].[Branch
Number])
WHERE ((([Remote Access Users].[Last Name]) Like [Please Enter Last
Name])) OR ((([Remote Access Users].[FC #])=[Please Enter FC #]));


Thanks so much for your help... Lol and please dumb this down as much
as possible!

Since we don't have any idea how your tables are structured or related it's
more than a bit hard to advise.

What is the Primary Key of each table?
How are the tables related?
What are the datatypes of the primary keys and their related fields?
What are you trying to do with this query?

John W. Vinson [MVP]

 

[Jeff Boyce]

Post back a list of your tables and fields, something like:

tblPerson
PersonID
FirstName
MI
LastName
DOB
... (other person-specific data)

tblCourse
CourseID
CourseTitle
CourseNumber
CourseDescription
... (other course-specific info)

trelRegistration
RegistrationID
PersonID
CourseID
RegistrationDate
... (other data specific to this person registering for this course)

And it may be that your tables need more normalization, if you don't have a
way to relate them. What is the underlying "things" about which you want to
keep data stored, and how are those things related (see above for an
example).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[melinda.pluma]

Post back a list of your tables and fields, something like:

tblPerson
PersonID
FirstName
MI
LastName
DOB
... (other person-specific data)

tblCourse
CourseID
CourseTitle
CourseNumber
CourseDescription
... (other course-specific info)

trelRegistration
RegistrationID
PersonID
CourseID
RegistrationDate
... (other data specific to this person registering for this course)

And it may be that your tables need more normalization, if you don't have a
way to relate them. What is the underlying "things" about which you want to
keep data stored, and how are those things related (see above for an
example).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that I think is crippled, the poor thing never had a
chance. I cannot have 3 tables in any of my queries. I think it's
because of how they are related. This is the SQL of the query that
won't run.

SELECT [Remote Access Users].[Install Date], [Remote Access
Users].Branch, [Remote Access Users].[E Number], [Remote Access Users].
[FC #], [Remote Access Users].[First Name], [Remote Access Users].
[Last Name], [Remote Access Users].UserID, [Remote Access
Users].Active, ISP.ISP, [Remote Access Users].Birthdate, [Remote
Access Users].[Order Entry], [Remote Access Users].[OE Date], [Remote
Access Users].[Transfer Date], [Remote Access Users].[Rcvd 517],
[Remote Access Users].Notes, [Remote Access Users].[Branch Location],
[Remote Access Users].[Branch Location], [Branch List].[Wire Symbol]
FROM (ISP RIGHT JOIN [Remote Access Users] ON ISP.[FC #] = [Remote
Access Users].[FC #]) INNER JOIN [Branch List] ON (ISP.ID = [Branch
List].ID) AND ([Remote Access Users].Branch = [Branch List].[Branch
Number])
WHERE ((([Remote Access Users].[Last Name]) Like [Please Enter Last
Name])) OR ((([Remote Access Users].[FC #])=[Please Enter FC #]));

Thanks so much for your help... Lol and please dumb this down as much
as possible!- Hide quoted text -

- Show quoted text -

tblRemote Access Users (I know this is huge... I want to break it
down, but due to my joining problems...)
ID (courtesy of access, not currently used...)
Enumber (primary key)
Branch
Branch Location (lookup box based on tblBranch List)
Install Date
Connection Date
Birthdate
First Name
Last Name
FC #
Active
User ID
Order Entry
OE date
Transfer Date
Rcvd 517
Notes

tblISP
ID (same)
Enumber (primary key)
First
Last
userID
Branch #
FC #
ISP
Amount
Notes
Jan - Dec (seperate fields each month)

tblBranch List
ID (primary key)
Branch Name
Branch Number
Wire Symbol


tblRemote Access Users is the main table, tblISP should only have
fields that Remote Access Users has. (They were both imported from
Excel and have not been... "merged" correctly) These two are joined
by the E Number, it is unique to each field that's why I used it. tbl
Branch List is joined to Remote Access Users by Branch.

This query serves the function of providing all the information we
have on each employee in the database. I'm trying to incorporate Wire
Symbol into my query, but because it comes from another table I
can't. I could add another look up box or field to remote access
users... But to me this is messy.

Mindy

 

[Jeff Boyce]

Mindy

When you find a table with repeating fields (your "January", "February",
"March"... these are all months and repeating), its time to consider whether
you need a spreadsheet or a relational database. You'd generally have to
use repeating fields for a spreadsheet, but doing so in Access will make
considerably more work for both you and Access.

Take a look at the topic of "normalization" for some ideas on how you might
restructure your tables to take better advantage of Access' features and
functions (these are relational, so feeding Access 'sheet data doesn't go
down well).

For instance, instead of one column/field per month (and what will you do
NEXT year, when you need ANOTHER "January"?!_, add a field that hold a
date/time value, and the field for the value you were putting one-of for
each month -- just one, not one per month.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Post back a list of your tables and fields, something like:

tblPerson
PersonID
FirstName
MI
LastName
DOB
... (other person-specific data)

tblCourse
CourseID
CourseTitle
CourseNumber
CourseDescription
... (other course-specific info)

trelRegistration
RegistrationID
PersonID
CourseID
RegistrationDate
... (other data specific to this person registering for this
course)

And it may be that your tables need more normalization, if you don't have
a
way to relate them. What is the underlying "things" about which you want
to
keep data stored, and how are those things related (see above for an
example).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a database that I think is crippled, the poor thing never had a
chance. I cannot have 3 tables in any of my queries. I think it's
because of how they are related. This is the SQL of the query that
won't run.

SELECT [Remote Access Users].[Install Date], [Remote Access
Users].Branch, [Remote Access Users].[E Number], [Remote Access Users].
[FC #], [Remote Access Users].[First Name], [Remote Access Users].
[Last Name], [Remote Access Users].UserID, [Remote Access
Users].Active, ISP.ISP, [Remote Access Users].Birthdate, [Remote
Access Users].[Order Entry], [Remote Access Users].[OE Date], [Remote
Access Users].[Transfer Date], [Remote Access Users].[Rcvd 517],
[Remote Access Users].Notes, [Remote Access Users].[Branch Location],
[Remote Access Users].[Branch Location], [Branch List].[Wire Symbol]
FROM (ISP RIGHT JOIN [Remote Access Users] ON ISP.[FC #] = [Remote
Access Users].[FC #]) INNER JOIN [Branch List] ON (ISP.ID = [Branch
List].ID) AND ([Remote Access Users].Branch = [Branch List].[Branch
Number])
WHERE ((([Remote Access Users].[Last Name]) Like [Please Enter Last
Name])) OR ((([Remote Access Users].[FC #])=[Please Enter FC #]));

Thanks so much for your help... Lol and please dumb this down as much
as possible!- Hide quoted text -

- Show quoted text -

tblRemote Access Users (I know this is huge... I want to break it
down, but due to my joining problems...)
ID (courtesy of access, not currently used...)
Enumber (primary key)
Branch
Branch Location (lookup box based on tblBranch List)
Install Date
Connection Date
Birthdate
First Name
Last Name
FC #
Active
User ID
Order Entry
OE date
Transfer Date
Rcvd 517
Notes

tblISP
ID (same)
Enumber (primary key)
First
Last
userID
Branch #
FC #
ISP
Amount
Notes
Jan - Dec (seperate fields each month)

tblBranch List
ID (primary key)
Branch Name
Branch Number
Wire Symbol


tblRemote Access Users is the main table, tblISP should only have
fields that Remote Access Users has. (They were both imported from
Excel and have not been... "merged" correctly) These two are joined
by the E Number, it is unique to each field that's why I used it. tbl
Branch List is joined to Remote Access Users by Branch.

This query serves the function of providing all the information we
have on each employee in the database. I'm trying to incorporate Wire
Symbol into my query, but because it comes from another table I
can't. I could add another look up box or field to remote access
users... But to me this is messy.

Mindy