Calculation

[tbotkin via AccessMonster.com]

I am looking for a little guidance in performing a calculation. What I am
looking to do is calculate the total number of houses under warranty for each
day of the year. I can quickly create a query to calculate the number for
today, >=Date()-365 AND <=Date(), but I need to know what the number is for
each day of the year. My initial thought is that I need the date for each
day of the year to reference, so I have created a table containing such. To
perform the calculation I created a form in data sheet view containing the
field of dates that I created in the table. I then added an unbound field
and have been toying with the Dcount function as the fields default value.
This has presented two problems. The first is the calculation is not correct,
the second is the calculation does not change for each value has it relates
to the date field. Here the equation that I am using;

=DCount("[BackLogClosings]![SubLot]","[BackLogClosings]","[BackLogClosings]!
[ClosingDateHO]>= [FXDate] -365" AND "[BackLogClosings]![ClosingDateHo]<=
[FXDate]")

ClosingDateHO is the recorded closing date
FXDate is the fixed date for each day of the year

I openly welcome anyone’s ideas or suggestion on how best to perform this
calculation.

Thanks in advance.

 

[Allen Browne]

You already have the table with a date for each day of the year. Excellent.
I will refer to this table as tblDate, with a field named TheDate.

Create a query using your existing table
Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.
Group by the Date field.
Count the primary key field.
Save the query.

Create another query using this query as an input table, as well as tblDate.
Drag tblDate.TheDate onto the date field from date field in the query.
Access draws a join line.
Double-click the join line.
Access shows a dialog with 3 options.
Choose the one that says:
All records form tblDate, and any matches from ...

Drag tblDate.TheDate into the output grid.
Drag Query1.CountOfId into the grid.

The query shows the date and the count.

If you want the count to show as zero instead of blank, change the CountOfId
to this expression:
TheCount: CLng(Nz([CountOfID],0))

 

[Guest]

Hi tbotkin...

I have read your description and kind of get what you are trying to do, but
the question is why? What is the end goal that you are trying to reach?

Damian.