Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-11-2019, 02:35 AM   #1
rob4465
Newly Registered User
 
Join Date: Jan 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
rob4465 is on a distinguished road
Combining two rows into one..

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.

rob4465 is offline   Reply With Quote
Old 11-11-2019, 03:38 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,378
Thanks: 0
Thanked 747 Times in 732 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Combining two rows into one..

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.
Ranman256 is offline   Reply With Quote
Old 11-11-2019, 03:45 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,360
Thanks: 115
Thanked 3,110 Times in 2,828 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Combining two rows into one..

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-11-2019, 03:50 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,560
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Combining two rows into one..

All I could think of would be a recordset approach and a loop as long as every record is in the same format.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-11-2019, 04:23 AM   #5
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 677
Thanks: 81
Thanked 79 Times in 79 Posts
vba_php is on a distinguished road
Re: Combining two rows into one..

Quote:
Originally Posted by isladogs View Post
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.
vba_php is offline   Reply With Quote
Old 11-11-2019, 06:38 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combining two rows into one..

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-11-2019, 06:49 PM   #7
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,243
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Combining two rows into one..

Quote:
Originally Posted by Pat Hartman View Post
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.
Quote:
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.
Quote:
I've posted an image that will help explain.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 11-11-2019 at 06:54 PM. Reason: correction
Micron is offline   Reply With Quote
Old 11-12-2019, 02:19 AM   #8
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 677
Thanks: 81
Thanked 79 Times in 79 Posts
vba_php is on a distinguished road
Re: Combining two rows into one..

Quote:
Originally Posted by Micron View Post
Where does it state that there are 2 tables? Not that I agree with the notion that there should be either.
Quote:
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.
vba_php is offline   Reply With Quote
Old 11-13-2019, 11:16 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combining two rows into one..

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-14-2019, 06:42 AM   #10
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,759
Thanks: 93
Thanked 1,725 Times in 1,596 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Combining two rows into one..

As an aside, Pat, you are not alone in the occasional "sleep-typing" episode.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-14-2019, 09:35 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combining two rows into one..

Maybe it's a grandparents thingy

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
combining rows , queries

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining multiple rows for same Id into one row marjon18us Queries 4 10-14-2012 10:25 PM
Combining rows jim924 Queries 2 09-07-2012 04:49 AM
Combining multiple rows into one row in SQL duluter Queries 12 01-20-2009 05:32 PM
Combining multiple rows Sola Queries 5 09-02-2008 03:37 PM
combining rows agonzalez Queries 3 01-30-2008 09:01 AM




All times are GMT -8. The time now is 06:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World