Query Not Showing All Results

[renenger]

I am having an issue with one of my queries I'm hoping someone can help me with.

I have a query that I need to calculate the $ amount of sales per Salesman for the year. However, it is not pulling up the salesmen with no sales.

I have two tables. tblSalesman and tblContractInfo.

tblSalesman: SalesID, SalesmanName, Location
tblContractInfo: Total$, Year, Month

This is what the fields look like in my query

Location: "*NV*"
SalesID:
SalesmanName:
Total: Sum(nz([Total$],0))
Years: nz(DatePart("yyyy",[Date]))
Months: nz(DatePart("m",[Date]))

If I take out Total, Year, Months it shows me all the salesman

It won't show salesman with no sales for this year.

Any ideas???????


Thanks
Brandy

 

[renenger]

Relationship

I have the join properties set as below:

Include ALL records from tblSalesman and only those records from tblContractInfo where the joined fields are equal.

The SQL view is : FROM tblSalesman LEFT JOIN tblContractInfo ON tblSalesman.SalesID = tblContractInfo.SalesID

 

[renenger]

SQL

SELECT tblSalesman.OfficeLocation, tblSalesman.SalesID, tblSalesman.SalesmanName, tblSalesman.Current, Sum(nz([Total$],0)) AS Total, nz(DatePart("yyyy",[Date]),0) AS Years, nz(DatePart("m",[Date]),0) AS Months

FROM tblSalesman LEFT JOIN tblContractInfo ON tblSalesman.SalesID = tblContractInfo.SalesID

GROUP BY tblSalesman.OfficeLocation, tblSalesman.SalesID, tblSalesman.SalesmanName, tblSalesman.Current, nz(DatePart("yyyy",[Date]),0), nz(DatePart("m",[Date]),0)

HAVING (((tblSalesman.OfficeLocation) Like "*NV*") AND ((tblSalesman.Current)=Yes) AND ((nz(DatePart("yyyy",[Date]),0)) Like "*03*"));

 

[renenger]

Date

Date is one of my column names. However, I followed your advice and changed it to InputDate. It still does not work.

I can't quite figure it out. Access should be able to do this. I want to show all our salesman and then show if they have had any sales for the year.

If there are no records in tblContractInfo for the salesman, it should say 0. However, this is not happening.

 

[renenger]

Nulls

I am using the NZ() function. If you'll notice above, I posted the criteria I have in each of the query fields. It will not work for some reason. I have the correct join properties, I am using the NZ() function, I'm not sure what else to try!!!