Query Question

[Secret Squirrel]

I'm trying to count my records based on days late. I have 5 queries set up to
do this based on the days late. Here's is how they work:

TotalRecords Query
RecordsOnTime Query
2-3DaysLate Query
4-7DaysLate Query
8+DaysLate Query

They all count the records that fall into their criteria along with the
month & year that the fall into. How can I create 1 query to summarize all
these queries into 1 query?
Here is an example of one of my queries:

SELECT Count(qryPerformanceOnTime.RecordID) AS CountOfRecordID,
Format([RecDate],"mmmm") AS MonthCount, Format([RecDate],"yyyy") AS YearCount
FROM qryPerformanceOnTime
GROUP BY Format([RecDate],"mmmm"), Format([RecDate],"yyyy");

The above query is the one that counts the number of records that are on time.
The other queries are just like these but tied into other queries that
filter only the records that fall into their criteria.

I want to be able to count the records from each of these queries by month
and year and put them all into 1 query. Is this possible?

 

[Douglas J. Steele]

Without knowing the details of qryPerformanceOnTime and the other queries,
it's difficult to give a precise answer, but what you can do is create a
query that uses IIf statements to return 1 if the criteria is met and 0 if
it isn't, then create a second query that sums the values returned by those
IIf statements.

SELECT IIf([condition for on time], 1, 0) As OnTime, IIf([condition for 2-3
days late], 1, 0) As 2to3Late, ...

then

SELECT Sum([OnTime]) As NumberOnTime, Sum([2to3Late]) As Number2-3DaysLate,
....

 

[Dale Fye]

Or, you could create a new table (tbl_Timeliness) which contains fields:
Range, Text
RangeSort, int
RangeStart, int
RangeEnd, int
with values that look something like:

Range RangeSort RangeStart RangeEnd
On time 1 0 1
2-3 Days Late 2 2 3
4-7 Days Late 3 4 7
8 or more 4 8 1000

Since I don't know the content of your main table, I'll just assume you have
a table that contains PaymentDueDates:

