Start and End Date Count Records Open per Day

[mikerea90]

The form that you saw in the database that I sent you has dates you must put in manually, but in the database I have, the StartDate and EndDate auto-populate.

 

[mikerea90]

Thanks again for your help!

 

[mikerea90]

Also - I am doing this query, but it does not work: (Says there is a missing operator):

SELECT [Index], [DateReceived], [Closed], [tblReportDate].[ReportDay]
FROM tblReportDate INNER JOIN tblIssues ON ([tblReportDate].[ReportDay]<=[tblIssues].[Closed]) AND ([tblReportDate].[ReportDay]>=[tblIssues].[DateReceived])
ODER BY Index

 

[pbaldy]

Is this a typo, or the problem?

ODER BY

 

[mikerea90]

haha wow I can't believe I didn't see that. wow haha

 

[mikerea90]

And then what are your thoughts on how to make a recordset? I am going to try to get one going right now - I just read up a little on recordset. I will post the code if I run into a problem. Thanks again.

 

[pbaldy]

No problem, and post back if you get stuck. You want to execute a query that deletes all the data in that table, open a recordset on it, and create 2 variables with the start and end dates. In a loop that compares the start date to the end date, add a record with the start date and then increment it by one.

 

[mikerea90]

Before I get that going I needed to finish this report that we spoke of before- the one where we show the currently opened, closed, open, and overdue on each date. I have done separate queries that count each area like you did before. Now I want to put all of these in one query. The only problem is that when I join all of them, I get like 320 records in the query. Do you know what I am doing wrong?

This is what I have so far:

SELECT qryAgeClosed.ReportDay, qryAgeClosed.Closed, qryAgeOpen.Open, qryAgeOpened.Opened, qryAgeOverdue.Overdue
FROM qryAgeClosed, qryAgeOpen, qryAgeOpened, qryAgeOverdue;

 

[mikerea90]

All of the queries have qryAgeClosedBase query like you had.

 

[pbaldy]

Without a join between the tables, you'll get a Cartesian product (if two tables each had 4 records, a query without a join would return 16 records). I think you want to join on the date field. You may want to left join all of them against the table with all the dates in it.

 

[mikerea90]

Okay I tried that, but I went through and I tried to group by Avg, Sum, Count, but each column had its own value and it was the same all the way down. Here is what I have:

SELECT tblReportDate.ReportDay, Count(qryAgeClosed.Closed) AS CountOfClosed, Count(qryAgeOpen.Open) AS CountOfOpen, Count(qryAgeOpened.Opened) AS CountOfOpened, Count(qryAgeOverdue.Overdue) AS CountOfOverdue
FROM qryAgeClosed, qryAgeOpen, qryAgeOpened, qryAgeOverdue, tblReportDate
GROUP BY tblReportDate.ReportDay;

 

[mikerea90]

I took out count. So I am just not sure where to go from here:

SELECT qryAgingGraphTotalBase.ReportDay, qryAgingGraphTotalBase.Closed, qryAgingGraphTotalBase.Open, qryAgingGraphTotalBase.Opened, qryAgingGraphTotalBase.Overdue
FROM qryAgingGraphTotalBase
GROUP BY qryAgingGraphTotalBase.ReportDay, qryAgingGraphTotalBase.Closed, qryAgingGraphTotalBase.Open, qryAgingGraphTotalBase.Opened, qryAgingGraphTotalBase.Overdue;

 

[pbaldy]

There are still no joins between tables/queries. Like this from one of the samples I made up:

SELECT tblIssues.IssueName, tblIssues.ArrivalDate, tblIssues.ClosedDate, tblDates.ReportDate
FROM tblDates INNER JOIN tblIssues ON tblDates.ReportDate = tblIssues.ArrivalDate

In design view, you would see lines between the appropriate fields in the upper section. I would assume the queries are already returning the counts, so you should just need to return the respective fields.

 

[mikerea90]

Okay the queries are returning the respective counts, but I am still unsure as to how I would add all of these queries into one query.

This is what I have, but there are errors:

SELECT qryAgingGraphTotalBase.ReportDay, qryAgingGraphTotalBase.Closed, qryAgingGraphTotalBase.Open, qryAgingGraphTotalBase.Opened, qryAgingGraphTotalBase.Overdue, tblReportDate.ReportDay
FROM qryAgingGraphTotalBase INNER JOIN tblReportDate ON tblReportDate.ReportDay = tbIssues.DateReceived
ORDER BY tblReportDate.ReportDay

 

[mikerea90]

Okay I modified it, but still errors...

SELECT tblReportDate.ReportDay, qryAgingGraphTotalBase.Closed, qryAgingGraphTotalBase.Open, qryAgingGraphTotalBase.Opened, qryAgingGraphTotalBase.Overdue, tblReportDate.ReportDay
FROM qryAgingGraphTotalBase INNER JOIN tblReportDate ON qryAgingGraphTotalBase.ReportDay = tblReportDate.ReportDay
ORDER BY tblReportDate.ReportDay;

 

[mikerea90]

Here is the query that I used to make the above query:

SELECT tblReportDate.ReportDay, qryAgeClosed.Closed, qryAgeOpen.Open, qryAgeOpened.Opened, qryAgeOverdue.Overdue
FROM qryAgeClosed, qryAgeOpen, qryAgeOpened, qryAgeOverdue, tblReportDate
GROUP BY tblReportDate.ReportDay, qryAgeClosed.Closed, qryAgeOpen.Open, qryAgeOpened.Opened, qryAgeOverdue.Overdue;

 

[mikerea90]

So I made the query in post #36 from the separate 4 queries and the ReportDate table. This was going to be my base like I had with the other 4 individual queries, but maybe I don't need that? Can I just make this the main query that puts all of them together?

 

[mikerea90]

Okay I tried this, but I still get repeats:

SELECT tblReportDate.ReportDay, qryAgingGraphTotalBase.Closed, qryAgingGraphTotalBase.Open, qryAgingGraphTotalBase.Opened, qryAgingGraphTotalBase.Overdue
FROM qryAgingGraphTotalBase LEFT JOIN tblReportDate ON qryAgingGraphTotalBase.ReportDay = tblReportDate.ReportDay;

 

[mikerea90]

Okay I think I know what you mean. I think I got it:

SELECT tblReportDate.ReportDay, qryAgeClosed.Closed, qryAgeOpen.Open, qryAgeOpened.Opened, qryAgeOverdue.Overdue
FROM (((tblReportDate INNER JOIN qryAgeOverdue ON tblReportDate.ReportDay = qryAgeOverdue.ReportDay) INNER JOIN qryAgeOpened ON tblReportDate.ReportDay = qryAgeOpened.ReportDay) INNER JOIN qryAgeOpen ON tblReportDate.ReportDay = qryAgeOpen.ReportDay) INNER JOIN qryAgeClosed ON tblReportDate.ReportDay = qryAgeClosed.ReportDay
GROUP BY tblReportDate.ReportDay, qryAgeClosed.Closed, qryAgeOpen.Open, qryAgeOpened.Opened, qryAgeOverdue.Overdue;

 

[mikerea90]

The only problem is that the fields only show when all of the dates have entries...