Using one table to populate multiple columns in a query

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

My problems began because I used the look-up feature in my principle table of
my database. I am trying to rectify this problem and I have run into a snag.
I am using one table (tblPersonnel) to populate 5 different fields in my
query. When I open the datasheet view the fields are not populated with any
data from tblPersonnel. I have included the SQL view to help shed light on
the problem.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(1)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(2)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(3)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(4)],
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));
 
Frank said:
My problems began because I used the look-up feature in my principle table of
my database. I am trying to rectify this problem and I have run into a snag.
I am using one table (tblPersonnel) to populate 5 different fields in my
query. When I open the datasheet view the fields are not populated with any
data from tblPersonnel. I have included the SQL view to help shed light on
the problem.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(1)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(2)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(3)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(4)],
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));


Without knowing your objective, I suspect that all those
ANDs should be OR\
 
Marsh:

My objective is to populate five different fields in a query. The fields
all come from the table "tblIncoming". The fields are called
"chrPrimaryAction", "chrSupportAction(1)", "chrSupportAction(2)",
"chrSupportAction(3)" and "chrSupportAction(4)". These fields are populated
from the table "tblPersonnel" field "idsID".

In my query, I want to display the corresponding
"chrStaffReferenceIndicator" from table "tblPersonnel" using the
"tblPersonnel.idsID".

I changed the ANDs to ORs as you suggested. This did populate the Columns,
but all five columns have the same "chrStaffReferenceIndicator" , this should
not be the case. I am posting the revised SQL view for your review.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction1,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction2,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction3,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction4,
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));


--
Regards,
Frank


Marshall Barton said:
Frank said:
My problems began because I used the look-up feature in my principle table of
my database. I am trying to rectify this problem and I have run into a snag.
I am using one table (tblPersonnel) to populate 5 different fields in my
query. When I open the datasheet view the fields are not populated with any
data from tblPersonnel. I have included the SQL view to help shed light on
the problem.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(1)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(2)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(3)],
tblPersonnel.chrStaffReferenceIndicator AS [SupportAction(4)],
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) AND (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));


Without knowing your objective, I suspect that all those
ANDs should be OR\
 
Frank said:
My objective is to populate five different fields in a query. The fields
all come from the table "tblIncoming". The fields are called
"chrPrimaryAction", "chrSupportAction(1)", "chrSupportAction(2)",
"chrSupportAction(3)" and "chrSupportAction(4)". These fields are populated
from the table "tblPersonnel" field "idsID".

In my query, I want to display the corresponding
"chrStaffReferenceIndicator" from table "tblPersonnel" using the
"tblPersonnel.idsID".

I changed the ANDs to ORs as you suggested. This did populate the Columns,
but all five columns have the same "chrStaffReferenceIndicator" , this should
not be the case. I am posting the revised SQL view for your review.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction1,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction2,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction3,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction4,
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));


It gets pretty messy when you have a table that is more of a
spreadsheet than a set of normalized relational database
tables. One of the rules of normalization is that you not
have "repeating" fields in a table. Instead you should have
a separate table with one action value per row.

You are going to run into many more problems with your
current table design so you really should take time out and
redesign your table(s?). If this remedy is impossible, then
your current problem can be dealt with by adding
tblPersonnel 4 more times to the query and join each action
to a separate instance of the table. This gets rather
confusing, but I think the query should be something like:

