Here is the SQL statement in my commission query - any help would be great.
SELECT [PAF Form].Commissions, [Personnel info].Branch, [Personnel
info].DeptNo, [Personnel info].FileNo, [Personnel info].LastName, [Personnel
info].FirstName, [Sales info].Month, [PAF Form].PersonalSales, [Sales
info].Salesdollarsactual, [Sales numbers].SalesId, [Sales
numbers].SplitSales, [Sales numbers].PercentofSales, IIf([Sales
numbers.SplitSales]=No,0,([Sales numbers.PercentofSales]*[Sales
info.Salesdollarsactual])) AS [Split Sales],
IIf([Marginincentive]=No,0,(SELECT[Marginincentive]FROM[Margin Acc table
1]WHERE [Margin Acc table 1].[Marginrate]=Round([Sales
info.Salesgpactual],2))) AS Margin, IIf([Sales numbers.SplitSales]=Yes,[Split
Sales]*[PAF Form.PersonalSales],([Sales info.Salesdollarsactual]*[PAF
Form.PersonalSales])) AS [Sales incentive], IIf([Sales
numbers.SplitSales]=Yes,[Split Sales]*[Margin],([Sales
info.Salesdollarsactual]*[Margin])) AS [Margin Incentive], [Branch
info].Branchsalesactual, [PAF Form].Branchsales, ([Branch
info.Branchsalesactual]*[PAF Form.Branchsales]) AS [Branch Incentive],
[Branch info].Month, [Branch info].Branchrosactual, [PAF Form].Rospluspoints,
[Branchrosactual]+[Rospluspoints] AS [Adjustment Factor], [PAF
Form].Rospayoutperpoint,
IIf([Branchrosactual]+[Rospluspoints]<0,0,([Branchrosactual]+[Rospluspoints])*[Rospayoutperpoint]*100)
AS [ROS Incentive], Supplements.Startdate, Supplements.Enddate,
Supplements.Startingamt, Supplements.Decreaseamt, Supplements.Month,
Supplements.Amtdue, [Guarantee Query].[Guarantee Incentive], [Guarantee
Query].Month
FROM [Sales info] RIGHT JOIN ([Branch info] LEFT JOIN (((([Personnel info]
LEFT JOIN [PAF Form] ON [Personnel info].FileNo = [PAF Form].FileNo) LEFT
JOIN [Sales numbers] ON [PAF Form].FileNo = [Sales numbers].FileNo) LEFT JOIN
Supplements ON [Personnel info].FileNo = Supplements.FileNo) LEFT JOIN
[Guarantee Query] ON [Personnel info].FileNo = [Guarantee Query].FileNo) ON
[Branch info].Branchlocationcode = [Personnel info].Branch) ON [Sales
info].SalesId = [Sales numbers].SalesId
WHERE ((([PAF Form].Commissions)=Yes) AND (([Sales info].Month) Between
[Enter Commission Start Date] And [Enter Commission End Date]) AND (([PAF
Form].Branchsales) Is Not Null) AND (([Branch info].Month) Between [Enter
Commission Start Date] And [Enter Commission End Date])) OR ((([PAF
Form].Commissions)=Yes) AND (([PAF Form].Rospluspoints) Is Not Null));
Pbb
John Spencer said:
I suggest that you help others answer your question by posting the SQL of
the query that fails to give you results and at least on of the sub queries.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
By the way if you are always retrieving a one month date range, you could
simplify the query input a little bit by using
Between [Enter Commission Start Date] AND
DateSerial(Year([Enter Commission Start Date]),Month([Enter Commission
Start Date])+1,0)
Or you could use
Between [Enter Commission Start Date] AND CDate(DateAdd("m",1, [Enter
Commission Start Date])-1)
Those look more complicated but they will calculate the end of the month
automatically when the start date is entered. The first will always get the
last day of the month.
The second will always get the day of the month for the next month that is
one less than the [Enter Commission Start Date] date. That is, enter Jan
12 and it will calculate Feb 11, Enter Jan 1 and it will calculate Jan 31
(1 day before Feb 1). It does get tricky when you enter January 31.
Pbb said:
Hi,
I have several queries that are working fine by themselves. They all have
parameters to enter Between [Enter Commission Start Date] And [Enter
Commission End date] which I would put in 1/1/06 and 1/31/06, or 2/1/06
and
2/28/06 and so on depending on the month I wish to see. However, when I
created this query with all of the tables and each table has a "month"
factor
I added these all to the query so I could set parameters as stated above
but
I cannot seem to get information beyond the 1/1/06 and 1/31/06 so when I
put
in a new date such as 2/1/06 and 2/28/06 is still gives me the January
information. Can some one please give advice on how to fix this? All of
the
queries seem to be working fine with just one table but when I added other
tables with "month" factor it does not give me the information I need. I
was
able to create a report using this query; the problem seems to be the
parameters.
