bottlenecks during Database realization

[sansui]

I am working in Sansui TV servicing center and would like to submit monthly report.
In MS Access 2007, I have created the following tables with the field descriptions as below,
Table1-ProductEnrolment


Field1- TV_SlNo(Primary Key, Number)
Field2- TV_Model (Text)
Field3- TV_Name (Text)
Field4- Model_ID (Number)
Table2-FailureReport
Field1- RID (Primary Key, AutoNumber)
Field2- TV_SlNo (lookup on Field1 of Table1)
Field3- Failure_reporter (text)
Field4- Reported_date (date)
Field5- Reported_failure (memo)
Field6- Failure_history (lookup on Field1 of Table1 with multiple values)
Table3-FailureAnalysis
Field1- AID (Primary Key, AutoNumber)
Field2- TV_SlNo (lookup on Field2 of Table2 without duplicate values and not analyzed status)
Field3- RID (lookup on external query or table that will have filtered RIDs from Table2 based on Field2 of Table3)
Field4- Analyzed_by (text)
Field5- RootCause (memo)
Field6- Analysis_done_on (date)
Table4-FailureClosure
Field1- CID (Primary Key, Auto Number)
Field2- TV_SlNo (lookup on Field2 of Table3 without duplicate values and OPEN closure status)
Field3- AID (lookup on external query or table that will have filtered AIDs from Table3 based on Field2 of Table4)
Field4- Corrective_action_taken (memo)
Field5- Closure_status (lookup values OPEN or CLOSED)
Field6- Closure_date (date)

Bottlenecks during Table realization

1.Field2 of Table3—I want to lookup TVs for which failure has been reported and analysis is not done. I also want to exclude duplicate entries in the drop list in cases where two dissimilar failures (two RIDs) reported for the same TV.
2. Field3 of Table3--- I want to lookup on external query or table that will have filtered RIDs from Table2 based on Field2 of Table3
3.Field2 of Table4—I want to lookup TVs for which analysis has been completed and closure status is OPEN. I also want to exclude duplicate entries in the drop list in cases where two dissimilar failures (two RIDs) reported for the same TV and thus two AIDs for the same TV.
4.Field3 of Table4--- I want to lookup on external query or table that will have filtered AIDs from Table3 based on Field2 of Table4
Data Entry FORMS
Form1-ProductEnrolment
Form2- FailureReport
Form3- FailureAnalysis
Form4- FailureClosure
REPORT
Service record
Starting date_____ & Ending date ________

[alansidman]

First off. You should never be using lookups in tables. They should all be done in forms. Look at this link.

http://access.mvps.org/access/lookupfields.htm

Now once you have your lookups in forms and have built your combo boxes, in your SQL statement for the lookup, click on the Sigma and set the field you want to have unique values set to Group By.

If you are unfamiliar with combo boxes, then look at the tutorials on combo boxes on this web page.

http://www.datapigtechnologies.com/AccessMain.htm scroll down to the third section
.
Once you have set up your forms and got your basic lookups in place, you will then create queries to filter your data.

On the same link are several tutorials on queries. Post back with your issues.