Re: Calculation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!



Allen Browne wrote:
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.

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: Option Group Question
    ... I did the first one and put it in the query. ... the query it now says Closing And/Or Doc Prep instead of 1 And/Or 2. ... other thing is, if I want to pull ONLY the records marked "Closing", how do I ... there a way to make the 1 say Closing so on the report management knows what ...
    (microsoft.public.access.formscoding)
  • Re: SQL query help
    ... Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL ... and example data for your tables, along with the expected output from your ... I have a table with products and closing dates for each of 4 quarters ... I need a query that will look at earning_dates and return the latest 6 ...
    (comp.databases.ms-sqlserver)
  • Re: Enter Parameter Value
    ... The query the report is based on uses the values of the controls on the ... that pops up after closing my query dialog form. ... I have gone back and looked at my query, the record source of my combo ...
    (microsoft.public.access.queries)
  • back to main menu
    ... >Are you closing each of the menu forms as you open the ... >next one or are you just opening the next form? ... >to just Open it from a command button. ... >>select a Query Menu form, ...
    (microsoft.public.access.forms)
  • Re: Enterprise Manager confusion
    ... View designer over QA. ... as it does like the Access query builder. ... By the way, you can drag columns individually in QA, you don't have to ... entering it in the first place, and the View designer offers the ability ...
    (microsoft.public.sqlserver.tools)