|
Subject:
|
Double records in a report
|
|
Posted By:
|
timoma
|
Post Date:
|
11/12/2004 3:07:34 PM
|
Hi,
I have a database with tables called "clients" and "Addresses". The Addresses table contains two addresses for each client, home address and work address.
I want to produce a report with the client name and appropriate addresses. However, the data shows up with two lines for each client, understandably. How can I show the report with one line for each client?
i.e., with the fields: client name, home address, work address on one line.
Thanks so much for your help, in advance.
|
|
Reply By:
|
echovue
|
Reply Date:
|
11/12/2004 5:12:59 PM
|
I could probably give you more help, if I knew the structure of the two tables and what fields are used to relate them together. But what you will want to do is create a query that associates both addresses with a single user (I am assuming that there is a filed in the address table that indicates whether or not it is a work or home address, which you would use to differentiate in the query), and then base the report on that query, with text boxes for user name, home address and work address in the detail section. I would think that the Report wizard could do most of the work for you and you could just spend a few minutes tidying it up. Let me know if that doesn't work, or if you want to post the table structure and need more help.
Mike
EchoVue.com
|
|
Reply By:
|
timoma
|
Reply Date:
|
11/13/2004 1:05:36 PM
|
Mike,
Thanks for the post. The tables are connected by the client ID number, which is the primary key in the clients table. There are two fields in the addresses table that hold either "home address" or "work address", depending on which type of address it is.
When the query is run to isolate the client ID field in the addresses table it brings up a record for the client and his home address or work address twice.
Client ID Home Address Work Address
0000001 123 Any Street, NY
0000001 Any Street, NY
I want to see the result on one line.
Thanks again for your thoughts.
|
|
Reply By:
|
echovue
|
Reply Date:
|
11/13/2004 4:51:17 PM
|
So if I understand correctly... in your Client table, you have ID, Name and other information, and in the Address table, you have the Client ID, the Home address and the Work address. If you link each address to the client, then you will get duplicate records, but if you pull up the query window, link the Client ID from the address table to the ID on the Client Table, and then show Client Name, Home Address and Work Address, it should only give you one line for each.
If by chance the two fields that you refer to in the address table are check boxes, specifying whether home address is true, or work address is true, then in the design view for your report, use the Client table as your record source, and then for each address use a combo box. As the control source for the combo box, use the ID, and then set the record source to a SQL string like "SELECT address, clientID FROM address_table WHERE work_address=true" and set the BOund column to 2. This will display the address linked to the Client ID. If you would like, I could upload an example app to my site for you to download....
Good luck
Mike
EchoVue.com
|
|
Reply By:
|
timoma
|
Reply Date:
|
11/14/2004 4:25:11 PM
|
Thanks alot Mike. That looks very good. I will try it.
|
