Solved Creating query with different table fields (1 Viewer)

The Rev

Registered User.
Local time
Today, 15:05
Joined
Jan 15, 2003
Messages
118
I need a query that is based on 2 separate tables. I have a Requirements table and an Assignments table. We assign different groups of requirements to 5 different groups of auditors (1_3_5, 2_9_10_11_12, 4_7_8, 6_13_14, and On-Site). My Assignments table has 5 separate fields in them to assign a separate auditor to each of the fields. I need to figure out how to create a query that will list all of the requirements sequentially and who is assigned to that specific requirement.

Requirement Table:
Req_Sorting
Requirement_Number
Requirement_Description

Assignments Table:
ID(AutoNumber)
1_3_5_FL
2_9_10_11_12_FL
4_7_8_FL
6_13_14_FL
On_Site_FL


As stated, I assign individual names to each of the Functional Lead (FL) table using a combobox linked to a separate Staff table. I need a query that lists the Requirement Number and who is assigned to it. No clue where to start. There's nothing in common between the two. Thanks in advance!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:05
Joined
May 21, 2018
Messages
8,463
Are you willing to fix the table design.? We can help you. You can maybe make this work as is, but this structure does not make any sense and you will make it much harder in the long run?
What values do you put in the assignment fields? Is it a foreign key to a requirement? Can you post some real data from that table?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:05
Joined
May 21, 2018
Messages
8,463
Also are you assigning a specific auditor to a requirement or just the group?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 19, 2002
Messages
42,971
Naming fields using data is a sure sign of a design flaw. The Assignments table, needs an autonumberID, a FK to the requirements table and one FK to the auditor table. It will contain a single row for each assigned autidor. You will need code in the auditor subform to ensure that no more than 5 are assigned. You may need additional validation logic also.
 

The Rev

Registered User.
Local time
Today, 15:05
Joined
Jan 15, 2003
Messages
118
Also are you assigning a specific auditor to a requirement or just the group?
The auditor gets assigned to the group. Each group consists of several requirements. Requirements are sequential from 3.1 through 3.14. The first group is 3.1, 3.3, and 3.5. The other groups are listed above. There's more below that, but that's the meat of what I need.

There are dozens of moving parts to the DB that reworking the table will break other things. Hoping there is a way to just do this as-is.

Thanks for taking the time to look at it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:05
Joined
May 21, 2018
Messages
8,463
I would think seriously about fixing it, but this can be done without fixing it using a union query. I still do not understand what goes in those fields when you assign an auditor. Is it a name or an auditor ID?

I did misunderstand. I thought the Group was a group of auditors, but I think it is a group of requirements.

Do you have a requirments_group table ? Or could you make one?

tblRequirementsGroup
RequirementID_FK
RequirementGroupName

If I understand then that data looks like this.

3.1 1_3_5
3.3 1_3_5
3.7 4_7_8

Then you can do a union query on your assignment table and link to the above table.
Any chance you can post the tables in a small db? Just those tables.

Also if I understand this correctly the union query (often called a normalizing query) can be used to make a properly structured assignment table.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:05
Joined
May 21, 2018
Messages
8,463
So if I understand correctly you have something like this and you need to add the group name to the requirments table

tblRequrements tblRequrements

Requirement_NumberReq_SortingRequirement_DescriptionRequirment_Group
3.1
1​
Desc1_3_5
3.2
2​
Desc2_9_10_11_12
3.3
3​
Desc1_3_5
3.4
4​
Desc4_7_8
3.5
5​
Desc1_3_5
3.6
6​
Desc6_13_14
3.7
7​
Desc4_7_8
3.8
8​
Desc4_7_8
3.9
9​
Desc2_9_10_11_12
3.10
10​
Desc2_9_10_11_12
3.11
11​
Desc2_9_10_11_12
3.12
12​
Desc2_9_10_11_12
3.13
13​
Desc6_13_14
3.14
14​
Desc6_13_14
Then you have assignments which is just a name (could be an ID)
tblAssignments tblAssignments