Select T.RangeSort, T.Range, Sum(P.ID) as PaymentCount
FROM tbl_Payments P, tbl_Timeliness T
WHERE DateDiff("d", P.PaymentDueDate, NZ(P.PaymentDate, Date())
Between T.RangeStart AND T.RangeEnd
GROUP BY T.RangeSort, T.Range
ORDER BY T.RangeSort

This methodology gives you a lot more flexibility in setting and changing
the range of dates for your query. With your method, if you decide you want
to do 2-4 days Late instead of 2-3, you have to rewrite several queries.
With this method, you only have to change a couple of values in a table.

HTH
Dale

 

[MikeB]

I'm trying to count my records based on days late. I have 5 queries set up to
do this based on the days late. Here's is how they work:

TotalRecords Query
RecordsOnTime Query
2-3DaysLate Query
4-7DaysLate Query
8+DaysLate Query

They all count the records that fall into their criteria along with the
month & year that the fall into. How can I create 1 query to summarize all
these queries into 1 query?
Here is an example of one of my queries:

SELECT Count(qryPerformanceOnTime.RecordID) AS CountOfRecordID,
Format([RecDate],"mmmm") AS MonthCount, Format([RecDate],"yyyy") AS YearCount
FROM qryPerformanceOnTime
GROUP BY Format([RecDate],"mmmm"), Format([RecDate],"yyyy");

The above query is the one that counts the number of records that are on time.
The other queries are just like these but tied into other queries that
filter only the records that fall into their criteria.

I want to be able to count the records from each of these queries by month
and year and put them all into 1 query. Is this possible?

Can you use a UNION query to add the results of the different queries
into a single query result?

 

[Secret Squirrel]

I like your approach. How would I also take this a step further and break
these ranges down by month? Here is what I used for my query:

SELECT T.RangeSort, T.Range, Count(P.RecordID) AS RecordCount
FROM tblPerformance AS P, tblTimeFrames AS T
WHERE
(((DateDiff("d",[P.OrigDate],[P.RecDate])-(DateDiff("ww",[P.OrigDate],[P.RecDate],7)+DateDiff("ww",[P.OrigDate],[P.RecDate],1)))
Between [T].[RangeStart] And [T].[RangeEnd]))
GROUP BY T.RangeSort, T.Range
ORDER BY T.RangeSort;

I would like to have the results broken down by month using the RecDate.

Or, you could create a new table (tbl_Timeliness) which contains fields:
Range, Text
RangeSort, int
RangeStart, int
RangeEnd, int
with values that look something like:

Range RangeSort RangeStart RangeEnd
On time 1 0 1
2-3 Days Late 2 2 3
4-7 Days Late 3 4 7
8 or more 4 8 1000

Since I don't know the content of your main table, I'll just assume you have
a table that contains PaymentDueDates:

Select T.RangeSort, T.Range, Sum(P.ID) as PaymentCount
FROM tbl_Payments P, tbl_Timeliness T
WHERE DateDiff("d", P.PaymentDueDate, NZ(P.PaymentDate, Date())
Between T.RangeStart AND T.RangeEnd
GROUP BY T.RangeSort, T.Range
ORDER BY T.RangeSort

This methodology gives you a lot more flexibility in setting and changing
the range of dates for your query. With your method, if you decide you want
to do 2-4 days Late instead of 2-3, you have to rewrite several queries.
With this method, you only have to change a couple of values in a table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I'm trying to count my records based on days late. I have 5 queries set up to
do this based on the days late. Here's is how they work:

TotalRecords Query
RecordsOnTime Query
2-3DaysLate Query
4-7DaysLate Query
8+DaysLate Query

They all count the records that fall into their criteria along with the
month & year that the fall into. How can I create 1 query to summarize all
these queries into 1 query?
Here is an example of one of my queries:

SELECT Count(qryPerformanceOnTime.RecordID) AS CountOfRecordID,
Format([RecDate],"mmmm") AS MonthCount, Format([RecDate],"yyyy") AS YearCount
FROM qryPerformanceOnTime
GROUP BY Format([RecDate],"mmmm"), Format([RecDate],"yyyy");

The above query is the one that counts the number of records that are on time.
The other queries are just like these but tied into other queries that
filter only the records that fall into their criteria.

I want to be able to count the records from each of these queries by month
and year and put them all into 1 query. Is this possible?

 

[Dale Fye]

I would probably add fields for Year and Month to the queries select clause,
and add them to the Group By and Order By clauses as well. Something like:

SELECT Year(P.RecDate) as YrRcvd, _
Month(P.RecDate) as MnRcvd, _
T.RangeSort, T.Range, _
Count(P.RecordID) AS RecordCount
FROM tblPerformance AS P, tblTimeFrames AS T
WHERE DateDiff("d",[P.OrigDate],[P.RecDate])- _
DateDiff("ww",[P.OrigDate],[P.RecDate],7)+ _
DateDiff("ww",[P.OrigDate],[P.RecDate],1)
Between [T].[RangeStart] And [T].[RangeEnd]
GROUP BY Year(P.RecDate) as YrRcvd, _
Month(P.RecDate) as MnRcvd, _
T.RangeSort, T.Range
ORDER BY Year(P.RecDate) as YrRcvd, _
Month(P.RecDate) as MnRcvd, _
T.RangeSort;

HTH
Dale



--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I like your approach. How would I also take this a step further and break
these ranges down by month? Here is what I used for my query:

SELECT T.RangeSort, T.Range, Count(P.RecordID) AS RecordCount
FROM tblPerformance AS P, tblTimeFrames AS T
WHERE
(((DateDiff("d",[P.OrigDate],[P.RecDate])-(DateDiff("ww",[P.OrigDate],[P.RecDate],7)+DateDiff("ww",[P.OrigDate],[P.RecDate],1)))
Between [T].[RangeStart] And [T].[RangeEnd]))
GROUP BY T.RangeSort, T.Range
ORDER BY T.RangeSort;

I would like to have the results broken down by month using the RecDate.

Or, you could create a new table (tbl_Timeliness) which contains fields:
Range, Text
RangeSort, int
RangeStart, int
RangeEnd, int
with values that look something like:

Range RangeSort RangeStart RangeEnd
On time 1 0 1
2-3 Days Late 2 2 3
4-7 Days Late 3 4 7
8 or more 4 8 1000

Since I don't know the content of your main table, I'll just assume you have
a table that contains PaymentDueDates:

Select T.RangeSort, T.Range, Sum(P.ID) as PaymentCount
FROM tbl_Payments P, tbl_Timeliness T
WHERE DateDiff("d", P.PaymentDueDate, NZ(P.PaymentDate, Date())
Between T.RangeStart AND T.RangeEnd
GROUP BY T.RangeSort, T.Range
ORDER BY T.RangeSort

This methodology gives you a lot more flexibility in setting and changing
the range of dates for your query. With your method, if you decide you want
to do 2-4 days Late instead of 2-3, you have to rewrite several queries.
With this method, you only have to change a couple of values in a table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I'm trying to count my records based on days late. I have 5 queries set up to
do this based on the days late. Here's is how they work:

TotalRecords Query
RecordsOnTime Query
2-3DaysLate Query
4-7DaysLate Query
8+DaysLate Query

They all count the records that fall into their criteria along with the
month & year that the fall into. How can I create 1 query to summarize all
these queries into 1 query?
Here is an example of one of my queries:

SELECT Count(qryPerformanceOnTime.RecordID) AS CountOfRecordID,
Format([RecDate],"mmmm") AS MonthCount, Format([RecDate],"yyyy") AS YearCount
FROM qryPerformanceOnTime
GROUP BY Format([RecDate],"mmmm"), Format([RecDate],"yyyy");

The above query is the one that counts the number of records that are on time.
The other queries are just like these but tied into other queries that
filter only the records that fall into their criteria.

I want to be able to count the records from each of these queries by month
and year and put them all into 1 query. Is this possible?