Summary Total of Two Date Fields

K

Karl Burrows

I want to create a summary of events based on two date criteria. I want a
total of how many times the date is not empty for each field value. If I
create a query with the two fields and use count, I get the same number
value for both. It is to track how many lots in a neighborhood are platted
and/or paved, so the summary report should list each subdivision with a
column for total platted and a column for total paved. For some reason, I
can not figure out how to get unique count values for each.

Can someone help me?

Thanks!
 
J

John Vinson

I want to create a summary of events based on two date criteria. I want a
total of how many times the date is not empty for each field value. If I
create a query with the two fields and use count, I get the same number
value for both. It is to track how many lots in a neighborhood are platted
and/or paved, so the summary report should list each subdivision with a
column for total platted and a column for total paved. For some reason, I
can not figure out how to get unique count values for each.

Can someone help me?
Click to expand...

I think the confusion may be because the Count() operator doesn't
count values - it counts *records* in your table.

One way to count non-null values is to use a Totals query; in it, put
calculated fields such as

CountOfX: Sum(IIF(IsNull([X]), 0, 1))

This will add 0 for each null value of the field X, and 1 for each
non-NULL.

John W. Vinson[MVP]
 
K

Karl Burrows

Okay, that works perfectly in the query. Now how can I group these by
month? I created a new column with the formula "PlatDate By Month:
Format$([tblLotInformation].[PlatDate],'mmmm yyyy')" to group totals by
month and it works in the query, but because the plat date and pave date may
be different, it will not sort the two together in a report. The query
itself works, but how do I convert this to a report that will summarize this
by month? I seems like I would have to put two footers in the report.

Thanks!

I want to create a summary of events based on two date criteria. I want a
total of how many times the date is not empty for each field value. If I
create a query with the two fields and use count, I get the same number
value for both. It is to track how many lots in a neighborhood are platted
and/or paved, so the summary report should list each subdivision with a
column for total platted and a column for total paved. For some reason, I
can not figure out how to get unique count values for each.

Can someone help me?
Click to expand...

I think the confusion may be because the Count() operator doesn't
count values - it counts *records* in your table.

One way to count non-null values is to use a Totals query; in it, put
calculated fields such as

CountOfX: Sum(IIF(IsNull([X]), 0, 1))

This will add 0 for each null value of the field X, and 1 for each
non-NULL.

John W. Vinson[MVP]
 
J

John Vinson

Okay, that works perfectly in the query. Now how can I group these by
month? I created a new column with the formula "PlatDate By Month:
Format$([tblLotInformation].[PlatDate],'mmmm yyyy')" to group totals by
month and it works in the query, but because the plat date and pave date may
be different, it will not sort the two together in a report. The query
itself works, but how do I convert this to a report that will summarize this
by month? I seems like I would have to put two footers in the report.
Click to expand...

I guess I don't understand the structure of your table. Where are the
PlatDate and PaveDate fields? If you're grouping (summing??) by
PlatDate, what is "the PaveDate" if there are several records with
different pave dates?


John W. Vinson[MVP]
 
K

Karl Burrows

These are just key dates we track for each lot. When the municipality plats
a section of lots, we note it in the lot table. When the roads are cut
through that section, the Plat Date is added. We are trying to check the
status of lots in a subdivision by tracking how many are platted and how
many are paved for a given month to see how inventory is developed. They
may pave lots before they are platted and or not all lots in a plat are
paved at the same time, so there are variances.

Maybe I am approaching this from the wrong direction. Does this help?

Thanks!

Okay, that works perfectly in the query. Now how can I group these by
month? I created a new column with the formula "PlatDate By Month:
Format$([tblLotInformation].[PlatDate],'mmmm yyyy')" to group totals by
month and it works in the query, but because the plat date and pave date
may
be different, it will not sort the two together in a report. The query
itself works, but how do I convert this to a report that will summarize
this
by month? I seems like I would have to put two footers in the report.
Click to expand...

I guess I don't understand the structure of your table. Where are the
PlatDate and PaveDate fields? If you're grouping (summing??) by
PlatDate, what is "the PaveDate" if there are several records with
different pave dates?


John W. Vinson[MVP]
 
J

John Vinson

These are just key dates we track for each lot. When the municipality plats
a section of lots, we note it in the lot table. When the roads are cut
through that section, the Plat Date is added. We are trying to check the
status of lots in a subdivision by tracking how many are platted and how
many are paved for a given month to see how inventory is developed. They
may pave lots before they are platted and or not all lots in a plat are
paved at the same time, so there are variances.

Maybe I am approaching this from the wrong direction. Does this help?
Click to expand...

Since I don't have any clear idea how you're approaching this, I can
only guess that you are coming from the wrong direction!

