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.

 

[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?

 

[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!!

 

[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;

 

[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));