If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count Number Of Days That Had One Or More Records
Creating a crosstab query that counts number of records for a specified
period of time. Displays each day of the month with the sum of records for the month and has a column that adds the total. Need to be able to count the number of days in which records are found in order to get an average of records for the month? If there was one record or 100 record, in a day, that day would count as 1. How? |
#2
|
|||
|
|||
Count Number Of Days That Had One Or More Records
Hi JC,
Try creating a second crosstab query that does the counting that you need. It should have the same criteria as your first crosstab query (remember to define parameters for crosstab query criteria: http://support.microsoft.com/?id=209778). For example, you could have a form with two text boxes to define your starting and ending dates. Each crosstab query would include criteria that looked to the same open form to get the date values. One you have the individual crosstab queries working, try adding each of them as source tables to a new SELECT query. You'll need to have a unique key field that you can join the two source crosstab queries on, to prevent a cartesian product result. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "JC" wrote: Creating a crosstab query that counts number of records for a specified period of time. Displays each day of the month with the sum of records for the month and has a column that adds the total. Need to be able to count the number of days in which records are found in order to get an average of records for the month? If there was one record or 100 record, in a day, that day would count as 1. How? |
#3
|
|||
|
|||
Count Number Of Days That Had One Or More Records
You don't say on what the crosstab query is grouped so for this example I'll
assume you have a table Orders, with columns OrderDate and CustomerID and its grouped on CustomerID, i.e. it returns the number of orders per customer per day of the month. You can count the number of days on which a customer placed an order within a specific month with a query like this: SELECT CustomerID, COUNT(*) AS DayCount FROM (SELECT DISTINCT CustomerID, YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth FROM Orders) WHERE OrderYear = [Enter Year:] AND OrderMonth = [Enter Month:] GROUP BY CustomerID; This can now be joined in a third query to the crosstab query on CustomerID. Ken Sheridan Stafford, England "JC" wrote: Creating a crosstab query that counts number of records for a specified period of time. Displays each day of the month with the sum of records for the month and has a column that adds the total. Need to be able to count the number of days in which records are found in order to get an average of records for the month? If there was one record or 100 record, in a day, that day would count as 1. How? |
#4
|
|||
|
|||
Count Number Of Days That Had One Or More Records
"Ken Sheridan" wrote: You don't say on what the crosstab query is grouped so for this example I'll assume you have a table Orders, with columns OrderDate and CustomerID and its grouped on CustomerID, i.e. it returns the number of orders per customer per day of the month. You can count the number of days on which a customer placed an order within a specific month with a query like this: Correction. That should have been: SELECT CustomerID, COUNT(*) AS DayCount FROM (SELECT DISTINCT CustomerID, OrderDate FROM Orders) WHERE YEAR(OrderDate) = [Enter Year:] AND MONTH(OrderDate) = [Enter Month:] GROUP BY CustomerID; The first one would have counted distinct month not days. Ken Sheridan Stafford, England This can now be joined in a third query to the crosstab query on CustomerID. Ken Sheridan Stafford, England "JC" wrote: Creating a crosstab query that counts number of records for a specified period of time. Displays each day of the month with the sum of records for the month and has a column that adds the total. Need to be able to count the number of days in which records are found in order to get an average of records for the month? If there was one record or 100 record, in a day, that day would count as 1. How? |
Thread Tools | |
Display Modes | |
|
|