Table design ideas needed (warning!! very long post)

[morlan]

I need some advice in designing tables and queries for a credit card application logging system.

The initial table design was so bad that it made it really difficult for me to design the all important queries. So I have decided to start from scratch again and redesign the tables.

I will explain the whole process carefully from start to finish.


Our company acts as an agent for well know credit card companies and banks.
The company employs Sales Reps that are located in busy public areas around the country. Their job is to find prospective customers for credit cards. The Sales Rep will fill out a credit card application on behalf of the customer.

At the end of the day the Sales Rep will have on average about 20 application which are in turn sent to our 'data centre' to be logged and sent on to the banks etc.

Basic Sales rep information is logged:

- Who took the application (Sales rep)
- Where it was taken (London, Manchester etc.)
- Date app was taken by sales rep
- When the app will be dispatched (always the keyed date)

Also, basic information about the applicant themselves (this helps us track complaints):

- Surname
- Date of birth
- SEQ Number (the unique number printed on the application)


If an app is missing mandatory information (postcode, date of birth etc) the Sales rep will not get paid for the app. It is very important that we log this information as the Sales rep will want to know "Why haven't I been paid!!" Currently this information is written down on an A4 summary sheet and sent to the Sales rep at the end of the day.


Here is an example of the summary sheet:

===========================
Sales Exec: John Doe
Date apps taken: 01/01/2003
Venue: London

Apps received: 20
Apps rejected: 6

Missing info: 2
Illegible: 1
Other error: 3

Details:

1 with Invalid ID type checked
2 with missing postcode
1 with illegible date of birth

===========================


My aim is to create this summary sheet automatically by getting the data input user to log if there is any missing info from
the app.

Please take a look at the following screen grab of the application:

The left hand side of the app is what the data input user currently logs. This part works well and has a basic table underneath on which I run queries. I have now added a new section on the right hand side (criteria) to allow the user to log additional information. This works well but for the bad table design underneath and hence why I cant create the queries that will in turn create the summary sheet as seen above.

For now ignore the right side of the application and let me explain how the 'criteria' section works. It's relatively simple.

Firstly you will notice three check boxes: 'Approval criteria not met', 'Invalid ID type checked' & 'Late application'. If any have been checked it should show up on the summary sheet as above.

Next there are 5 dropdown boxes. Why 5 you ask? Well because there are never mre than 5 mistakes per application.
Each dropdown has the exact same list contents which can either be Missing or Illegible (this you would indicate by using the toggle buttons), Below is just some of the dropdown list contents.


Name to appear on card
Title
First name
Surname
Current address
Current address postcode
Landline number
Resident type check box
Period of current residence
Previous address
Period of previous residence

So, for example, if a postcode is missing you would choose 'Current address postcode' from the dropdown and then click the 'missing' toggle button. This would then add to the summary sheet as above.

Lastly the Comments box. If the data inputer has anything futher to add about this application they can insert it here.

God, this is the most painfully long post ever. Hope you understand me so far!

Note that if two applications are logged with 'missing postcode' then it should tally in the summary sheet as you can see above. Same goes for all other missing/illegible info. Although I'm sure you've gathered that already.

Right, now, Suggestions Suggestions Suggestions! I really need help on this one as I totally messed it up the last time.
I await you replies.

Thanks for reading

 

[rockman]

You are correct that the "5" separate error fields is a poor design. I think it would be even poor design if there could only be 2 possible errors! Hence, this is how I would tackle it:

tblApplications
fldApplicationID (autonumber)
fldCriteriaNotMet
fldInvalidID
fldLateApp

tblErrorTypes
fldErrorTypeID (autonumber)
fldDescription

tblApplicationErrors
fldDummyID (autonumber)
fldApplicationID
fldErrorTypeID
fldMissing
fldIllegible

On your form, delete the comboboxes and option buttons on the right side of the screen. Replace them with a subform that points to a 1 line form with a combobox, and 2 option buttons (set in continuous form mode).

This will allow you to query the tblApplicationErrors to sum up your entries more easily. Plus it gives you the flexibility to enter as many errors as you wish (in the event that some day more than 5 errors could be made).

Hope this helps,
Jeff