join on two firelds

M

margaret

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

margaret

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

KARL DEWEY

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

margaret

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

KARL DEWEY

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

duplicates 3
Append Crosstab Query to Existing Table 0
How to have two tables on same line? 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Duplicating Data 8
Complex query help 1
Unable to display values in 2nd list box from 1st list box selection 2
Help filling in form from 2 different tables 3

Top