Hope you can help me with this one.
John takes 4 credit card applications in a day. These are keyed into the database as below:
tblMain
ID --- SaleExec --- AppDate
100--- John ------- 01/01/2002
101--- John ------- 01/01/2002
102--- John ------- 01/01/2002
103--- John ------- 01/01/2002
John has a lot of errors on his applications so these have to be keyed into....
...tblAppErrors:
UniqueID field is the ID value from tblMain.
ErrorDescription field has a possible 30 values. Postcode, Surname, DOB are just 3. All these descriptions are kept in a seperate table called tblErrorCodes.
Missing/Illegible identifies if the ErrorDescription was missing or illegible.
ID --- UniqueID --- ErrorDescription --- Missing --- Illegible
1 ---- 100 -------- Postcode ----------- 1 --------- 0
2 ---- 100 -------- Surname ------------ 1 --------- 0
3 ---- 101 -------- Postcode ----------- 1 --------- 0
4 ---- 101 -------- DateOfBirth -------- 1 --------- 0
5 ---- 101 -------- Surname ------------ 0 --------- 1
6 ---- 102 -------- Postcode ----------- 1 --------- 0
7 ---- 102 -------- Surname ------------ 0 --------- 1
The above information tells me that the application that John took with the ID number of 100 had 1 missing postcode and 1 missing Surname. Application 101 had 1 missing Postcode, 1 missing DOB and 1 illegible Surname. Application 102 has 1 missing postcode adn 1 illegible Surname.
I am trying to build a query that will pull back this information and which I can then base on a report so that John can easily see where he has gone wrong...
Here are the queries I am trying to achieve:
Query 1:
Name --- Date --------- AppsTaken
John --- 01/01/2002 --- 4
The above query is simple and goes something like:
SELECT COUNT(M.id) AS Total, M.SalesExec, M.Appdate
FROM dbo.tblMain
WHERE (M.salesexec = 'John') AND (M.appdate = ' 2002 / 01 / 01')
GROUP BY m.SalesExec, M.appdate
I am having problems bulding this next query which is based on tblAppErrors. The result should look something like this:
Query 2:
ErrorDescription --- Missing -- Illegile
Postcode ----------- 3 --------- 0
Surname ------------ 1 --------- 2
DateOfBirth -------- 1 --------- 0
Once I have the above query figured out I need to somehow link Query 1 and Query 2 so that I can view them in an MS Access based report:
REPORT
============
Name --- Date ----------- AppsTaken
John ----- 01/01/2002 --4
ErrorDescription --- Missing --- Illegile
Postcode ----------- 3 ----------- 0
Surname ------------ 1 ---------- 3
DateOfBirth -------- 1 ----------- 0
I need help building Query 2 and then linking Query 1 and 2 for an Access based report.
I am open to all suggestions and if you think that there would be an easier way of doing this than above, please let me know.
John takes 4 credit card applications in a day. These are keyed into the database as below:
tblMain
ID --- SaleExec --- AppDate
100--- John ------- 01/01/2002
101--- John ------- 01/01/2002
102--- John ------- 01/01/2002
103--- John ------- 01/01/2002
John has a lot of errors on his applications so these have to be keyed into....
...tblAppErrors:
UniqueID field is the ID value from tblMain.
ErrorDescription field has a possible 30 values. Postcode, Surname, DOB are just 3. All these descriptions are kept in a seperate table called tblErrorCodes.
Missing/Illegible identifies if the ErrorDescription was missing or illegible.
ID --- UniqueID --- ErrorDescription --- Missing --- Illegible
1 ---- 100 -------- Postcode ----------- 1 --------- 0
2 ---- 100 -------- Surname ------------ 1 --------- 0
3 ---- 101 -------- Postcode ----------- 1 --------- 0
4 ---- 101 -------- DateOfBirth -------- 1 --------- 0
5 ---- 101 -------- Surname ------------ 0 --------- 1
6 ---- 102 -------- Postcode ----------- 1 --------- 0
7 ---- 102 -------- Surname ------------ 0 --------- 1
The above information tells me that the application that John took with the ID number of 100 had 1 missing postcode and 1 missing Surname. Application 101 had 1 missing Postcode, 1 missing DOB and 1 illegible Surname. Application 102 has 1 missing postcode adn 1 illegible Surname.
I am trying to build a query that will pull back this information and which I can then base on a report so that John can easily see where he has gone wrong...
Here are the queries I am trying to achieve:
Query 1:
Name --- Date --------- AppsTaken
John --- 01/01/2002 --- 4
The above query is simple and goes something like:
SELECT COUNT(M.id) AS Total, M.SalesExec, M.Appdate
FROM dbo.tblMain
WHERE (M.salesexec = 'John') AND (M.appdate = ' 2002 / 01 / 01')
GROUP BY m.SalesExec, M.appdate
I am having problems bulding this next query which is based on tblAppErrors. The result should look something like this:
Query 2:
ErrorDescription --- Missing -- Illegile
Postcode ----------- 3 --------- 0
Surname ------------ 1 --------- 2
DateOfBirth -------- 1 --------- 0
Once I have the above query figured out I need to somehow link Query 1 and Query 2 so that I can view them in an MS Access based report:
REPORT
============
Name --- Date ----------- AppsTaken
John ----- 01/01/2002 --4
ErrorDescription --- Missing --- Illegile
Postcode ----------- 3 ----------- 0
Surname ------------ 1 ---------- 3
DateOfBirth -------- 1 ----------- 0
I need help building Query 2 and then linking Query 1 and 2 for an Access based report.
I am open to all suggestions and if you think that there would be an easier way of doing this than above, please let me know.
