Query Values

[renenger]

Ok. I am getting really frustrated with this. Let me try to explain the DB first.

This is a contracting database. The user inputs all our contracts. Salesman Info, contract price, units, etc...

tblContractInfo - holds all pertinent contract info. Date, price, salesid, units, builderid, subdivision,etc...

tblOptionJobs - holds all the contract options. Date, optionid, contractid (if there is one), price, salesid.

tblSalesman - Company salesman. Salesid, Name, State.

What I am trying to do is create a report that shows all the months in the year. Calculates the total sales for each salesman for each month. Shows the total amount by state each month.

For the most part it works. However, if a salesman does not have any sales for the year, it will not show their name in the report. I have tried the nz function to no avail. So I added dummy records into the database with $0 and that worked. The problem I am running into now is on one of my queries, if the user has no contract sales for the month and only option sales it will not show this amount in the detail list.

I created three queries:
qryYearlyTotalsCountertop - this pulls all the CT sales for the year by month and salesman.
qryYearlyTotalsOptions - this pulls all the Options sales for the year by month and salesman
qryYearlyTotals - pulls all contract sales for year by month and salesman.

For month on all queries I used: Months: DatePart("m",[ContractDate])

Those 3 queries are used in the qryTotalYearlyAmount

I am pulling SalesID, SalesmanName, and Months from qryYearlyTotals. With the join "Include all records from qryYearlyTotals and only those from the others that are equal"

The total field is: Total: nz([SumofTotal$])+nz([qryYearlyTotalsOptions].[SumofAmount])+nz([qryYearlyTotalsCounterTops].[SumofAmount])

I think the join is causing my problem but I don't know how to work around it. If the salesman only had option sales one month they will not have a record for that month in qryYearlyTotals.

Any ideas?? I hope this is not too confusing. I am attaching a copy of what the report looks like.

I really appreciate it.

 

[bjackson]

have you tried using iif
Total: iif([SumofTotal$] >0,[SumofTotal$] ,0)+iif([qryYearlyTotalsOptions].[SumofAmount]>0,[qryYearlyTotalsOptions].[SumofAmount],0)iif([qryYearlyTotalsCounterTops].[SumofAmount]>0,[qryYearlyTotalsCounterTops].[SumofAmount],0)

 

[renenger]

QUERY FORMULA

THAT DIDN'T WORK EITHER. AS SOON AS I ADDED A RECORD FOR THAT MONTH WITH $0 CONTRACT PRICE, THEIR VALUES SHOWED UP. SO THE JOIN IS CAUSING A PROBLEM AND I DON'T KNOW HOW ELSE TO JOIN THEM.

THANK YOU VERY MUCH THOUGH.

 

[Pat Hartman]

Since you cannot guarentee the presence of a salesperson in either one of the tables, making one or the other the "left" table in an outer join won't help you. You need to add the salesperson table to the query. Make it the "left" table and join each query to it rather than to each other.

 

[renenger]

QUERY

I HAVE TRIED THAT TOO AND IT RETURNS HUNDREDS OF DUPLICATE RECORDS. ANYWAY TO AVOID THAT?

 

[renenger]

QUERY

OK. THE [MONTHS] FIELD IS NOT INCLUDED IN TBLSALEMAN.

MONTHS IN THE QUERIES IS MONTHS: DATEPART("M",[CONTRACTDATE])

HOW CAN I GET THIS TO SHOW IN THE NEW QUERY AFTER USING THE TBLSALESMAN TO JOIN THEM WITH?

 

[Pat Hartman]

Are you shouting intentionally?

There is clearly something wrong with your join. Visually, it should connect salesperson to the countertop query and salesperson to the options query and salesperson to the totals query. ALL joins should be left joins. If that still doesn't fix the problem, post the SQL.

Regarding your latest post, you get months to show by selecting that column from the appropriate table/query.

 

[renenger]

Sorry Pat

Sorry Pat. This is just so frustrating!!

SELECT qryYearlyTotals.SalesID, qryYearlyTotals.SalesmanName, qryYearlyTotals.Months, nz([SumofTotal$])+nz([qryYearlyTotalsOptions].[SumofAmount])+nz([qryYearlyTotalsCounterTops].[SumofAmount]) AS Total

