Reporting Out Two Names w/Phone # from the Same Table
in [Reports]
|
Prev: Textbox - Wrap text onto second line for long fields
Next: Report with Monthly rows, Yearly columns & 3 pieces of data pe
From: RichKorea on 30 Jun 2008 17:42 I'm putting together a service report that will include two names with phone numbers for contacts at the customer's site. Service records are stored in a ServiceReport table, and contacts are stored in a separate table, which feeds the combo box on the input form, filtered by the customer name (there's a lot of repeat business and overlaps, so we want to hang on to the contacts, rather then re-entering the data every time). For creating a report, I'm going to need unique name and phone number fields for ContactA and ContactB. Originally, I had the ServiceReport table just reference the Contact_ID (autonumbered), but I modified that to have the contact fields in ServiceReport use a combo box tied to the first column (the ID), but displaying the second column (the name). That allows the report to display the two names, but doesn't let me get at the phone numbers. What's a good way to pull in the two names into the same report detail? Thanks Rich
From: bhicks11 via AccessMonster.com on 30 Jun 2008 20:51 Not sure I understand Rich but maybe it would help you to include the phone number in the combobox query, then you can pull it from the combobox. You may or may not have it visible. Reference the column in the combobox like this: Me.Lname = Me.ComboBoxID.Column(3) Bonnie http://www.dataplus-svc.com RichKorea wrote: >I'm putting together a service report that will include two names with phone >numbers for contacts at the customer's site. Service records are stored in a >ServiceReport table, and contacts are stored in a separate table, which feeds >the combo box on the input form, filtered by the customer name (there's a lot >of repeat business and overlaps, so we want to hang on to the contacts, >rather then re-entering the data every time). > >For creating a report, I'm going to need unique name and phone number fields >for ContactA and ContactB. Originally, I had the ServiceReport table just >reference the Contact_ID (autonumbered), but I modified that to have the >contact fields in ServiceReport use a combo box tied to the first column (the >ID), but displaying the second column (the name). That allows the report to >display the two names, but doesn't let me get at the phone numbers. > >What's a good way to pull in the two names into the same report detail? > >Thanks >Rich -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200806/1
From: RichKorea on 30 Jun 2008 21:16 Thanks for the reply Bonnie, The combobox on the form where the data's entered already pulls in multiple columns, I'm trying to get two references to the same Contact_Table to work on a report. Referencing two items from the ServiceReport table to different contact ID's wasn't working until I added a Contact_1 table and setup separate links, but since the field names were the same, I couldn't put those into the query. What I just tried was creating two subforms, one for each contact category, that queries the contact table based on the contact_ID. I then link the subforms to the contact_ID I get back from the main report's query, and this appears to work. "bhicks11 via AccessMonster.com" wrote: > Not sure I understand Rich but maybe it would help you to include the phone > number in the combobox query, then you can pull it from the combobox. You > may or may not have it visible. > > Reference the column in the combobox like this: > > Me.Lname = Me.ComboBoxID.Column(3) > > Bonnie > http://www.dataplus-svc.com > > RichKorea wrote: > >I'm putting together a service report that will include two names with phone > >numbers for contacts at the customer's site. Service records are stored in a > >ServiceReport table, and contacts are stored in a separate table, which feeds > >the combo box on the input form, filtered by the customer name (there's a lot > >of repeat business and overlaps, so we want to hang on to the contacts, > >rather then re-entering the data every time). > > > >For creating a report, I'm going to need unique name and phone number fields > >for ContactA and ContactB. Originally, I had the ServiceReport table just > >reference the Contact_ID (autonumbered), but I modified that to have the > >contact fields in ServiceReport use a combo box tied to the first column (the > >ID), but displaying the second column (the name). That allows the report to > >display the two names, but doesn't let me get at the phone numbers. > > > >What's a good way to pull in the two names into the same report detail? > > > >Thanks > >Rich > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200806/1 > >
From: bhicks11 via AccessMonster.com on 30 Jun 2008 21:33
Good going Rich! RichKorea wrote: >Thanks for the reply Bonnie, > >The combobox on the form where the data's entered already pulls in multiple >columns, I'm trying to get two references to the same Contact_Table to work >on a report. Referencing two items from the ServiceReport table to different >contact ID's wasn't working until I added a Contact_1 table and setup >separate links, but since the field names were the same, I couldn't put those >into the query. > >What I just tried was creating two subforms, one for each contact category, >that queries the contact table based on the contact_ID. I then link the >subforms to the contact_ID I get back from the main report's query, and this >appears to work. > >> Not sure I understand Rich but maybe it would help you to include the phone >> number in the combobox query, then you can pull it from the combobox. You >[quoted text clipped - 25 lines] >> >Thanks >> >Rich -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200807/1 |