AssignmentID1_3_5_FL2_9_10_11_12_FL4_7_8_FL6_13_14_FLOn_Site_FL
1​
John SmithMike BrownSheila BlackBetty WhiteEd Green
You need to normalize above
Code:
SELECT tblAssignments.[1_3_5_FL] AS FunctionalLead, "1_3_5" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[2_9_10_11_12_FL] AS FunctionalLead, "2_9_10_11_12" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[4_7_8_FL] AS FunctionalLead, "4_7_8" AS RequirementGroup_FK
FROM tblAssignments
UNION SELECT tblAssignments.[6_13_14_FL] AS FunctionalLead, "6_13_14" AS RequirementGroup_FK
FROM tblAssignments
ORDER BY 2;

qryNormalAssignments qryNormalAssignments

FunctionalLeadRequirementGroup_FK
John Smith1_3_5
Mike Brown2_9_10_11_12
Sheila Black4_7_8
Betty White6_13_14
Now you can join on Group Name
Code:
SELECT tblRequrements.Requirement_Number, qryNormalAssignments.FunctionalLead
FROM tblRequrements INNER JOIN qryNormalAssignments ON tblRequrements.Requirment_Group = qryNormalAssignments.RequirementGroup_FK
ORDER BY tblRequrements.Req_Sorting;

qryAssignments qryAssignments

Requirement_NumberFunctionalLead
3.1John Smith
3.2Mike Brown
3.3John Smith
3.4Sheila Black
3.5John Smith
3.6Betty White
3.7Sheila Black
3.8Sheila Black
3.9Mike Brown
3.10Mike Brown
3.11Mike Brown
3.12Mike Brown
3.13Betty White
3.14Betty White
You can then save a lot of work, by just restructuring the assignment table to be like the Assignment query. Further you can ensure relational integrity between assignments and requirement groups. With your current design you cannot.
 
Last edited:

The Rev

Registered User.
Local time
Today, 15:05
Joined
Jan 15, 2003
Messages
118
So if I understand correctly you have something like this and you need to add the group name to the requirments table

tblRequrements tblRequrements

Requirement_NumberReq_SortingRequirement_DescriptionRequirment_Group
3.1
1​
Desc1_3_5
3.2
2​
Desc2_9_10_11_12
3.3
3​
Desc1_3_5
3.4
4​
Desc4_7_8
3.5
5​
Desc1_3_5
3.6
6​
Desc6_13_14
3.7
7​
Desc4_7_8
3.8
8​
Desc4_7_8
3.9
9​
Desc2_9_10_11_12
3.10
10​
Desc2_9_10_11_12
3.11
11​
Desc2_9_10_11_12
3.12
12​
Desc2_9_10_11_12
3.13
13​
Desc6_13_14
3.14
14​
Desc6_13_14
Then you have assignments which is just a name (could be an ID)
tblAssignments tblAssignments

AssignmentID1_3_5_FL2_9_10_11_12_FL4_7_8_FL6_13_14_FLOn_Site_FL
1​
John SmithMike BrownSheila BlackBetty WhiteEd Green
You need to normalize above
Code:
SELECT tblAssignments.[1_3_5_FL] AS FunctionalLead, "1_3_5" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[2_9_10_11_12_FL] AS FunctionalLead, "2_9_10_11_12" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[4_7_8_FL] AS FunctionalLead, "4_7_8" AS RequirementGroup_FK
FROM tblAssignments
UNION SELECT tblAssignments.[6_13_14_FL] AS FunctionalLead, "6_13_14" AS RequirementGroup_FK
FROM tblAssignments
ORDER BY 2;

qryNormalAssignments qryNormalAssignments

FunctionalLeadRequirementGroup_FK
John Smith1_3_5
Mike Brown2_9_10_11_12
Sheila Black4_7_8
Betty White6_13_14
Now you can join on Group Name
Code:
SELECT tblRequrements.Requirement_Number, qryNormalAssignments.FunctionalLead
FROM tblRequrements INNER JOIN qryNormalAssignments ON tblRequrements.Requirment_Group = qryNormalAssignments.RequirementGroup_FK
ORDER BY tblRequrements.Req_Sorting;