FROM ((qryYearlyTotals RIGHT JOIN tblSalesman ON qryYearlyTotals.SalesID = tblSalesman.SalesID) LEFT JOIN qryYearlyTotalsOptions ON tblSalesman.SalesID = qryYearlyTotalsOptions.SalesID) LEFT JOIN qryYearlyTotalsCounterTops ON tblSalesman.SalesID = qryYearlyTotalsCounterTops.SalesID;

[Months] is in each of the queries. It is not in the tblSalesman.

Months in the queries is derived by Months: Datepart ("m",[ContractDate])

I appreciate all your help. Thank you!!

 

[Pat Hartman]

1. Make ALL the joins Left Joins with tblSalesperson being the "left" table. To force this, remove the existing joins, move the salesperson table to the left and rebuild the joins.
2. Don't leave Nz() to default. Always specify the value you want nulls to be replaced with. When you allow Nz() to decide what to return, it bases its decision on the data type of the null field. By letting the function default, you essentially force people to look up the data type of the field to determine what the result of the function will be.

 

[renenger]

Query

Like this?


SELECT qryYearlyTotals.SalesID, qryYearlyTotals.SalesmanName, qryYearlyTotals.Months, Sum(IIf([SumofTotal$]>0,[SumofTotal$],0)+IIf([qryYearlyTotalsOptions].[SumofAmount]>0,[qryYearlyTotalsOptions].[SumofAmount],0)+IIf([qryYearlyTotalsCounterTops].[SumofAmount]>0,[qryYearlyTotalsCounterTops].[SumofAmount],0)) AS Total

FROM ((tblSalesman LEFT JOIN qryYearlyTotals ON tblSalesman.SalesID = qryYearlyTotals.SalesID) LEFT JOIN qryYearlyTotalsOptions ON tblSalesman.SalesID = qryYearlyTotalsOptions.SalesID) LEFT JOIN qryYearlyTotalsCounterTops ON tblSalesman.SalesID = qryYearlyTotalsCounterTops.SalesID

GROUP BY qryYearlyTotals.SalesID, qryYearlyTotals.SalesmanName, qryYearlyTotals.Months;

 

[Pat Hartman]

Does the join work now?

Using the Nz() is better than IIf(), just include the alternate value.

nz([SumofTotal$],0)
or
nz([someTextField],"a")

 

[renenger]

OK. STILL NOT SHOWING ME THE RESULTS IF THERE IS NO RECORD IN qryYearlyTotals. I think it's the months field. Not sure how to add that.



SELECT tblSalesman.SalesID, tblSalesman.SalesmanName, qryYearlyTotals.Current, qryYearlyTotals.Months, qryYearlyTotals.Years, Sum(NZ([SumofTotal$],0)+NZ([qryYearlyTotalsOptions].[SumofAmount],0)+NZ([qryYearlyTotalsCounterTops].[SumofAmount],0)) AS Total

FROM ((tblSalesman LEFT JOIN qryYearlyTotals ON tblSalesman.SalesID = qryYearlyTotals.SalesID) LEFT JOIN qryYearlyTotalsOptions ON tblSalesman.SalesID = qryYearlyTotalsOptions.SalesID) LEFT JOIN qryYearlyTotalsCounterTops ON tblSalesman.SalesID = qryYearlyTotalsCounterTops.SalesID

GROUP BY tblSalesman.SalesID, tblSalesman.SalesmanName, qryYearlyTotals.Current, qryYearlyTotals.Months, qryYearlyTotals.Years

HAVING (((qryYearlyTotals.Current)=Yes));

 

[Pat Hartman]

I don't think you want the having clause. It will prevent any rows from being returned if there was no matching row in qryYearlyTotals.

You may have better luck with this query if you do the summing in the supporting queries. That way this final query can just add the already summed columns together. You can also get rid of the group by clause.

SELECT tblSalesman.SalesID, tblSalesman.SalesmanName, qryYearlyTotals.Current, qryYearlyTotals.Months, qryYearlyTotals.Years, NZ([SumOfSumofTotal$],0)+NZ([qryYearlyTotalsOptions
].[SumOfSumofAmount],0)+NZ([qryYearlyTotalsCounterTops].[SumOfSumofAmount],0) AS Total

FROM ((tblSalesman LEFT JOIN qryYearlyTotals ON tblSalesman.SalesID = qryYearlyTotals.SalesID) LEFT JOIN qryYearlyTotalsOptions ON tblSalesman.SalesID = qryYearlyTotalsOptions.SalesID) LEFT JOIN qryYearlyTotalsCounterTops ON tblSalesman.SalesID = qryYearlyTotalsCounterTops.SalesID;