Complicated querie

[morlan]

Complicated querie (for me anyway)

Hi folks,

I have been asked to run a querie on a huge chunk of information in order to get statistics and performance on our Sales executives.

I will try and describe and simplify what I am trying to do.

We employ sales people in various airports around the contry to try to get people to sign up for a credit card. If the customer is interested the sales exec will fill out an application form and then send it in to our data centre here where it is checked and then sent on to the bank.

Every month the bank send back a listed of the applications that were successful or were declined. This is in the form of a spreadsheet that is impoted to a table in the database


I will try and keep this as simple as poss.

I have one table called tblAppsTaken and another called tblBankReport. Here are examples of what they contain.

tblAppsTaken contents:

SalesExec | UniqueAppNumber
=======================
John Daly | 1045001
Mark Holeman | 1045002
Sarah Good | 1045003
John Daly | 1045004
John Daly | 1045005

tblBankReport

UniqueAppNumber | Status
=======================
1045001 | Rejected
1045002 | Accepted
1045003 | Rejected
1045004 | Accepted
1045005 | Accepted

You may have already guessed what I what I want to do next.

I need to run a report to see how many apps for John Daly were rejected or accepted and the same for all sales execs. This means we will be able to monitor performance of the employees each month.

Does anyone have any conceptual idea as to how I would glue these two tables together and start running queries?.

 

[Mile-O]

Looks just like a case of joining the two tables on the AppNumber and then setting the SalesPerson field to be a Count.

Then put their names in as the criteria.

 

[morlan]

Looks just like a case of joining the two tables on the AppNumber and then setting the SalesPerson field to be a Count.

Then put their names in as the criteria.

Cheers Mile, thats exacltley what I did.. now it gets more complicated. The two tables now look like this...

tblAppsTaken contents:

SalesExec | UniqueAppNumber | DateOfBirth
====================================
John Daly | 1045001 | 02/01/1950
Mark Holeman | 1045002 | 06/08/1955
Sarah Good | 1045003 | 23/12/1957
John Daly | 1045004 | 11/04/1959
John Daly | 1045005 | 03/07/1957

tblBankReport

UniqueAppNumber | Status
=======================
1045001 | Rejected
1045002 | Accepted
1045003 | Rejected
1045004 | Accepted
1045005 | Accepted

The new entries in bold are the birthdays of the applicants.
I need to filter the querie by age of the applicants. The ages range from 18 years to 70 years old.

I am using the criteria for the DateOfBirth field: Between #01/01/1950# And #30/04/1970# to filter records of people born between those dates.

This works fine and each record within these birthdates is listed individually in the querie's results.

Ideally I would like it to count the records returned instead of returning a long list. I have tried changing 'Group by' total field to 'Count' but this brings back no records.

Is there something I'm doing wrong here, is the expression criteria correct?

Any help much obliged

 

[raskew]

As a test, try copying/pasting this to a new query in Northwind. I've used your date criteria converted to US short-date format so you may have to turn the dates around:

SELECT DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([birthdate]),Day([birthdate]))) AS age, Count(DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([birthdate]),Day([birthdate])))) AS agec
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Employees.BirthDate) Between #1/1/1950# And #4/30/1970#))
GROUP BY DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([birthdate]),Day([birthdate])));

 

[morlan]

raskew, That doesnt seem to produce what Im after

Ill try and make this even more simple -

A simple table with two columns:

tblMain


Name | DateOfBirth
==================
John Daly | 02/01/1950
Mark Holeman | 06/08/1955
Sarah Good | 23/12/1957
Pat Redmonde | 11/04/1959
Nob Relling | 03/07/1957


A simple querie:

How many people are aged 30 - 40 years?

 

[raskew]

I just tried copy/pasting from my previous example and ran into some problems where the forum formatted the example differently than I'd intended. See if this works any better:

SELECT DateDiff("yyyy",[BirthDate],Date())+(Date()< DateSerial(Year(Date()),Month([birthdate]),Day([birthdate]))) AS age, Count(DateDiff("yyyy",[BirthDate],Date())+(Date()< DateSerial(Year(Date()),Month([birthdate]),Day([birthdate])))) AS agec
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Employees.BirthDate) Between #1/1/1950# And #4/30/1970#))
GROUP BY DateDiff("yyyy",[BirthDate],Date())+(Date()< DateSerial(Year(Date()),Month([birthdate]),Day([birthdate])));