Originally Posted by
June7
25/15/4 or 65/25/5 - which is it?
You want a query that will match the Table A Y/N response field with the defect info record in TableB?
Table A is not normalized so there is no relationship between Tables A and B
Normalization could require another table to 'junction' Tables A and B. Table A would have basic info about the project. Table B has info about defect types. Junction table would contain records for identified defects only - no defect, no record:
JobID (fk of Table A pk)
DefectItemNum (fk of Table B pk)
DefectSeverity
Then Tables A and B can be joined to the junction table on respective pk/fk fields so that all related data is available.
It is a balancing act between normalization and ease of data entry/output.
With current Table A, possibilities are:
1. DLookup expressions in a query to find the defect info.
2. VBA custom function called from query to return the defect info. Might run faster than No. 1.
3. UNION query to reorganize Table A data to a normalized structure. Use this query in joins to other tables/queries. There is no wizard or designer for UNION, must type in the SQL View window of query designer. Limit of 50 lines in a UNION.
Do not care about real data. See the instructions for attaching project at bottom of my post.