Putting mult query to one to make report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I needed to make a report that shows how many records took how long to
complete so in the table there is a request date and a complete date and so I
have a query set up to tell the date difference between them and then if it
is 1 it will list it etc ten queries like that for records that took 1 day
then 2 day etc, then I was trying to make a query that pulled all those
queries and counted the amount of records in each query so there would be 10
fields to display the amount of record that took x amount of days, but that
query doesn't work right it will only pull the info if the amount of records
in each of the 10 query is the same then it will display 1 for all of them
but if any are different it won't pull that, can anyone give me a hand?
 
Use DateDiff() to get the difference between the two dates.
Group by this value, and get the count of the primary key field:

SELECT DateDiff("d",[RequestDate],[CompleteDate]) AS Days,
Count(Request.ID) AS CountOfID
FROM Request
GROUP BY DateDiff("d",[RequestDate],[CompleteDate]);

Once you have that working, you can use that query as an input "table" for a
crosstab query if you wish to have the Days as column headings.
 
Back
Top