Query to show outstanding documents

[mulu]

Hi there

I am designing a database for a Visa and Passport company which will manage the companies applications for visas and passports, etc

I have a table for clients info, application info, documents required for the application, and docs received for the application

Now I need to create a query which sums up which docs are required for each application, and which have been received in order to establish which docs are still outstanding.

At the moment (not sure if correct way of doing it)
I have a lookup field in my applications table which I can tick / select multiple values from my DOCS REQUIRED table,

then also in my application table, I have another lookup field which I can tick / select values from my DOCS RECEIVED table

Please tell me if there is any other info which you require in order to assist me

Thank you so much

Mel

 

[HiTechCoach]

I would have a child table for the documents require. See the query in the attached database. Very easy to do now.

1) DO NOT use Look ups fields at the table level.

*** I removed them

2)You should avoid having duplicate tables

*** I delete on of the tables. By adding a Application Docs table it eliminated the need for two identical tables.

3) If you are going to define a relationships then it is best to also turn on Enforce Referential Integrity

See attached

 

[mulu]

Hi there HiTech

thank u so much for your help. however - how do I select an application type, and application status, if I am not to use lookup fields at table level?

Thank you so much again

 

[HiTechCoach]

Hi there HiTech

thank u so much for your help. however - how do I select an application type, and application status, if I am not to use lookup fields at table level?

Thank you so much again

Data entry normally is not done at the table level. In a relational database this just is not a good idea. Forms are for data entry. Forms have a lot of power that just does not exist in tables.

On a form you will use a comb box similar to what you were doing in the table.

I would use a sub form to show the data for the Application Docs on the Application form. You will create a new record in the Application Docs table for each required document.

Also see: Splitting your Access database into application and data