Query Help

[trevorg]

I have been asked to produce a report that will look at showing totals, percentage, average and oldest date on a table.

The purpose is to show how many records have been selected for an underwriter, so I need to group the underwriter and count the records where his/her name appears (No issue here), then work out how many policies are outstanding from a review date (PR Completed) over 30 days, then show a percentage of the records over the 30 days, then an average over the 30 days and finally the oldest record showing the date it was selected.

I am not sure where to begin let alone plan a solution.

I have attached a sample database (Access 2003) with a table and single query.

I would appreciate any assistance.

 

[PNGBill]

Is this part of a Student Project or you just need to solve the problem any way you can?

If just solve the problem, I would start with a query that collected much of the common data for the report and then depending on how many records the report is likely to have, would use a combination of other queries and or DLookup.

The base query will hold the Underwriter ID and 2nd query can get the oldest record, or the Average etc and just bring that query into the main query as if it was a table.

In the report itself, where 1 or two bits of data are required a DLookup can get this sometimes easier then trying to include same in the main query where you may have some conflict issues, eg Averages or Max.

A Report, like a Form, can only have One Data Source but you can have Functions to fill in data for Unbound Controls as a way around any restriction the one query gives you.

 

[Trevor G]

It isn't part of student project, but a work requirement.

I have had to build a new version of a database and include newer sections based on requirements from a team of managers and users. The underwriters have to review policies to check they conform to the organisations needs and of course FSA, it was decided that they would provide a number of reports on a period bases as well as adhoc and all reports are to go into Excel and split down to teams so they have a sheet each. The idea seems easier than the development work (but this is what they want).

All the data that I get is collected from 5 different systems and there is another person who has to put them into a single table, there are no id fields as once a week the main table is dumped of its content then go get the 5 systems. His role in this can't be touched due to other requirements, so I am left to produce a solution.

I will read through your suggestions and see how I can progress, but at this current time I am stuck.

 

[PNGBill]

Where is the data when you start your part of the task? In one table or more then one?
If One table and there is more then one field with ID as it's name then you may have a problem.

You say no issue to Grouping the underwriter and counting records - this sounds like a good place to start.
This gives you the Underwriter and the records. Most of the task is done.

Post your SQL to do the above.
Post some table names and field names of data you want to bring into the above sql.

Question regarding Student is that you may have had to do all of this in a certain way where as you can use many ways to perform the task.

 

[Trevor G]

I think I have this licked now, I have built up several queries to deal with different parts of the overall question then used a final query to bring them all together, so far as I have reopended this today all seems correct.

All data is coming from a single table and I am using the underwriter names and branch and team to identify them uniquely, there are no ID Fields

The SQL code behind the final query is indicated below:

SELECT qryUnderwriterSelectedPolicies.BranchAndTeam, qryUnderwriterSelectedPolicies.Uwr, qryUnderwriterSelectedPolicies.[No of policies selected], qryUnderwriterSelectedPoliciesOver30Days.[No of Policies Over 30 days old], [No of Policies Over 30 days old]/[No of policies selected] AS [Percent of polices over 30 days], qryUnderwriterAverageDays.[Average Number of Days], qryUnderwriterOldestDate.FirstOfSelectedDate
FROM ((qryUnderwriterSelectedPolicies LEFT JOIN qryUnderwriterSelectedPoliciesOver30Days ON qryUnderwriterSelectedPolicies.Uwr = qryUnderwriterSelectedPoliciesOver30Days.Uwr) LEFT JOIN qryUnderwriterAverageDays ON qryUnderwriterSelectedPolicies.Uwr = qryUnderwriterAverageDays.Uwr) LEFT JOIN qryUnderwriterOldestDate ON qryUnderwriterSelectedPolicies.Uwr = qryUnderwriterOldestDate.Uwr
ORDER BY qryUnderwriterSelectedPolicies.Uwr;

I still have one question and that is how can I show the oldest date held for the selected records based on the underwriter.

I know I can group and I know I can select First but that may not always give me the oldest date.

I am grateful for your participation and advice.

 

[PNGBill]

Sql Max and Min are two you should be using to find the earliest or latest record.
Using this on a date field should give you the result you want.

when you have your infinite number of queries you may get help from some sql experts on how to combine them to reduce the number but if it ain't broke then don't rush to fix it.

Another Tool to use is make your own Public Functions to get some of these individual results.

You then use the function in a query or just as the record source of an unbound text box control on your form or report as and when the specific data is required.
This has saved me a lot of messing around, once the functions were created.

Say a particular value is quite complicated in resolving, then a function allows you to mix sql and vba code together and once done, all you do is call the function.
eg =Sum() is a function. So you can have EarliestPolicy(), LatestPolicy() as a function.

 

[Trevor G]

Thank you once again,

I will be looking to fix my last area today (if I can) then I will test it with the code that will send it to Excel produce a separate worksheet per team, I have this already as I am doing other reports that doesn't seem so complex as this one.