Creating query using multiple tables

[navin]

Hi All,

I need to create query using 4 tables. I created a query but it is
repeating the records everytime I run the query. Below is the query I
wrote:

SELECT tblRequest.RequestDate, tblRequest.ProcessName,
tblRequest.ProjectTitle, tblRequest.Category, tblRequest.SubCategory,
tblRequest.ASIS_Process, tblProjects.ProjectStatus,
tblUsers.FirstName, tblUsers.LastName, tblFocusGroup.FGName
FROM tblRequest, tblProjects, tblUsers, tblFocusgroup
WHERE (tblProjects.ProjectID=tblRequest.RequestID) AND
tblUsers.EID=tblRequest.EID AND tblProjectID.SPOCID=tblFocusGroup.ID;

Below is the the table structure:

tblFocusGroup

Field Name Type
ID AutoNum
FGName Text
--------------------------------------------------------------------------------------------------------

tblRequest:

Field Name Type
RequestID AutoNum
RequestDate Date/Time
ProcessName Text
ProjectTitle Text
Category Text
SubCategory Text
ASIS_Process Text
--------------------------------------------------------------------------------------------------------
tblProjects:
Field Name Type
ProjectID Text Linked to "RequestID" in
Table tblRequest
RequestorID Text Linked to "EID" in Table
tblUsers
ProjectStatus Text
SPOCID Text Linked to "ID" in Table
tblFocusGroup
--------------------------------------------------------------------------------------------------------

tblUsers
Field Name Type
EID Text (Unique)
FirstName Text
LastName Text

Please help in getting this query created.

Thank you in advance for the help!!
Navin

 

[KARL DEWEY]

Your problem is the tables are not joined and you get a Cartesian effect
where the number of records in one is multiplied by the number of records in
the others.

You need left joins but the way you posted your table structure I can not
figure it out. You list the table names multiple times.

To create the needed left joins open in design view and drag the tables
shown in the space above the grid so that the table that is highest in the
hiearchy is on the left (Like Users having many Projects) and those tables
related on the many side to the right of it.

Then click on the related field of the left table and drag to the right
table field that is related. Then double click on the line just created and
select option to shown all the left table records and only those of the right
table that match. Do this for all the tables (of course they must have a
relationship).