Combining two rows into one.. (1 Viewer)

rob4465

New member
Local time
Today, 19:03
Joined
Jan 13, 2010
Messages
4
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

Well-known member
Local time
Today, 15:03
Joined
Apr 9, 2015
Messages
4,339
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

MVP / VIP
Local time
Today, 19:03
Joined
Jan 14, 2017
Messages
18,186
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

Enthusiastic Amateur
Local time
Today, 19:03
Joined
Sep 21, 2011
Messages
14,046
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

Forum Troll
Local time
Today, 14:03
Joined
Oct 6, 2019
Messages
2,884
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

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Feb 19, 2002
Messages
42,971
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.
 

Micron

AWF VIP
Local time
Today, 15:03
Joined
Oct 20, 2018
Messages
3,476
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.
 
Last edited:

vba_php

Forum Troll
Local time
Today, 14:03
Joined
Oct 6, 2019
Messages
2,884
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. :rolleyes:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:03
Joined
Feb 19, 2002
Messages
42,971
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

Immoderate Moderator
Staff member
Local time
Today, 14:03
Joined
Feb 28, 2001
Messages
26,999
As an aside, Pat, you are not alone in the occasional "sleep-typing" episode. ;)
 

Users who are viewing this thread

Top Bottom