Combining two rows into one..

[rob4465]

I could do with some suggestions for getting data from 2 rows into one. To explain further I have a phone report table that I need to manipulate so I can run queries for graphs and stats. I've posted an image that will help explain.

Basically the data I have shows the person's name on one row, then their data is on the next row.

So for example;

Row 1: Joe Bloggs
Row 2: All field data relating to Joe Bloggs

Row 3: Mike Jones
Row 4: All field data relation to Mike Jones

Is there a relatively simple way to approach this? Thanks very much for any suggestions.

 

[Ranman256]

Run a Crosstab query on the group key to make all 1 persons records side by side To a table,
then run a query on that table to concat the row fields.

 

[isladogs]

I don't think a crosstab would work for this and doubt the new table would be properly structured anyway.
No image attached but from your description, this is a badly designed table. More like a spreadsheet.
Can you upload an image of both the table data and table design. Zip them as you have less than 10 posts.

 

[Gasman]

All I could think of would be a recordset approach and a loop as long as every record is in the same format.

 

[vba_php]

doubt the new table would be properly structured anyway.

this is a badly designed table. More like a spreadsheet.

yep. this data should be in 2 tables...1 with the person's name and the other with the person's related data. then running a query is easy.

 

[Pat Hartman]

I'll start with the obvious. Did you try creating a query that joins the two tables on EmployeeID or whatever the related fields are called?

If the issue is that table 1 has one row but table 2 has multiple rows, I'm not sure why a crosstab of table 2 joined to table 1 won't work.

I think we need more information about the details of the two tables.

 

[Deleted member 147267]

I'll start with the obvious. Did you try creating a query that joins the two tables on EmployeeID or whatever the related fields are called?

Where does it state that there are 2 tables? Not that I agree with the notion that there should be either.

this data should be in 2 tables...1 with the person's name and the other with the person's related data

Would anyone put names in tbl1 and then your birth date, gender, phone number, address, etc. into tbl2?


EDIT - OP says an image was posted, but I don't see it or a link to it.

I've posted an image that will help explain.

 

[vba_php]

Where does it state that there are 2 tables? Not that I agree with the notion that there should be either.

Would anyone put names in tbl1 and then your birth date, gender, phone number, address, etc. into tbl2?

sorry about that! what I meant to say was: The person's name in field 1 and all related data in subsequent fields in the same row.

 

[Pat Hartman]

It would appear that I was sleep-typing when I answered the question. Please pay no attention to anything I said - except that we need a better description of the actual input file.

 

[The_Doc_Man]

As an aside, Pat, you are not alone in the occasional "sleep-typing" episode.

 

[Pat Hartman]

Maybe it's a grandparents thingy