SELECT tblIncoming.dtmDateReceived,
tblCompanies.chrCompanyName, tblIncoming.[chrDocRef#],
tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel1.chrStaffReferenceIndicator AS SupportAction1,
tblPersonnel2.chrStaffReferenceIndicator AS SupportAction2,
tblPersonnel3.chrStaffReferenceIndicator AS SupportAction3,
tblPersonnel4.chrStaffReferenceIndicator AS SupportAction4,
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS,
tblIncoming.memComments
FROM ((((((tblIncoming
LEFT JOIN tblCompanies
ON tblIncoming.chrCompany = tblCompanies.idsID)
LEFT JOIN tblPersonnel
ON tblIncoming.chrPrimaryAction = tblPersonnel.idsID)
LEFT JOIN tblPersonnel As tblPersonnel1
ON tblIncoming.[chrSupportAction(1)] =
tblPersonnel1.idsID)
LEFT JOIN tblPersonnel As tblPersonnel2
ON tblIncoming.[chrSupportAction(2)] =
tblPersonnel2.idsID)
LEFT JOIN tblPersonnel As tblPersonnel3
ON tblIncoming.[chrSupportAction(3)] =
tblPersonnel3.idsID)
LEFT JOIN tblPersonnel As tblPersonnel4
ON tblIncoming.[chrSupportAction(4)] =
tblPersonnel4.idsID)
LEFT JOIN tblOutgoing
ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE tblOutgoing.chrSTATUS Is Null
 
Marsh:

Your fix worked perfectly.

I am making these changes on a copy of the database. I have to be honest
with you, I didn't understand your comment regarding my database. "The table
that is more of a spreadsheet than a set of normalized relational database
tables. One of the rules of normalization is that you not have repeating
fields in a table Instead you should have a separate table with one action
value per row."

I want to do this correctly, so I will have to do some research to try and
understand your comments as they apply to my database.

Thank you very much for your assistance, I really appreciate your time and
effort.

--
Regards,
Frank


Marshall Barton said:
Frank said:
My objective is to populate five different fields in a query. The fields
all come from the table "tblIncoming". The fields are called
"chrPrimaryAction", "chrSupportAction(1)", "chrSupportAction(2)",
"chrSupportAction(3)" and "chrSupportAction(4)". These fields are populated
from the table "tblPersonnel" field "idsID".

In my query, I want to display the corresponding
"chrStaffReferenceIndicator" from table "tblPersonnel" using the
"tblPersonnel.idsID".

I changed the ANDs to ORs as you suggested. This did populate the Columns,
but all five columns have the same "chrStaffReferenceIndicator" , this should
not be the case. I am posting the revised SQL view for your review.

SELECT tblIncoming.dtmDateReceived, tblCompanies.chrCompanyName,
tblIncoming.[chrDocRef#], tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction1,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction2,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction3,
tblPersonnel.chrStaffReferenceIndicator AS SupportAction4,
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS, tblIncoming.memComments
FROM ((tblIncoming LEFT JOIN tblCompanies ON tblIncoming.chrCompany =
tblCompanies.idsID) LEFT JOIN tblPersonnel ON (tblIncoming.chrPrimaryAction =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(1)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(2)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(3)] =
tblPersonnel.idsID) OR (tblIncoming.[chrSupportAction(4)] =
tblPersonnel.idsID)) LEFT JOIN tblOutgoing ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE (((tblOutgoing.chrSTATUS) Is Null));


It gets pretty messy when you have a table that is more of a
spreadsheet than a set of normalized relational database
tables. One of the rules of normalization is that you not
have "repeating" fields in a table. Instead you should have
a separate table with one action value per row.

You are going to run into many more problems with your
current table design so you really should take time out and
redesign your table(s?). If this remedy is impossible, then
your current problem can be dealt with by adding
tblPersonnel 4 more times to the query and join each action
to a separate instance of the table. This gets rather
confusing, but I think the query should be something like:

SELECT tblIncoming.dtmDateReceived,
tblCompanies.chrCompanyName, tblIncoming.[chrDocRef#],
tblIncoming.memSubject,
tblPersonnel.chrStaffReferenceIndicator AS PrimaryAction,
tblPersonnel1.chrStaffReferenceIndicator AS SupportAction1,
tblPersonnel2.chrStaffReferenceIndicator AS SupportAction2,
tblPersonnel3.chrStaffReferenceIndicator AS SupportAction3,
tblPersonnel4.chrStaffReferenceIndicator AS SupportAction4,
tblIncoming.chrFollowUp, tblOutgoing.chrSTATUS,
tblIncoming.memComments
FROM ((((((tblIncoming
LEFT JOIN tblCompanies
ON tblIncoming.chrCompany = tblCompanies.idsID)
LEFT JOIN tblPersonnel
ON tblIncoming.chrPrimaryAction = tblPersonnel.idsID)
LEFT JOIN tblPersonnel As tblPersonnel1
ON tblIncoming.[chrSupportAction(1)] =
tblPersonnel1.idsID)
LEFT JOIN tblPersonnel As tblPersonnel2
ON tblIncoming.[chrSupportAction(2)] =
tblPersonnel2.idsID)
LEFT JOIN tblPersonnel As tblPersonnel3
ON tblIncoming.[chrSupportAction(3)] =
tblPersonnel3.idsID)
LEFT JOIN tblPersonnel As tblPersonnel4
ON tblIncoming.[chrSupportAction(4)] =
tblPersonnel4.idsID)
LEFT JOIN tblOutgoing
ON tblIncoming.[chrDocRef#] =
tblOutgoing.[chrResponcetoDocRef#]
WHERE tblOutgoing.chrSTATUS Is Null
 
Frank said:
Marsh:

Your fix worked perfectly.

I am making these changes on a copy of the database. I have to be honest
with you, I didn't understand your comment regarding my database. "The table
that is more of a spreadsheet than a set of normalized relational database
tables. One of the rules of normalization is that you not have repeating
fields in a table Instead you should have a separate table with one action
value per row."

I want to do this correctly, so I will have to do some research to try and
understand your comments as they apply to my database.


You can get a quick overview of Notmalization at:
http://support.microsoft.com/kb/283878/en-us

And you will get a ton of other articles from Google. Some
of them will get pretty esoteric, so, for your current
purposes, you only need to learn the first three Normal
Forms.
 
Back
Top