Select 2 dates on form and use query/report to sum cost between da

  • Thread starter Thread starter drewship
  • Start date Start date
D

drewship

Hello all.
I am creating a database to track repair costs on products. I want to use a
form to select between 2 dates entered in the 'DATE SERVICE CLAIM FILED'
column of my 'RepairInformation' table. I then need to click a button to open
my 'Project Status Report' report and sum the associated costs in the 'Cost'
column of the 'RepairInformation' table.

I have a form with combination boxes named 'BeginDate' and 'EndDate' and a
button to open the report. I have the 'BeginDate' and 'EndDate' displayed on
the report. Now I need to sum the costs from the 'RepairInformation' table
between the selected dates.

If anyone can help me it would be greatly appreciated since I am still
learning how to build Access databases.

Thanks in advance!!
 
drewship said:
Hello all.
I am creating a database to track repair costs on products. I want to use a
form to select between 2 dates entered in the 'DATE SERVICE CLAIM FILED'
column of my 'RepairInformation' table. I then need to click a button to open
my 'Project Status Report' report and sum the associated costs in the 'Cost'
column of the 'RepairInformation' table.

I have a form with combination boxes named 'BeginDate' and 'EndDate' and a
button to open the report. I have the 'BeginDate' and 'EndDate' displayed on
the report. Now I need to sum the costs from the 'RepairInformation' table
between the selected dates.
Click to expand...


Since the report's record source query is limited by the
begin and end dates, you can use a text box in the report
header and/or footer sections with the expression:
=Sum(Cost)
 
Thank you for your reply Marshall.

The only items I will be displaying on the report will be the selected
'BeginDate', selected 'EndDate', and the Sum of the 'Cost' colum in the
'RepairInformation' table between the dates selected. Since the costs
themselves will not be displayed on the report, I need something to perform
the sum function in the background before the report is displayed so just the
results are provided on the report.

Here is what I have now and is a combination of the Query wizard and my
attempts to make it work:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.OptionPeriod, Min(OptionPeriods.BeginDate) AS MinOfBeginDate,
Max(OptionPeriods.EndDate) AS MaxOfEndDate, Sum(RepairInformation.COST) AS
SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy'), OptionPeriods.OptionPeriod
HAVING (((Min(OptionPeriods.BeginDate))>=[Forms]![Monthly Project Cost
Report]![BeginDate]) AND ((Max(OptionPeriods.EndDate))<=[Forms]![Monthly
Project Cost Report]![EndDate]) AND
((Sum(RepairInformation.COST))>="BeginDate" And
(Sum(RepairInformation.COST))<="EndDate"));

Arer there any corrections that will make this work or is a different method
required?

Thanks.
 
drewship said:
The only items I will be displaying on the report will be the selected
'BeginDate', selected 'EndDate', and the Sum of the 'Cost' colum in the
'RepairInformation' table between the dates selected. Since the costs
themselves will not be displayed on the report, I need something to perform
the sum function in the background before the report is displayed so just the
results are provided on the report.

Here is what I have now and is a combination of the Query wizard and my
attempts to make it work:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.OptionPeriod, Min(OptionPeriods.BeginDate) AS MinOfBeginDate,
Max(OptionPeriods.EndDate) AS MaxOfEndDate, Sum(RepairInformation.COST) AS
SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy'), OptionPeriods.OptionPeriod
HAVING (((Min(OptionPeriods.BeginDate))>=[Forms]![Monthly Project Cost
Report]![BeginDate]) AND ((Max(OptionPeriods.EndDate))<=[Forms]![Monthly
Project Cost Report]![EndDate]) AND
((Sum(RepairInformation.COST))>="BeginDate" And
(Sum(RepairInformation.COST))<="EndDate"));

Arer there any corrections that will make this work or is a different method
required?
Click to expand...


I don't think I quite follow all the stuff in your query,
but the one thing that jumps out as very strange is the
conditions on the Sum(Cost). You are trying to compare the
total cost to text strings, which makes no sense. I think
you should remove those two criteria.

It's also seems likely that you should get rid of the Min
and Max of OptionPeriods.BeginDate. Try changing your
HAVING clause:

