Complicated form

[secret]

Hi,

I have a database which contains records received through several forms like
1. Claim forms with Different sections


a. Section1:Project Information(requester name, team name, request date, need by date, request number etc)
b. Section2. Request Detail (product line, profile type, zone,region, state, sub type etc.)
Similarly,
2. Organization form with different section.
a. section1: Project information (requester name, ream name,request date etc)
b.section 2: request detail:(product line, profile type, zone,region, state, sub type etc.)
3. enterprise form with similar sections(above as in 1 and 2).

Now, I need to create a single master form with different section (like request num,type of request(want to have select option in form which will enable me to pick what type of form i want information from either form1, form2 or form3), start date, end date, status) which will enable me to track the information of all the three above form. If I enter the information in my master form then it should display me result of above three form like what type of form it is who is the requester of the form what is the status and so on. Can anyone suggest what will be the step to create this master form which will enable me to navigate all the above three form? Thank You..

[secret]

Hello,
I was wondering if anyone knows how to accomplish the following, any help would be greatly appreciated, thanks!
I have a database that contains 4 different types of forms(auto claim form, organization claim form, health claim form and Personal injury claim form), and each forms has many projects( project information, request detail, security level etc), also each project has many tasks (name of requester, start date of claim, end date of claim, address of requester, phone, zip etc).The tasks for each project are the same,(in general) to save time, and avoid duplicating my entries I am trying to create a master form that will show me a pre-determined list of ALL tasks, for each project so I can avoid having to type them over and over for each project. I am trying to create a list of all the tasks and have them appear on my form ready to be slightly manipulated to fit each project. And I want to have an option in my form to select one out of four form and get the information out of it and edit it.
Sincerely,
Raaz

[June7]

Three forms because three tables? Is this project data the same in all 3 tables? If so, data structure does not seem correct. Why duplicating project info? Describe your data structure.

[secret]

No, Project data are not same in the three table but some of them matches like requester name, team name, request date and end date etc but all the information are not same. So what would be my first step to accomplish this task any idea. And thank you for your reply

[June7]

Really need to understand data structure in order to advise on form arrangements. What is purpose of database? Describe tables or attach project to post. Not really understanding what you describe as 'master form' is supposed to do. Is this a menu (switchboard) or data entry form?

A single project will not have all types of data (claim, organization, enterprise)?

You can have a Tasks table. Have field in the other tables for taskID (foreign key). Select the taskID (primary key) from Tasks table. Use combobox to present a list of tasks to choose from. Whenever you need task details, join tables on the key fields.

[secret]

In fact a master form will have an option that will enable me to see all other four forms and thank you for suggesting me to use the combo box properties so will i have to create a task table with matching filed from other four table of the form and set the connection between them.
FYI....
I have a database that contains 4 different types of forms(auto claim form, organization claim form, health claim form and Personal injury claim form), and each forms has many projects( project information, request detail, security level etc), also each project has many tasks (name of requester, start date of claim, end date of claim, address of requester, phone, zip etc).The tasks for each project are the same,(in general) to save time, and avoid duplicating my entries I am trying to create a master form that will show me a pre-determined list of ALL tasks, for each project so I can avoid having to type them over and over for each project. Also i want to add a field which will allow me to know who from my team is working on that specific claim. I am trying to create a list of all the tasks and have them appear on my form ready to be slightly manipulated to fit each project. And I want to have an option in my form to select one out of four form and get the information out of it and edit it.
Sincerely,

[June7]

Still not understanding your setup. Helps to answer questions. Purpose of db is to track claims? What is a project - a single claim or a group of claims?

How many tasks? Either have a table as source for combobox RowSource or type the list in the RowSource property.

If I were building this, tables probably like:

tblClaims
ClaimID (primary key)
Requester
AssignedTo (Foreign key)
DateRequest
ClaimType (auto, health, organization, injury)
ClaimResolution

tblTasks
TaskID (primary key)
TaskName

tblClaimTasks
ProjectID (foreign key)
TaskID (foreign key)

tblEmployees
EmployeeID (primary key)
LastName
FirstName
Team

Only one form need to enter a claim of any type.

[secret]

Hi,
Thank You so much for your reply yeah the purpose of the database is to track the request of a claim which we receive through 4 different forms.
Let me elaborate little bit more now since we have 4 forms through which we receive the data we need to set up a database in such a way that we will be able to track all 4 different type of request that we receive through forms.
The database should be able to take any one of these forms and store the request. We also need to ensure to track additional details like which analyst is working on it, the status of the request and date it is completed. For further information below are the four forms which we have:
Form 1:
Project information:
Name of requester:
Request Date:...................
Need by Date:..........
Request Number:........
Request Type:.......... and several more columns
Request Detail
Product Type:................
Sub type............................
region ...................
zone.............
state ...............and several more columns
Similarly, Form 2 has several sections like
Form 2:
Project Information:
Requester name:...
Target environment:..........
Request Date:
Needed by Date
Request Detail:
Performer:
Authority Lime:
Authority type: and several more columns which differ from form1 for form 3 or form form4.
Likewise, there is two more form( Form 3 and Form4) with similar kind of section with some differences.

