Join rows into one long row

[jhicsupt]

I have a table that has many rows of data, i.e.

Cust No EEName EENo Date Worked Comments
X123 John Doe 13333 5/14/09 Painted house
X123 Joe Smith 12XA1 4/10/09 Washed car
A345 John Doe 13333 5/15/09 Planted flowers
A345 Joe Smith 12XA1 5/15/09 Washed car

I would like to combine these into one long row by customer (putting a "/"
in between) as follows:

John Doe 13333 5/14/09 Painted house / Joe Smith 12XA1 4/10/09 Washed car

John Doe 13333 5/15/09 Planted flowers / A345 Joe Smith 12XA1 5/15/09 Washed
car

The common field that would tie all of these resources together would be
Customer ID.

Is there a way to do this?

Thanks in advance.

 

[John W. Vinson]

I have a table that has many rows of data, i.e.

Cust No EEName EENo Date Worked Comments
X123 John Doe 13333 5/14/09 Painted house
X123 Joe Smith 12XA1 4/10/09 Washed car
A345 John Doe 13333 5/15/09 Planted flowers
A345 Joe Smith 12XA1 5/15/09 Washed car

I would like to combine these into one long row by customer (putting a "/"
in between) as follows:

John Doe 13333 5/14/09 Painted house / Joe Smith 12XA1 4/10/09 Washed car

John Doe 13333 5/15/09 Planted flowers / A345 Joe Smith 12XA1 5/15/09 Washed
car

The common field that would tie all of these resources together would be
Customer ID.

Is there a way to do this?

Thanks in advance.

You'll need some VBA code. You can adapt the code at
http://www.mvps.org/modules/mdl0004.htm
This uses a comma as the delimiter but you can change it to " / " easily
enough.

 

[Larry Daugherty]

Yes and you most definitely *Don't* want to do that in Access. That's
commmitting *spreadsheet* on a RDBMS.. You may be trying to do your
data management from tables. That, too, is not to be done in Access.

Your tables should be designed first. Each table will hold all of the
instances in your application of *entities* of a given type. For
example; tblPerson might hold information about each person.

A lot of intelligence is captured in your application by how the
tables are *related* to each other.

Once your tables are designed, you can design Forms on them. All
versions of Access provide an AutoForm icon that is visible in
Database|table view. Clicking that icon will invoke the Wizard that
will step you through the process of designing your form. You can
delete the result and start over as many times as you'd like until you
get something you like well enough to start. You can stay in Design
mode and tweak your new Form's design. You can open any form in
design mode any time you like.

Since the data in your table is initially stored in entry order,
you'll want a query to sort the results in a particular order. It is
more common to base the form on a query than directly on a table.

Beyond Forms and Queries there are Reports. The Report Designer has
lots of powerful goodies and that's where I think you'll want to be
addressing the presentation of your data. For example, you could
group by customer and then list every service event for that customer
in the order you choose on your report.

I may have guessed wrong as to your desired outcome ...

FYI there are specific newsgroups for various parts of the Access
universe.

HTH

 

[BruceM]

John,

The link didn't work, so I investigated a bit, and found this link, which
seems to be the one you intended:
http://www.mvps.org/access/modules/mdl0004.htm

However, that one is about concatenating subform records, which doesn't seem
to be in play here. There is another concatenation code example:
http://www.mvps.org/access/modules/mdl0008.htm

However, that seems it would produce something like this if the input is the
CustNo and EEName:

X123; John Doe; Joe Smith
or
A345; John Doe; Joe Smith

However, I'm not at all sure I am reading the code correctly.

To the OP, I think you are trying to get a listing of all work your
employees have done for a particular customer, but the current table
structure is going to cause you some headaches. Customers should be in one
table, employees in another, and jobs in another at the least.

tblCustomer
CustNo
CustName
Address, etc.

tblEmployee
EENo
EEFirstName
EELastName

tblJob
JobID
CustNo
EENo
JobDate
JobType

There would be a one-to-many relationship between CustNo in tblCustomer and
its counterpart in tblJob; likewise for EENo. There would be a main form
based on tblCustomer, with a subform based on tblJob. The subform would
have a combo box that gets its Row Source from tblEmployee, so that you can
store EENo in tblJob.

This is a bare-bones outline, at least for now. If you are interested in
proceding further along this route I can point you to additional resources,
and provide a nudge here and there.

 

[BruceM]

A few too many "Howevers" in my reply, perhaps. I am a little uncertain
just what the second code option will return, and that is how my hesitation
manifested itself. Upon further examination I think it will not include the
CustNo, but it could be made to do so. For the rest, I am pretty sure I am
reading it correctly. I meant to say it may be possible to adapt the code
to concatenate several fields, but I still suspect it may be a much more
awkward approach than to use related records.

John,

The link didn't work, so I investigated a bit, and found this link, which
seems to be the one you intended:
http://www.mvps.org/access/modules/mdl0004.htm

However, that one is about concatenating subform records, which doesn't
seem to be in play here. There is another concatenation code example:
http://www.mvps.org/access/modules/mdl0008.htm

However, that seems it would produce something like this if the input is
the CustNo and EEName:

X123; John Doe; Joe Smith
or
A345; John Doe; Joe Smith

However, I'm not at all sure I am reading the code correctly.

To the OP, I think you are trying to get a listing of all work your
employees have done for a particular customer, but the current table
structure is going to cause you some headaches. Customers should be in
one table, employees in another, and jobs in another at the least.

tblCustomer
CustNo
CustName
Address, etc.

tblEmployee
EENo
EEFirstName
EELastName

tblJob
JobID
CustNo
EENo
JobDate
JobType

There would be a one-to-many relationship between CustNo in tblCustomer
and its counterpart in tblJob; likewise for EENo. There would be a main
form based on tblCustomer, with a subform based on tblJob. The subform
would have a combo box that gets its Row Source from tblEmployee, so that
you can store EENo in tblJob.

This is a bare-bones outline, at least for now. If you are interested in
proceding further along this route I can point you to additional
resources, and provide a nudge here and there.