HAVING (((Min(OptionPeriods.BeginDate))>=[Forms]![Monthly
Project Cost Report]![BeginDate]) AND
((Max(OptionPeriods.EndDate))<=[Forms]![Monthly Project Cost
Report]![EndDate]) AND
((Sum(RepairInformation.COST))>="BeginDate" And
(Sum(RepairInformation.COST))<="EndDate"))

to:

WHERE BeginDate>=Forms![Monthly Project Cost
Report]!BeginDate AND EndDate<=Forms![Monthly Project Cost
Report]!EndDate
 
Marshall,
I could not figure out how to make the changes you suggested so I took the
query back to before I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');

I tried adding your WHERE clause but I keep getting syntax errors for a
missing operator. I am missing something but my lack of knowledge prevents me
from identifying the necessary componants and structure of SQL and a query.

Is there someplace I can upload an example database better illustrate the
problem?

Thanks!!

Marshall Barton said:
drewship said:
The only items I will be displaying on the report will be the selected
'BeginDate', selected 'EndDate', and the Sum of the 'Cost' colum in the
'RepairInformation' table between the dates selected. Since the costs
themselves will not be displayed on the report, I need something to perform
the sum function in the background before the report is displayed so just the
results are provided on the report.

Here is what I have now and is a combination of the Query wizard and my
attempts to make it work:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.OptionPeriod, Min(OptionPeriods.BeginDate) AS MinOfBeginDate,
Max(OptionPeriods.EndDate) AS MaxOfEndDate, Sum(RepairInformation.COST) AS
SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy'), OptionPeriods.OptionPeriod
HAVING (((Min(OptionPeriods.BeginDate))>=[Forms]![Monthly Project Cost
Report]![BeginDate]) AND ((Max(OptionPeriods.EndDate))<=[Forms]![Monthly
Project Cost Report]![EndDate]) AND
((Sum(RepairInformation.COST))>="BeginDate" And
(Sum(RepairInformation.COST))<="EndDate"));

Arer there any corrections that will make this work or is a different method
required?
Click to expand...


I don't think I quite follow all the stuff in your query,
but the one thing that jumps out as very strange is the
conditions on the Sum(Cost). You are trying to compare the
total cost to text strings, which makes no sense. I think
you should remove those two criteria.

It's also seems likely that you should get rid of the Min
and Max of OptionPeriods.BeginDate. Try changing your
HAVING clause:

HAVING (((Min(OptionPeriods.BeginDate))>=[Forms]![Monthly
Project Cost Report]![BeginDate]) AND
((Max(OptionPeriods.EndDate))<=[Forms]![Monthly Project Cost
Report]![EndDate]) AND
((Sum(RepairInformation.COST))>="BeginDate" And
(Sum(RepairInformation.COST))<="EndDate"))

to:

WHERE BeginDate>=Forms![Monthly Project Cost
Report]!BeginDate AND EndDate<=Forms![Monthly Project Cost
Report]!EndDate
Click to expand...
 
drewship said:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');
Click to expand...


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
 
Thank you Marshall.

In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:


Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost


The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

Thanks again!!

Marshall Barton said:
drewship said:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');
Click to expand...


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
 
As a clarification, the OptionPeriods table has dates hard coded in for
testing. Getting the BeginDate and EndDate from the form is what I would
prefer since I do not want to store them.

Thanks.

drewship said:
Thank you Marshall.

In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:


Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost


The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

Thanks again!!

Marshall Barton said:
drewship said:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');
Click to expand...


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
 
Let's try changing the Group By clause to:

GROUP BY RepairInformation.ID,
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])
--
Marsh
MVP [MS Access]

In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:

Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost

The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

Marshall Barton said:
drewship said:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');
Click to expand...


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
 
Thanks. I made the change, then ran the query by itself first to see what
would happen without BeginDate and EndDate inputs. It asked me for the
BeginDate and EndDate which I provided and 2 records were returned from the
OptionPeriods table instead of the RepairInformation table.

Perhaps I am making this too complicated. If I can get the sum of all the
records in the RepairInformation table between the dates selected on the
Monthly Project Cost Report form, then the OptionPeriods table would not be
needed as I first thought.

