HELP WITH QUERY WITH MULTIPLE CRITERIA

[generaluser]

I have the following question:

I have a data base that logs customer's complaints with a particular business. I want to run a weekly query that produces the follwing data by Department (Between July 1 and July 15):

Total new complaints, total # of completed complaints, total complaints not solved, oldest complaint not solved, avg days took to complete complaints.

-Total new complaints should be based on date received
-total # of completed complaints should be based on date completed
-Total complaints not solved has no specific date criteria
-Oldest complaint not solved has no specific date criteria
-For Avg days will include solved complaints based on date completed (I have the formula to determine avg figured out)

Is it possible to do all of this in 1 query? I can do it but only with multiple queries and nothing comes up when I try to join all the queries together. All help will be appreciated.

 

[LarryDeLaruelle]

You would need to use a union query to combine the several queries into one dataset.

The question, however, is how you would present that data after doing the union.

If you intend or need to separate a potential report into each category, you could add a column to each query to identify it's type and then, in the report, use grouping to separate.

Alternatively, you could create separate subreports with each separate query as the record source and add them to a main report which would avoid the need to do a union query. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LittleSmudge]

The approach I would take would be this one:-

Your report seems to have just 5 numbers to present.

So, apart from the layout and formatting you just need five TextBox controls on the Report. Call these

TotalNewComplaints
TotalHashCompleted TotalUnsolved
TotalOpenComplaints
OldestUnsolved
AverageDays

( What's the "#" for ? )
( Do you mean a list of all complete complaints by date complete ? )
( Or the number not complete )
[ # in Access is a date delimitor. But that doesn't seem to fit here. What have I missed ? ]


Then, in the Report Detail's OnFormat event put some code that goes through an calculated each number.
Open a recordset on
"SELECT Count(TableIdField) As CountOfNewIn FROM TableName WHERE ReceivedDate BETWEEN #" & Format(SInputDate, "Medium Date&quot & "# AND #" & Format(EInputDate, "Medium Date&quot & "#"

TotalNewComplaints = rs!CountOfNewIn

rs.Close

re-open for Hashs Completed - I don't understand that one.
rs.Close

re-Open on
"SELECT Count(TableIdField) As CountOfIncomplete FROM TableName WHERE IsNull(CompleteDate)"

TotalOpenComplaints = rs!CountOfIncomplete
rs.Close

re-Open on
"SELECT Top 1 ReceivedDate FROM TableName WHERE IsNull(CompleteDate) ORDERBY ReceivedDate"

OldestUnsolved = rs!ReceivedDate

etc..


'ope-that-'elps.

G LS

 

[generaluser]

I just used the '#' to mean number. I just didn't write the word 'number' out completely. It wasn't pseudo code or anything.

Sorry

 

[LittleSmudge]

Try using "No" like the rest of the world ;-)


That makes a lot more sence now.

re-Open on
"SELECT Count(TableIdField) As CountOfComplete FROM TableName WHERE CompleteDate BETWEEN #" & Format(SInputDate, "Medium Date&quot & "# AND #" & Format(EInputDate, "Medium Date&quot & "#"

TotalNoCompleted = rs!CountOfComplete
rs.Close




There-ya-go.

G LS