join on two firelds

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables ... tblCashRcpt and tblEmployees. The field
emp.tblemployees is the primary key. In tblcashRcpt I have two fields
CollCode and CollCode2. The relationship between the two tables is one to
many on emp.tblemployees to CollCode.tblCashRcpt.

In frmCashRcpt I show both the CollCode and CollCode2. When CollCode is
entered it displays the name information from tblempoyees. How can I also
get it to display the name information for CollCode2.

I'm assuming I need to add a column in the qryCashRcpt (which the
frmCashRcpt uses) but I'm not sure how I tell it that the name I need is for
the CollCode2 employee not the CollCode employee.

I'm using Access 2007.
 
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP;


Thanks for any help you can give.
 
Try using this ---
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM (tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP) LEFT JOIN tblEmployees AS
tblEmployees_1 ON tblCashRcptCurDate.COLLCODE2 = tblEmployees_1.EMP;
--
KARL DEWEY
Build a little - Test a little


margaret said:
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP;


Thanks for any help you can give.


KARL DEWEY said:
Post the SQL for your query.
Click to expand...
Click to expand...
 
I changed the first line to read:

SELECT tblEmployees.[last] & ", " & tblEmployees.[first] AS name,
[tblEmployees_1].[last] & ", " & [tblEmployees_1].[first] AS name2,

and it worked. Thanks for your help.

KARL DEWEY said:
Try using this ---
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM (tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP) LEFT JOIN tblEmployees AS
tblEmployees_1 ON tblCashRcptCurDate.COLLCODE2 = tblEmployees_1.EMP;
--
KARL DEWEY
Build a little - Test a little


margaret said:
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP;


Thanks for any help you can give.


KARL DEWEY said:
Post the SQL for your query.
--
KARL DEWEY
Build a little - Test a little


:

I have two tables ... tblCashRcpt and tblEmployees. The field
emp.tblemployees is the primary key. In tblcashRcpt I have two fields
CollCode and CollCode2. The relationship between the two tables is one to
many on emp.tblemployees to CollCode.tblCashRcpt.

In frmCashRcpt I show both the CollCode and CollCode2. When CollCode is
entered it displays the name information from tblempoyees. How can I also
get it to display the name information for CollCode2.

I'm assuming I need to add a column in the qryCashRcpt (which the
frmCashRcpt uses) but I'm not sure how I tell it that the name I need is for
the CollCode2 employee not the CollCode employee.

I'm using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I did the join and forgot the select.
--
KARL DEWEY
Build a little - Test a little


margaret said:
I changed the first line to read:

SELECT tblEmployees.[last] & ", " & tblEmployees.[first] AS name,
[tblEmployees_1].[last] & ", " & [tblEmployees_1].[first] AS name2,

and it worked. Thanks for your help.

KARL DEWEY said:
Try using this ---
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM (tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP) LEFT JOIN tblEmployees AS
tblEmployees_1 ON tblCashRcptCurDate.COLLCODE2 = tblEmployees_1.EMP;
--
KARL DEWEY
Build a little - Test a little


margaret said:
SELECT ([last] & ", " & [first]) AS name, tblCashRcptCurDate.ID,
tblCashRcptCurDate.CURDATE, tblCashRcptCurDate.CURYEAR,
tblCashRcptCurDate.TIME, tblCashRcptCurDate.RECEIPT, tblCashRcptCurDate.BAG,
tblCashRcptCurDate.BOOTH, tblCashRcptCurDate.COLLCODE,
tblCashRcptCurDate.COLLCODE2, tblCashRcptCurDate.CASE,
tblCashRcptCurDate.SELLER, tblCashRcptCurDate.AMOUNT,
tblCashRcptCurDate.Closed
FROM tblCashRcptCurDate LEFT JOIN tblEmployees ON
tblCashRcptCurDate.COLLCODE = tblEmployees.EMP;


Thanks for any help you can give.


:

Post the SQL for your query.
--
KARL DEWEY
Build a little - Test a little


:

I have two tables ... tblCashRcpt and tblEmployees. The field
emp.tblemployees is the primary key. In tblcashRcpt I have two fields
CollCode and CollCode2. The relationship between the two tables is one to
many on emp.tblemployees to CollCode.tblCashRcpt.

In frmCashRcpt I show both the CollCode and CollCode2. When CollCode is
entered it displays the name information from tblempoyees. How can I also
get it to display the name information for CollCode2.

I'm assuming I need to add a column in the qryCashRcpt (which the
frmCashRcpt uses) but I'm not sure how I tell it that the name I need is for
the CollCode2 employee not the CollCode employee.

I'm using Access 2007.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top