I am going to try to remove portions of the code related to the
OptionPeriods table to see if that works.

Marshall Barton said:
Let's try changing the Group By clause to:

GROUP BY RepairInformation.ID,
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])
--
Marsh
MVP [MS Access]

In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:

Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost

The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

Marshall Barton said:
drewship wrote:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
Click to expand...
 
This is what I ended up with and it works to a point:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS [DATE SERVICE CLAIM
FILED By Year], Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation
WHERE RepairInformation.[DATE SERVICE CLAIM FILED]>=Forms![Monthly Project
Cost Report]!BeginDate And RepairInformation.[DATE SERVICE CLAIM
FILED]<=Forms![Monthly Project Cost Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]);

It provides all the records between the selected dates, but it does not SUM
them into a single value as I expected the SUM to do. I only need the SUM of
all the records to display on my report, not the individual values. I will
work with it and see what I can do.

Thanks.

drewship said:
Thanks. I made the change, then ran the query by itself first to see what
would happen without BeginDate and EndDate inputs. It asked me for the
BeginDate and EndDate which I provided and 2 records were returned from the
OptionPeriods table instead of the RepairInformation table.

Perhaps I am making this too complicated. If I can get the sum of all the
records in the RepairInformation table between the dates selected on the
Monthly Project Cost Report form, then the OptionPeriods table would not be
needed as I first thought.

I am going to try to remove portions of the code related to the
OptionPeriods table to see if that works.

Marshall Barton said:
Let's try changing the Group By clause to:

GROUP BY RepairInformation.ID,
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])
--
Marsh
MVP [MS Access]

In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:

Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost

The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

:

drewship wrote:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
Click to expand...
 
While researching other postings indicating SUM issues, I noticed several
mentioning Group By and Where in that those statements are usually the
culprits with summing issues. I changed the code to the following and it
works:

SELECT Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation
WHERE (((RepairInformation.[DATE SERVICE CLAIM FILED])>=[Forms]![Monthly
Project Cost Report]![BeginDate] And (RepairInformation.[DATE SERVICE CLAIM
FILED])<=[Forms]![Monthly Project Cost Report]![EndDate]))
GROUP BY Year(RepairInformation.[DATE SERVICE CLAIM FILED]);

Now that I have the SumOfCost value correctly calculated, I seem to be
unable to display this on my report. I set SumOfCost as the Source for the
field on my report, but it still displays only the value of the first record
in the group.

Thoughts??

Thanks.

drewship said:
This is what I ended up with and it works to a point:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS [DATE SERVICE CLAIM
FILED By Year], Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation
WHERE RepairInformation.[DATE SERVICE CLAIM FILED]>=Forms![Monthly Project
Cost Report]!BeginDate And RepairInformation.[DATE SERVICE CLAIM
FILED]<=Forms![Monthly Project Cost Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]);

It provides all the records between the selected dates, but it does not SUM
them into a single value as I expected the SUM to do. I only need the SUM of
all the records to display on my report, not the individual values. I will
work with it and see what I can do.

Thanks.

drewship said:
Thanks. I made the change, then ran the query by itself first to see what
would happen without BeginDate and EndDate inputs. It asked me for the
BeginDate and EndDate which I provided and 2 records were returned from the
OptionPeriods table instead of the RepairInformation table.

Perhaps I am making this too complicated. If I can get the sum of all the
records in the RepairInformation table between the dates selected on the
Monthly Project Cost Report form, then the OptionPeriods table would not be
needed as I first thought.

I am going to try to remove portions of the code related to the
OptionPeriods table to see if that works.

Marshall Barton said:
Let's try changing the Group By clause to:

GROUP BY RepairInformation.ID,
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])
--
Marsh
MVP [MS Access]


drewship wrote:
In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:

Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost

The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

:

drewship wrote:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
Click to expand...
 
Never mind...Not sure what happened but I saved, closed, and reopened the
dabase and it does what I need it to do. Thank you very much Marshall!!

drewship said:
This is what I ended up with and it works to a point:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS [DATE SERVICE CLAIM
FILED By Year], Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation
WHERE RepairInformation.[DATE SERVICE CLAIM FILED]>=Forms![Monthly Project
Cost Report]!BeginDate And RepairInformation.[DATE SERVICE CLAIM
FILED]<=Forms![Monthly Project Cost Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]);