So now i want to built a master form for a database so that the database would take any of the above form(form1 or form2 or form3 or form4) and store the request. Also we need to ensure to track additional details like which analyst is working on it, the status of the request and date it is complected. So the master form will have some of these section too like :
name of analysyst working:
Status of the form:
Request date:

Hope this gives you better idea of what I am trying to do. Thank You....

[June7]

I still recommend a structure like what I described. Could include in tblClaims all fields for all claim types or normalize the fields that differentiate into child tables. How many fields would be different? Then I would build one form with a tab control. Tab 1 would have basic claim info that pertains to all claim types. Then other tabs with subform for each child table.

[tdecker81]

And build a separate table to list your users. Have them sign in with a start form before going to your master form with their ID and password if needed. Pass their ID to the master form. It can then know who's doing what in your database. Anytime you need to fill their name into a box or record it's available for you to pull directly from your master form.

On the master form you can also have a sort or filter by their name so they only see open cases that are assigned to them or that they opened.

[secret]

Thank you for your reply let me seek some more help from you guys because its really being helpful for me:
Currently we receive data through forms in email and store those data in an excel sheet and now we are designing an Access database(or one single form in access which will be a master form) to store the record we receive through all 3 different kind of form(ORG FORM,CLAIM FORM AND AAP FORM).
Now I have already splited the records of all form(ORG,CLAIM,AAP) into table and build forms for each table. So currently I have 10 tables and 10 forms But now I am having difficulty combining the table and setting up a relationship between them. Any suggestion. Let me describe what I have done so far.
Separated the detail from all the form into tables:
For Form ORG I have built 3 tables named as:
1. tbl_Org_Project_Information: with fields {ID (primary key), requester name, team name, request date, request type)
2. tbl_Org_Request_Detail:(ID(primary key),performer,product line,job title, zone,region,state,sub type)
3.tbl_Org_Authority_Limit: (ID(primary key),auto authority limit,fire authority limit,additional specification)
Similarly for form Claim I have built 2 table.
1.tbl_Claim_Project_Information(ID(PK),Requester name,requester type, request date, preetyp much same as tbl1 for Org form except some field differences)
2.tbl_Claim_Request_Detail:(ID(primary key),Product line,product type, zone, region, subtype....and few more fields)
And for AAP Request form I have build 4 tables
1.tbl_AAP_Project Info:(ID(primary key),requester name,target name,request date.......)
2.tbl_AAP_Request_Detail:(ID(primary key),prduct line,profile type,region,zone......and several more)
3.tbl_AAP_Fire_Profile:(ID(primary key),security level,addidional indicator1, additional indicator2,additional indicator3)
4.tble_AAP_Auto_Profile:(ID(primary key),indicator1, additional indicator1, additional indicator2)
Also I have built a master table(or a junction table) in fact this is the main table whose fields i want to have in my master form which I am trying to construct. So the files on this master tables are
tble_Master_table:(ID(primary key),requester name,team name,type of request,request date, end date,status, working analyst,status)
And I have created a simple form with form wizard for all these 10 tables so now I have 10 tables and 10 forms.
Can anyone suggest me how will I LINK these 10 table or this 10 form to construct a single master form which will allow me to store the data received form any 3 form either AAP or Claim or Org. How and i relate(relationship) a master table to other table or how will I create a sub form from these form to generate a linked form where i can store all my data received from 3 separate form into my access database.

Thank You,
Secret

4.tbl_AAP_Auto_Profile:(ID(primary key),

[June7]

You will continue to have problems with querying and reporting unless you get data properly structured. I don't think you can achieve 'a single master form' for data entry to all these tables with this structure.

However, might be able to make relationships work if you include a field in each of the ProjectInfo tables as a foreign key to the 'Master' table primary key. I have doubts because the 'Master' primary key will be a foreign key in 3 tables that should really be one table.

You have fields in 'Master' that duplicate the ProjectInfo tables (such as requestor name, type, date). Put in one, not both.

Your 'Master' table is actually getting close to my suggestion for a 'Claims' table.

The 'child' tables need a foreign key field for respective ProjectInfo table primary key.

[secret]

What field am I suppose to have in each of the ProjecctInfo table and and how will I create a relationship between tbl_master table, tbl_Project Info, tbl_Request detail for all the 4 form. Could you let me know what field should I have in each of the table and how would i relate them and later on how will I create a form and sub form to gather the data in a single place can I have a combo box set properties where I can have an option to pick (Either Org form or Claim form or AAP FORM)???

[tdecker81]

Create a single table called request with a field called request type. The table will have all of the fields required for each type. On the form hide or show the fields based on the selected type. You'll have a single table for all of your records a single details section and one entry form.

I'd be willing to take a look. PM me.

[June7]

You create relationships between tables with primary and foreign keys. Look at the schema I showed in earlier post. An autonumber field can serve as primary key then child tables have a number datatype field that will save this value as a foreign key.

tdecker81 has offered to assist you with setting up a proper data structure. If I read correctly suggestion is in line with what I proposed. We are trying to tell you that your structure is difficult to manage.