You can count non-NULL dates for a given subdivision by setting the
Control Source of a report textbox (let's call it txtPlatted) to:

+DCount("*", "[yourtable]", "[Subdivision] = " & Me!subdivisionID & "
AND [Plat Date] IS NOT NULL")

using your appropriate table and field names of course. Do the same
for another textbox with the pave date field.

John W. Vinson[MVP]
 
K

Karl Burrows

I have two tables. The first is the general information for a neighborhood
(name, location, number of lots, project manager). The second table
includes individual lot information (lot number; address; sales price; map,
book and page; and "critical" dates to include plat date, pave date, closing
date). One goal is to track lot development from the ground up. This query
and corresponding report would track an input date range to show how many
lots in a neighborhood have been platted and how many have been paved for
each month in that date range chosen. They are independent dates, but each
occurs to groups of lots at one time, but not necessarily identical lots.
The idea is to track the pipeline to determine when lots would be available
for sale and look at the trends of the development.

Does that help or is there anything else that can help to determine how to
run this? I have a simple query that works based on the formula you
provided that includes the neighborhood field from one table and PlatDate
and PaveDate from the other table using the formula. If I run the query, it
provides the summary information I need, but I am trying to translate that
into a report that will group these numbers by month. The problem is there
are two columns of grouped data that are driven by dates.

I almost think I need two separate queries for both platted and paved and
then join them to combine data based on one date so I have a number of
platted lots and a number of paved lots for the same date and then sort them
that way.

Thanks!


These are just key dates we track for each lot. When the municipality
plats
a section of lots, we note it in the lot table. When the roads are cut
through that section, the Plat Date is added. We are trying to check the
status of lots in a subdivision by tracking how many are platted and how
many are paved for a given month to see how inventory is developed. They
may pave lots before they are platted and or not all lots in a plat are
paved at the same time, so there are variances.

Maybe I am approaching this from the wrong direction. Does this help?
Click to expand...

Since I don't have any clear idea how you're approaching this, I can
only guess that you are coming from the wrong direction!

You can count non-NULL dates for a given subdivision by setting the
Control Source of a report textbox (let's call it txtPlatted) to:

+DCount("*", "[yourtable]", "[Subdivision] = " & Me!subdivisionID & "
AND [Plat Date] IS NOT NULL")

using your appropriate table and field names of course. Do the same
for another textbox with the pave date field.

John W. Vinson[MVP]
 
K

Karl Burrows

After playing with a few different queries, it seems the issue here is that
platted and paved are unique records, but by including them in the same
query, I am overlapping dates since they are unique values, which is
creating the problem. The count of plat dates and the count of pave dates
need to be independent of each other.

I have two tables. The first is the general information for a neighborhood
(name, location, number of lots, project manager). The second table
includes individual lot information (lot number; address; sales price; map,
book and page; and "critical" dates to include plat date, pave date, closing
date). One goal is to track lot development from the ground up. This query
and corresponding report would track an input date range to show how many
lots in a neighborhood have been platted and how many have been paved for
each month in that date range chosen. They are independent dates, but each
occurs to groups of lots at one time, but not necessarily identical lots.
The idea is to track the pipeline to determine when lots would be available
for sale and look at the trends of the development.

Does that help or is there anything else that can help to determine how to
run this? I have a simple query that works based on the formula you
provided that includes the neighborhood field from one table and PlatDate
and PaveDate from the other table using the formula. If I run the query, it
provides the summary information I need, but I am trying to translate that
into a report that will group these numbers by month. The problem is there
are two columns of grouped data that are driven by dates.

I almost think I need two separate queries for both platted and paved and
then join them to combine data based on one date so I have a number of
platted lots and a number of paved lots for the same date and then sort them
that way.

Thanks!


These are just key dates we track for each lot. When the municipality
plats
a section of lots, we note it in the lot table. When the roads are cut
through that section, the Plat Date is added. We are trying to check the
status of lots in a subdivision by tracking how many are platted and how
many are paved for a given month to see how inventory is developed. They
may pave lots before they are platted and or not all lots in a plat are
paved at the same time, so there are variances.

Maybe I am approaching this from the wrong direction. Does this help?
Click to expand...

Since I don't have any clear idea how you're approaching this, I can
only guess that you are coming from the wrong direction!

You can count non-NULL dates for a given subdivision by setting the
Control Source of a report textbox (let's call it txtPlatted) to:

+DCount("*", "[yourtable]", "[Subdivision] = " & Me!subdivisionID & "
AND [Plat Date] IS NOT NULL")

using your appropriate table and field names of course. Do the same
for another textbox with the pave date field.

John W. Vinson[MVP]
 
J

John Vinson

After playing with a few different queries, it seems the issue here is that
platted and paved are unique records, but by including them in the same
query, I am overlapping dates since they are unique values, which is
creating the problem. The count of plat dates and the count of pave dates
need to be independent of each other.
Click to expand...

Then use my suggestion, of two textboxes each with an independent
DLookUp.


John W. Vinson[MVP]
 
K

Karl Burrows

If you are still there, can you tell me how the Me!subdivisionID fits into
the equation?

Thanks!

After playing with a few different queries, it seems the issue here is that
platted and paved are unique records, but by including them in the same
query, I am overlapping dates since they are unique values, which is
creating the problem. The count of plat dates and the count of pave dates
need to be independent of each other.
Click to expand...

Then use my suggestion, of two textboxes each with an independent
DLookUp.


John W. Vinson[MVP]
 
J

John Vinson

If you are still there, can you tell me how the Me!subdivisionID fits into
the equation?
Click to expand...

That was MY GUESS, which may have been quite wrong, about how your
table is structured. I assumed that you had a table with multiple
subdivisions, and that you wanted to limit the calculation to those
properties in a single subdivision.

Remember - *you* can see your tables, and know how they're structured.
I can't! Sorry if I was sowing confusion as a result!

John W. Vinson[MVP]
 
K

Karl Burrows

I'm sorry if I am not doing a good job of describing my issues. I want a
list summary of totals for all subdivisions in the table for each of platted
lots and paved lots. I have multiple subdivisions. I researched DCount all
last night, which is why I posted back. It seems to only count for a
specific value like "=SUBD A".

SUBD A 20 25
SUBD B 50 75
SUBD C 40 25
etc.

Thanks!

If you are still there, can you tell me how the Me!subdivisionID fits into
the equation?
Click to expand...

That was MY GUESS, which may have been quite wrong, about how your
table is structured. I assumed that you had a table with multiple
subdivisions, and that you wanted to limit the calculation to those
properties in a single subdivision.

Remember - *you* can see your tables, and know how they're structured.
I can't! Sorry if I was sowing confusion as a result!

John W. Vinson[MVP]
 
J

John Vinson

I'm sorry if I am not doing a good job of describing my issues. I want a
list summary of totals for all subdivisions in the table for each of platted
lots and paved lots. I have multiple subdivisions. I researched DCount all
last night, which is why I posted back. It seems to only count for a
specific value like "=SUBD A".

SUBD A 20 25
SUBD B 50 75
SUBD C 40 25
Click to expand...

Again...

I CAN'T SEE YOUR TABLES.

I can't see what query you're actually using.

I don't know your fieldnames (I see some sample data but I have no
idea what fields that data is in or what it means).

Give me a bit of help here, please? and I'll be happy to try to help
you.

You can pass a field value to the third argument of DCount, *if* you
wish to limit the records which are being counted; if you want to
count all the records in your table, just leave off the third
argument.

John W. Vinson[MVP]
 
K

Karl Burrows

Sorry, I posted the info earlier, but maybe I didn't put in enough info:

I have two tables. The first is the general information for a neighborhood
(name, location, number of lots, project manager). The second table
includes individual lot information (lot number [LotNumber]; address
[StreetAddress]; sales price [SalesPrice]; map [MapID],
book [MapBook] and page [MapPage]; and "critical" dates to include plat date
[PlatDate], pave date [PaveDate], closing
date [ActualClosingDate]). One goal is to track lot development from the
ground up. This query
and corresponding report would track an input date range to show how many
lots in a neighborhood have been platted and how many have been paved for
each month in that date range chosen. They are independent dates, but each
occurs to groups of lots at one time, but not necessarily identical lots.
The idea is to track the pipeline to determine when lots would be available
for sale and look at the trends of the development.

The query I have so far collects the totals for all subdivisions using the
DCount, but it gives me the grand totals of all lots platted and paved, not
broken out by subdivision:
=DCount("[PlatDate]","tblLotInformation","[Subdivision]"), so each
subdivision listed shows the grand totals for all platted and paved. This
is where I am stuck.

Anything else I can offer that would help with my situation? Thanks for
your help and patience!

I'm sorry if I am not doing a good job of describing my issues. I want a
list summary of totals for all subdivisions in the table for each of
platted
lots and paved lots. I have multiple subdivisions. I researched DCount
all
last night, which is why I posted back. It seems to only count for a
specific value like "=SUBD A".

SUBD A 20 25
SUBD B 50 75
SUBD C 40 25
Click to expand...

Again...

I CAN'T SEE YOUR TABLES.

I can't see what query you're actually using.

I don't know your fieldnames (I see some sample data but I have no
idea what fields that data is in or what it means).

Give me a bit of help here, please? and I'll be happy to try to help
you.

You can pass a field value to the third argument of DCount, *if* you
wish to limit the records which are being counted; if you want to
count all the records in your table, just leave off the third
argument.

John W. Vinson[MVP]
 
Top