Help in making a query

[morlan]

I need help in designing a query.

Application forms are logged into my system. Some of the apps will have errors on them so these are logged into the tblApplicationErrors table.
Most common errors are missing postcode, illegible name etc

I have two tables that have a one to one relationship. The main table holds information about the app including date received, who took the app, where it was taken etc.

If the applications have any problems with them, these problems are logged into tblApplicationErrors.


I have attached the database so that you can further understand what I am trying to do.

There is a query and a report included also. The report show what I would ultimatley like the query to return.

Any suggestions appreciated!

 

[morlan]

Just adding DB in Access 97 format!

 

[morlan]

Please! Anyone?

 

[Pat Hartman]

You will need at least three queries (sales rep, totals, and details) and the form will need two subforms. One for totals and one for details. The main form will show the sales rep. I couldn't make sense of your table structures. They don't seem to have identifiers that you can use to join them to produce the report you want.

 

[AncientOne]

1) your main table has six fields for errors. What if most of the applications have only one error? lots of redundancy there..

2) There is not usually a good reason for having a one-to-one relationship between tables. I can see no reason for yours. What is your rationale? IMHO, you should have a one-to-many relationship between tblMain and tblErrors, thus eliminating the redundancy cited in 1).

 

[morlan]

Thanks for the replies
I'll just try and explain this a bit better:

Paper application forms are logged into my system.

The main table holds information about the app including date received, who took the app, where it was taken etc.

If the applications have any problems with them, these problems are logged into tblApplicationErrors .


Here is an example of the tables and some records


tblMain

ID SalesExec Venue Date
=============================
0001 John Doe London 01/01/2003
0002 John Doe London 01/01/2003
0003 John Doe London 01/01/2003
0004 John Doe London 01/01/2003
0005 John Doe London 01/01/2003


tblApplicationErrors


ID / Missing1 / Missing2 / Illegible1 / Illegible2 / Comments
=======================================================================================
0001 / Postcode / Name / ----- / This app has a coffee stain
0002 / Address / Name / ----- / -----
0003 / Postcode / Name / ---- / ----- / -----
0004 / Postcode / ----- / ----- / Date of birth / -----
0005 / Bank name / Postcode / Surname / ------- / This app has ciggy burns


Query
======

Based on the information in the above tables the query would bring back to following information:

John Doe, Date 01/01/2003, 5 received, 0 approved, 5 rejected,

1 x Badcondition, Missing postcode and name, this app has a coffee stain
1 x WrongInk, Missing address and illegible name
1 x Missing postcode and illegible name
1 x Missing postcode and illegible Date of birth
1 x NotDated, Missing Bank name and Illegible postcode and surname, This app has ciggy burns


Would you have any further suggestions?

 

[AncientOne]

Yes, but you are just repeating what we have already established.

The most efficient way of setting up your tables is to have a table to cover all errors that commonly occur and to join that table in a many-to-many relationship with your main table via a link table. Then you won't have hundreds of blank fields in any of your tables. The only way all error fields would be populated with your present set-up would be if there were multiple omissions and illegibilities in an application. That is (I hope) very rare.

If you took up my suggestion, you would improve performance and make queries easier without impairing your ability to produce the report in the format you favour.

 

[morlan]

Yes, but you are just repeating what we have already established.

The most efficient way of setting up your tables is to have a table to cover all errors that commonly occur and to join that table in a many-to-many relationship with your main table via a link table. Then you won't have hundreds of blank fields in any of your tables. The only way all error fields would be populated with your present set-up would be if there were multiple omissions and illegibilities in an application. That is (I hope) very rare.

If you took up my suggestion, you would improve performance and make queries easier without impairing your ability to produce the report in the format you favour.

Understood,

Could you further advise on a better table design?

I cant see any other way of storing the data

Thanks

 

[AncientOne]

I'll try and put something together for you by tomorrow.

 

[morlan]

I'll try and put something together for you by tomorrow.

I'll try and put something together for you by tomorrow.

Thanks mate. That's very kind.

For you reference, heres a screen grab of the form itself.

All data in the left pane is logged to tblMain and all data in the right pane is logged to tblApplicationErrors

There are five combo boxes which all contain the same values.
(assuming that there would never be more than 5 errors on the application)

Note: There is now a 'Salvaged' checkbox. For example, if there is an illegible postcode on the application, the user chooses postcode from the dropdown menu and then toggles 'Illegible'.
If the user consequentily resolves the postcode (by using a postcode search utility) they would then click 'Salvaged'.

The table needs to be designed in such a way that I could find out what illegible fields were salvaged and what missing fields were salvaged.

 

[morlan]

I'll try and put something together for you by tomorrow.

Did you come up with any ideas?