It provides all the records between the selected dates, but it does not SUM
them into a single value as I expected the SUM to do. I only need the SUM of
all the records to display on my report, not the individual values. I will
work with it and see what I can do.

Thanks.

drewship said:
Thanks. I made the change, then ran the query by itself first to see what
would happen without BeginDate and EndDate inputs. It asked me for the
BeginDate and EndDate which I provided and 2 records were returned from the
OptionPeriods table instead of the RepairInformation table.

Perhaps I am making this too complicated. If I can get the sum of all the
records in the RepairInformation table between the dates selected on the
Monthly Project Cost Report form, then the OptionPeriods table would not be
needed as I first thought.

I am going to try to remove portions of the code related to the
OptionPeriods table to see if that works.

Marshall Barton said:
Let's try changing the Group By clause to:

GROUP BY RepairInformation.ID,
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])
--
Marsh
MVP [MS Access]


drewship wrote:
In SQL view, I get the following error with the code you provided "You tried
to execute a query that does not include the specified expression 'BeginDate'
as part of an aggregate function." None of the SQL is hilighted.

Pseudo-code for what I need would be something like this:

Get Forms![Monthly Project cost Report]!BeginDate
Get Forms![Monthly Project cost Report]!EndDate
If Tables!RepairInformation![Date Service Claim Filed] >=Forms![Monthly
Project cost Report]!BeginDate AND <=Forms![Monthly Project cost
Report]!EndDate Then SumOfCost = SUM Tables!RepairInformation!Cost

The BeginDate, EndDate, and SumOfCost will all need to be displayed on a
report.
I hope this provides a better understanding of what I am trying to
accomplish. It looks pretty straight forward to me but I am an admitted
newbee to this.

:

drewship wrote:
I tried to make changes to what the wizard provided
which is this:

SELECT DISTINCTROW RepairInformation.ID, Format$(RepairInformation.[DATE
SERVICE CLAIM FILED],'yyyy') AS [DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate, Sum(RepairInformation.COST)
AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods ON RepairInformation.ID =
OptionPeriods.ID
GROUP BY RepairInformation.ID, Format$(RepairInformation.[DATE SERVICE CLAIM
FILED],'yyyy');


I am still not clear on exactly what you what you want it to
do, but see if this is close:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS
[DATE SERVICE CLAIM FILED By Year],
OptionPeriods.BeginDate, OptionPeriods.EndDate,
Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation INNER JOIN OptionPeriods
ON RepairInformation.ID = OptionPeriods.ID
WHERE OptionPeriods.BeginDate >= Forms![Monthly Project Cost
Report]!BeginDate
AND OptionPeriods.EndDate<=Forms![Monthly Project Cost
Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED])

Stay in SQL View, do not switch back to query design view.

If you get an error, post back with the exact message and
what in the query is highlighted.

If/when the query runs cleanly, explain what about the
result is not what you are hoping to get.
Click to expand...
Click to expand...
Click to expand...
 
drewship said:
Never mind...Not sure what happened but I saved, closed, and reopened the
dabase and it does what I need it to do. Thank you very much Marshall!!

drewship said:
This is what I ended up with and it works to a point:

SELECT RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]) AS [DATE SERVICE CLAIM
FILED By Year], Sum(RepairInformation.COST) AS SumOfCOST
FROM RepairInformation
WHERE RepairInformation.[DATE SERVICE CLAIM FILED]>=Forms![Monthly Project
Cost Report]!BeginDate And RepairInformation.[DATE SERVICE CLAIM
FILED]<=Forms![Monthly Project Cost Report]!EndDate
GROUP BY RepairInformation.ID,
Year(RepairInformation.[DATE SERVICE CLAIM FILED]);

It provides all the records between the selected dates, but it does not SUM
them into a single value as I expected the SUM to do. I only need the SUM of
all the records to display on my report, not the individual values. I will
work with it and see what I can do.
Click to expand...
Click to expand...


I just got back and see that you figured out how to get what
you wanted. Way to go!
 
Back
Top