One to many relationship

[abujafar]

I am unsure about the following situation.

In a database there is a client table and an application table for application forms. The Client May have more than one applications. There are two types of applications, app1 and app2 for arguments sake. The applications are the same except for a small subset of questions. Should I have one application table or should I create two additional tables tblApp1 and tblApp2 that relate to the main application table. If I only have the main application table, There will always be blank fields when I fill out one type of application to the exclusion of the other type of application. What is the correct approach to this type of problem.

One of my concerns is to produce a report that lists all the applications for a particular client because a client may have more than one application of different types.

Thanks in advance for any assistance.

 

[The_Doc_Man]

I am not sure that there is an easy answer. Though the subject matter might seem different, this appears to be the same technical problem being discussed in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=158468

 

[gemma-the-husky]

almost certainly, i would but both application types in the same table - you might need a flag to distinguish them, or you might not

i would probably be inclined to store nulls/blanks for those records where the fields arent appropriate, rather than have extra tables - in the long run, i feel it would probably be easier to handle - unless you need these fields to be required, or not allow zerolength for another reason , - then you would HAVE to go down the 1-1 table relationship for the extra fields

 

[abujafar]

Pat Hartman your solution seems to be applicable. I am going to try it in the next couple of hours and let you know.

My concern is to produce a report with all the applications for a particular client. An quick solution seems to be a main report with the application info and two sub reports one for each type of application. Is there a way to do this using queries only and base the report on the single query.