qryAssignments qryAssignments

Requirement_NumberFunctionalLead
3.1John Smith
3.2Mike Brown
3.3John Smith
3.4Sheila Black
3.5John Smith
3.6Betty White
3.7Sheila Black
3.8Sheila Black
3.9Mike Brown
3.10Mike Brown
3.11Mike Brown
3.12Mike Brown
3.13Betty White
3.14Betty White
You can then save a lot of work, by just restructuring the assignment table to be like the Assignment query. Further you can ensure relational integrity between assignments and requirement groups. With your current design you cannot.
I really appreciate the tips. The assignments table was honestly just a trash table to hold an assortment of junk data for an in-brief slide show. But, like all good intentions, boss wants more and scope creep happens. Should have known better when I started this table. Sigh. I'll try this on Monday to see what's what. Have a great Sunday!!
 

The Rev

Registered User.
Local time
Today, 15:05
Joined
Jan 15, 2003
Messages
118
So if I understand correctly you have something like this and you need to add the group name to the requirments table

tblRequrements tblRequrements

Requirement_NumberReq_SortingRequirement_DescriptionRequirment_Group
3.1
1​
Desc1_3_5
3.2
2​
Desc2_9_10_11_12
3.3
3​
Desc1_3_5
3.4
4​
Desc4_7_8
3.5
5​
Desc1_3_5
3.6
6​
Desc6_13_14
3.7
7​
Desc4_7_8
3.8
8​
Desc4_7_8
3.9
9​
Desc2_9_10_11_12
3.10
10​
Desc2_9_10_11_12
3.11
11​
Desc2_9_10_11_12
3.12
12​
Desc2_9_10_11_12
3.13
13​
Desc6_13_14
3.14
14​
Desc6_13_14
Then you have assignments which is just a name (could be an ID)
tblAssignments tblAssignments

AssignmentID1_3_5_FL2_9_10_11_12_FL4_7_8_FL6_13_14_FLOn_Site_FL
1​
John SmithMike BrownSheila BlackBetty WhiteEd Green
You need to normalize above
Code:
SELECT tblAssignments.[1_3_5_FL] AS FunctionalLead, "1_3_5" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[2_9_10_11_12_FL] AS FunctionalLead, "2_9_10_11_12" AS RequirementGroup_FK
FROM tblAssignments
UNION
SELECT tblAssignments.[4_7_8_FL] AS FunctionalLead, "4_7_8" AS RequirementGroup_FK
FROM tblAssignments
UNION SELECT tblAssignments.[6_13_14_FL] AS FunctionalLead, "6_13_14" AS RequirementGroup_FK
FROM tblAssignments
ORDER BY 2;

qryNormalAssignments qryNormalAssignments

FunctionalLeadRequirementGroup_FK
John Smith1_3_5
Mike Brown2_9_10_11_12
Sheila Black4_7_8
Betty White6_13_14
Now you can join on Group Name
Code:
SELECT tblRequrements.Requirement_Number, qryNormalAssignments.FunctionalLead
FROM tblRequrements INNER JOIN qryNormalAssignments ON tblRequrements.Requirment_Group = qryNormalAssignments.RequirementGroup_FK
ORDER BY tblRequrements.Req_Sorting;

qryAssignments qryAssignments

Requirement_NumberFunctionalLead
3.1John Smith
3.2Mike Brown
3.3John Smith
3.4Sheila Black
3.5John Smith
3.6Betty White
3.7Sheila Black
3.8Sheila Black
3.9Mike Brown
3.10Mike Brown
3.11Mike Brown
3.12Mike Brown
3.13Betty White
3.14Betty White
You can then save a lot of work, by just restructuring the assignment table to be like the Assignment query. Further you can ensure relational integrity between assignments and requirement groups. With your current design you cannot.

This worked like a charm!! I'll use it for now and work the redesigned normalized table into the next version. Can't tell you how much I appreciate your help!!!
 

Users who are viewing this thread

Top Bottom