How to look up column names using a query

[debmil02]

Hi, if anyone can help it would be much appreciated. I do not know VBA/SQL so I'm sort of depending on basic queries. I have used SQL cut/paste in the past for some simpler items so I do know how to do that in queries but I'm not great at creating myself. I've set up a group of tables to manage a quality control project, below is an example of the table structure. I have a query combining Tables B, C and D no problem. However, for Table A the column names are the values in Table B and I don't know how to look them up. Any advice? I can also restructure the tables if needed.

Here is an example - in the real table structure we have thousands of jobs, with about 65 quality control items, about 25 impacts and about 5 impact groups.

Table A (this contains each job where job ID is the unique key)
Job ID
Item 1 (is a Y/N format)
Item 2 (is a Y/N format)
Item 3 (is a Y/N format)

Table B (this contains quality control items, where Item# is the unique key and would match Table A Columns...so Item 1 in Table A needs Item 1 Item Name from Table B, where the Item numbers increment until they reach the end of the Item numbers in Table B)
Item #
Item Name
Item Description
Item Impact #

Table C (this contains the impact of the quality control items, where Impact # is the unique key)
Item Impact #
Item Impact Description


Item Impact Group #

Table D (this contains the severity of the impact groups, where Impact Group # is the unique key)
Impact Group #
Impact Group Name
Impact Group Weight

[June7]

Multiple yes/no and/or similar named fields is indication of non-normalized data.

This doesn't make sense to me: 'where the Item numbers increment until they reach the end of the Item numbers in Table B'. Table A will have how many Item fields?

Provide sample of actual data in post, or attach the project, for analysis.

Advise to avoid use of spaces, special characters, punctuation (underscore is exception) in names, also reserved words. Better would be ItemNum or Item_Num.

[debmil02]

Table A has 65 Items (Item 1 Y/N, Item 2 Y/N etc through Item 65 Y/N). Table B has 65 Items as well. For each job in Table A, quality control experts examine the product and if defect 1 is found they select yes or no. They do that for each of the quality control items for each job. So in Table A is the results of the quality control exam by job for each item. Table B contains the item name with descriptions. Table C contains the groups of quality control items. Table D contains the severity weighting for each group.

Here is a better example of the structure - I can't attach the actual project because it contains confidential info. So I need to match in tblDefects Column 1 (through 25) to the corresponding DefectID in tblDefectDescription so that I can get to the point of calculating a defect total for each job.

tblDefects with 2000 records
JobID – contains 1 through 2000
1 - Y/N
2 - Y/N
3 - Y/N
4 - Y/N
5 - Y/N
6 - Y/N (and so on through 25)

tblDefectDescription with 25 rows
DefectID – contains 1 through 25
DefectName – text
DefectDescription – text
DefectImpact – lookup to tblImpact

tblImpact with 15 rows
ImpactID – contains 1 through 15
ImpactName – text
ImpactGroup – lookup to tblImpactGroup

tblImpactGroup with 4 rows
ImpactGroupID – contains 1 through 4
ImpactGroupName – text
ImpactGroupWeight - number

[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.

[debmil02]

As I mentioned, they are both examples. I tried to be more clear in the second example. So if I understand correctly, you are suggesting that a record would only be created when a defect exists rather than creating a record for each job defaulting to No? Of course in terms in tables that would be easy to do - the challenge I have at that point is creating a form for the end user who is collecting defects. Right now there is a form that reads from the defect table and the user simply checks the box if the defect occurs. I'm not sure how I would structure a data collection form to create the defect record.

I can create tables and queries, I do not know how to do Dlookups or VBA. If it's not possible to create a query this way, I think my other options would be a series of queries to manually join columns with the corresponding data or just export it into excel. I was able to do the lookups in Excel, I was just hoping there was an easier way in Access where the data is stored.

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.

[June7]

Examples need to be consistent with the actual situation. I am still not clear what that is. If the total Y/N columns in Table A is 50 or less, UNION query is possible. I have never tried to get around the 50-line limit by doing a UNION of other UNION queries, but maybe that is possible. The UNION for your data would be like:

SELECT JobID, "Item1" As ItemCat, Item1 As Assessment FROM TableA
UNION SELECT JobID, "Item2", Item2 FROM TableA
UNION SELECT JobID, "Item3", Item3 FROM TableA
...continue for up to 50 columns
UNION SELECT JobID, "Item50", Item50 FROM TableA;

In normalized junction table you could have a record for each defect type even if the defect is not present, just conventional practice would not. I guess the DefectSeverity would be 'None'. Yes, data entry would be different from just clicking a bunch of check boxes for a single record. It would be entering multiple records.

[debmil02]

Thank you for the info. At this time we have less than 50 but that will change over time, which is why they are in their own table and I was hoping to have a dynamic lookup. I think the quick way now is to export them to Excel since that has a VLookup function but I will be rethinking the design overall since it's problematic.