Show 2 Contacts From Same Table in Same Row

[Hillary]

I have a student record an I want to pull Contact 1 and Contact 2 for the student in the same row. The Contact Sequence sits on a different table then the Contact Name.

This is what I want to see,
[tt] StuLast StuFirst Contact1 Contact2[/tt]

But this is what I am seeing
[tt] StuLast StuFirst Contact1
StuLast StuFirst Contact2[/tt]

My formula for pulling the Contact is ParentLast: IIf([SIS_SLOT]=1,[dbo_CSL_CNT_CONTACT.LAST_NAME]) and ParentLast: IIf([SIS_SLOT]=2,[dbo_CSL_CNT_CONTACT.LAST_NAME])
where [SIS_SLOT] is the Sequence Number.

Thanks!


Hillary

 

[mordja]

Hillary,

Can you describe the table setup a little clearer. In your contacts table does each student have only two rows, [SIS_SLOT]=1 and [SIS_SLOT]=2.

If this is the case you could use a dlookup function to retrieve contact1 and contact2 on the same row.

Mordja

 

[Trendsetter]

Is this a report that you want to run or is it to appear on a form??
I am assuming from what you say that there is a Student table with a linked table of contacts, is this correct??



Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Hillary]

All links are type 2.

tblDistrict Student to tblSchool Student
tblSchool Student to tblContact Student Sis Map (tblStudent Sis Map is the table with the sequence - Mordja each student has 6 rows in this table; [SIS_SLOT] = 1 through [SIS_SLOT] = 6)
tblContact Student Sis Map to tblStudent Contact
tblStudent Contact to tblContact (Contact has the contact info such as first name, last name - tblStudent Contact has to be used as a link)

Let me know if you need more info.

Thanks again!

Hillary

 

[PHV]

I think you have to join the Sis table 2 times, one restricted to slot 1 and the other restricted to slot 2.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Use a crosstab query to put the contacts on one record per student. Use the SIS_SLOT value as the "Column Heading".


John

http://www.interautomateconsulting.com

 

[dhookom]

In addition to the accurate responses of others, you can concatenate the child records using a function as demonstrated in the sample at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane
MS Access MVP
Find out how to get great answers faq219-2884.