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

 

[Damian S]

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.

 

[tbotkin via AccessMonster.com]

Thanks for the timely reply. Working through your proposed suggestion will
count the number of closing on a given day. This is close to what I am
looking for but not quite it. Please allow me to try and explain this in a
different way. What I want to do is determine the number of obligations for
each given day in the year. The obligation is defined by the warranty period,
which is for 365 days after the closing date. Using the below query I can
determine there are X number of units that have a closing date between
today’s date and today’s date minus 365 days which is the obligation for
today. How can I see this same calculation represented for each day of the
year?

SELECT Count([BackLogClosings]![Sub/Lot]) AS 1yrWrty
FROM [BackLogClosings]
WHERE ((([BackLogClosings].[ClosingDateHO])>=Date()-365 And ([BackLogClosings]
.[ClosingDateHO])<=Date()));

In the end I will use this information in a graph to see trends. I can
determine averages per week, month or year. And I could input proposed
closing dates and forecast ahead, determining needed rescores and funds…..

Sorry if I was not clearer in my first posting. Thanks again!

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

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

[quoted text clipped - 26 lines]

Thanks in advance.

 

[tbotkin via AccessMonster.com]

Hi Damian,

In the end I will use this information in a graph to see trends. I can
determine averages per week, month or year. And I could input proposed
closing dates and forecast ahead, determining needed rescores and funds…..

Thanks for your Reply!
tbotkin

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.

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

[quoted text clipped - 20 lines]

Thanks in advance.

 

[tbotkin via AccessMonster.com]

Hi Allen,

Please ignore my previous post. I have figured out my problem using the
below query. Thanks for pointing me in the write direction with the sum
feature in the queries.

Many Thanks!

SELECT tbl_FXDate.FXDate, Count([BackLogClosings].[SubLot]) AS [CountOfSubLot]

FROM tbl_FXDate, MSTR INNER JOIN [BackLogClosings] ON MSTR.[SubLot] =
[BackLogClosings].[SubLot]
WHERE ((([BackLogClosings].[ClosingDateHO])>=[FXDate]-365 And (
[BackLogClosings].[ClosingDateHO])<=[FXDate]) AND ((MSTR Company)="raf"))
GROUP BY tblFXDate.FXDate;

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

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

[quoted text clipped - 26 lines]

Thanks in advance.

 

[tbotkin via AccessMonster.com]

Hi Damian,

Please ignore my previous post I have figured out the problem.

Thanks!
tbotkin

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.

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

[quoted text clipped - 20 lines]

Thanks in advance.