linking multikple columns to one column in a differ table

[Guest]

I have a form where 9 different names (there are over 150 names) are entered
into 1 record. Each name would have a date and a memo box associtated with
it. Each name is associated with a different team (there are over 20
teams). I would like to have a query where you would be able to pick a team
and have only the information contain to that team appear. The end result I
am looking for is a report that looks like this:

Team
Name, Date, memo
Name, Date, memo
so and so on

I am a newbie so I need detailed instructions.
Thanks in advance

 

[Guest]

You might need to show us how some of the data looks; however, if you have 9
names in one record, either all in one field or 9 different fields, you'll
have big problems getting a query to work.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Guest]

They are all in different fields. I have already developed forms, reports
and queries on this table that are working fine. This database is also in
production with another form. Here is what my table looks like:

Policy Number| Today's Date| Person Auditing Policy| Mail/Scan Pass|
Mail/Scan Fail| Mail/Scan Person| Mail/Scan Date| Mail/Scan Fail Reason|ARC
Pass|ARC Fail| ARC Person|ARC Date |ARC Fail Reason|Data Entry Pass|Data
Entry Fail|
Data Entry Person|Data Entry Date|Data Entry Fail Reason|Underwriting Pass|
Underwriting Fail|Underwriting Person|Underwriting Date|Underwriting Fail
Reason Case Coordinator Pass|Case Coordinator Fail|Case Coordinator Person|
Case Coordinator Date|Case Coordinator Fail Reason|Requirements Management
Pass|Requirements Management Fail|Requirements Management Person|
Requirements Management Date|Requreiments Management Fail Reason|Final Act
Pass|Final Act Fail|Final Act Person|Final Act Date|Final Act Fail
Reason|Assembly Pass|Assembly Fail|Assembly Person|Assembly Date|Assembly
Fail Reason|Producer Care Center Pass|Producer Care Center Fail|Producer Care
Center Person|Producer Care Center Date|Producer Care Center Fail Reason
They are only allowed to enter a person, date and reason if they click fail
Any help would be appreciated
Thanks

 

[Guest]

You need three tables - the first list all your policy number as the
particulars of the policy like Holder, Beneficiary, Value, Terms, etc.

Next you need and Action table listing all function performed by the audits.
Action ---
ActionID - Autonumber - Primary Key
Action - Text

In that table you have the list
Action --- List in the table
1 - Mail/Scan
2 - ARC
3 - Data Entry
4 - Underwriting
5 - Case Coordinator
6 - Requirements Management
7 - Final Act
8 - Assembly
9 - Producer Care Center

Then the audit action records. Your data entry form/subform
(Policy/Action) will have a list box to select the action when you add a new
record.
Audits ---
AuditID - Autonumber - Primary Key
Policy Number
Today's Date
Person Auditing Policy
Action
Pass/Fail - Yes/No field with Pass/Fail as labels
Person
Action Date
Fail Reason