jurisdiction report - multiple counties per company

[TedErnst]

I'd like to have output like this:

Company1Name ContactInfo1 County1, County2, County3, County4
Company2Name ContactInfo2 County5, County6
Company3Name ContactInfo3 County7
etc

What I have so far looks more like this:
Company1Name ContactInfo1 County1
County2
County3
County4
Company2Name ContactInfo2 County5
County6
Company3Name ContactInfo3 County7
etc

Help?

 

[llkhoutx]

Create a separate table for counties with a foreign key linking it to that tables to the company table. You can create a relationship between the two tables if the county table also has a primary key, different from the foreign key to the company table.

tbCompanies
CompanyID
sCompanyName
etc

tbCounties
CountyID
FKCompanyID
sCounty

On a report/form displaying Company, create a subform of Counties with the Child & Master Link properties set to (Master) CompanyID, (Child) FKCompanyID. The Counties subreport/subform will then always tumble with a Company change.

 

[TedErnst]

Thank you for your response. I'm probably not explaining this well.

I believe I have the relationships correct, as my report does list the correct counties for eacy company. The issue is that the counties are each listed on their own line since they are each their own record. I'd like to save space and just have a comma delimited list of counties for each company.

Any way to do that? To get multiple records from the same field on the same line?

 

[cross5900]

I think I see what you want, and it would involve your query.

ExpressionName: [field]&", "&[field2]&", "&[field3] ... and so on.

Should output: Field, Field2, Field 3. This what you looking for?

 

[TedErnst]

ExpressionName: [field]&", "&[field2]&", "&[field3] ... and so on.

Should output: Field, Field2, Field 3. This what you looking for?

This is similar to what I want. Maybe it's more clear if I make up some data.

tblCompany
CompanyID strCompanyName
1 Bob's Company
2 Sarah's Company

tblCounty
CountyID strCountyName
1 Jefferson
2 Washington
3 Lincoln
4 Hancock
5 Nixon

tblAssignCounty
AssignCountyID CompanyID CountyID
1 1 1
2 1 2
3 1 3
4 1 4
5 2 4
5 2 5

This assigns Jefferson, Washington, Lincoln and Hancock to Bob and Hancock and Nixon to Sarah. This what I have so far. So when I make a report, I see:

Bob's Company
Jefferson
Washington
Lincoln
Hancock

Sarah's Company
Hancock
Nixon

What I want is:
Bob's Company: Jefferson, Washington, Lincoln, Hancock
Sarah's Company: Hancock, Nixon

Is this more clear?