help with simple query joining tables

[mikeder]

I have a problem. I want to create a relatively simple form to generate a relatively simple report. The form I want to create contains a dropdown menu (combo box) that lists all our clients. What I want to do is allow the user to select a client from this combo box and then click on a preview report button that will list all the projects for that client.

For example, on the combo box, if I select INSYNC as the client and then click on the preview report button it should list all the projects that our company has worked on with INSYNC.

For all the stuff I mentioned above, I was able to accomplishy. However, one thing I wanted to include in the report is the project manager next to each project, and this is where I get lost.

Here is my query code (that currently works):

SELECT Client.Client_ID, Client.Client_Name, Projects.Client_ID, Projects.Year, Projects.Code, Projects.FileNumber, Projects.ProposalPhase, Projects.JobPhase, Projects.Active, Projects.WorkDescription, Projects.Project_opened
FROM Client, Projects
WHERE (((Client.Client_Name)=[forms]![client_tester].[Combo1]) AND ((Projects.Client_ID)=[Client].[Client_ID]))
ORDER BY Projects.Code;

In the above code there are only two tables, [Client] and [Projects]. Very simple. However, to incorporate project manager, I have to include at least one more table, [HFP_Personnel]. What I want to do is a Left Join [Projects]. Both the [Projects] table and [HFP_Personnel] table contain the common field HFPID. I want to create a join relationship that does the following: Include ALL records from 'Projects' and only those records from 'HFP_Personnel' where the joined fields are equal'. The program won't allow me to do this join relationship and any other type of relationship I choose will give me the wrong result sets.

Here is the query ode that is being rejected by ACCESS:

SELECT Client.Client_ID, Client.Client_Name, Projects.Client_ID, Projects.Year, Projects.Code, Projects.FileNumber, Projects.ProposalPhase, Projects.JobPhase, Projects.Active, Projects.WorkDescription, Projects.Project_opened, HFP_Personnel.F_Name, HFP_Personnel.L_Name
FROM Client, Projects LEFT JOIN HFP_Personnel ON Projects.HFPID = HFP_Personnel.HFPID
WHERE (((Client.Client_Name)=[forms]![client_tester].[Combo1]) AND ((Projects.Client_ID)=[Client].[Client_ID]))
ORDER BY Projects.Code;

I'm in need of help.

 

[Nouba]

Give this a try.

SELECT
  P.Client_ID
, C.Client_Name
, P.Year
, P.Code
, P.FileNumber
, P.ProposalPhase
, P.JobPhase
, P.Active
, P.WorkDescription
, P.Project_opened
, H.F_Name
, H.L_Name
FROM HFP_Personnel AS H
  RIGHT JOIN (Client AS C
    INNER JOIN Projects AS P ON
      C.Client_ID = P.Client_ID) ON
    H.HFPID = P.HFPID
WHERE C.Client_Name=[Forms]![client_tester].[Combo1]
ORDER BY P.Code

 

[Pat Hartman]

First you need to fix the join between Projects and Client. You have used the old, non-specific type of join which produces a cartesian product. In the QBE grid, just draw the appropriate join line between Projects and Client. This join can be an inner join if the other join is to the right of it. Otherwise, it should not hurt to make both joins a left join since projects should always have a client.

 

[mikeder]

thanks!

Thanks for the help. Seems like in Access I can't hard code a relationship and then on the same query, use the relationship building (drawing an appropriate line between fields).

Your suggestions have helped solve my problems. Many thanks