Combining Fields From Multiple Rows Into One Row & Field

Jun 5, 2007

Hello All,

I am trying to figure out the best was to combine fields from multiple rows into one row & field.

Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:

Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1

What I want is one row and the 3 footnotes combines into one field:

Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1

Any help would be greatly appreciated.

View Replies

Combining Multiple Columns Into One Longer Column (without Combining Fields)

Jul 17, 2013

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)

What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.

View 14 Replies View Related

Combining Rows From Multiple Tables Into A New Table

Nov 21, 2006

I have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance

I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance

When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance

I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!

View 1 Replies View Related

Queries :: Identifying And Combining Rows Based On Matched Fields?

Jul 10, 2013

I have two tables. One table is a list of classes with the number of enrolled students:

Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15

And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):

Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2

Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:

Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19

View 7 Replies View Related

Queries :: Combining Rows Into One Field

May 12, 2013

So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)

What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.

Also you select the consumable from a combo box..... if that just makes it that much harder...

View 2 Replies View Related

Combining Multiple Like Fields And Totaling

Nov 10, 2006

Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.

In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case

So clv1 might be done by User A
Then clv2 might be done by User B

But the next case it might be switched.

I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range

Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.

Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?

Well the db example is imb so it cannot be attached

Thanks for any help.

View 13 Replies View Related

Combining Multiple Fields Into One Colomn

Feb 15, 2007

Hello,

I have a table with employee numbers in four fields (Leader, Facilitator, ect...). How can I combine those numbers into one column trough a query?

Thanks in advance.

Keith

View 1 Replies View Related

General :: Merging Combining Multiple Results From Same Field

Jan 16, 2014

Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):

UserA - Hours - Date - ProjectA
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectB
UserA - Hours - Date - ProjectB
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectA

What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.

View 12 Replies View Related

Key On Multiple Fields - Highlight Rows With Matching Keys

Dec 29, 2011

I'm trying to clean up a database and make sure that I don't make an error in future entrys.

Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.

How do I do this? Is it possible?

View 10 Replies View Related

Combining Rows

Jan 30, 2008

View 3 Replies View Related

Combining Rows With Duplicate Values

Jun 17, 2013

I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.

I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.

Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?

Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.

View 8 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Combine Multiple Rows Into One Field?

Mar 28, 2008

Hello,

I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.

This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.

I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.

**Account Number and Sub ID fields in both tables are not unquie.

Data Table: (this table has alot more data but this gives you the idea.)
Account_NUM - Sub_ID - Data1
1234 | 3 | 123 N Inc.
1234 | 3 | 666 DRN
4567 | 4 | 543 S Way.
7890 | 5 | zzz ABC

Notes Table: (Yes the notes field is a MEMO field)
Account_Num - Sub_ID - Notes
1234 | 3 | notes1
1234 | 3 | notes2
1234 | 3 | notes3

The output I am trying to Get:
Account_Num - Sub_ID - Data1 - Notes
1234 | 3 | 123 N Inc. | notes1 notes2 notes3
1234 | 3 | 666 DRN | notes1 notes2 notes3
4567 | 4 | 543 S Way. | NULL
7890 | 5 | zzz ABC | NULL

Thank you for any advice or thoughts.

Joe

View 2 Replies View Related

Queries :: Combining Columns / Rows With Same Cell Value As Key

May 20, 2013

I am a beginner in SQL, I have a problem regarding combining rows/ columns having value. Below is my example:

Table1:
1 123 23 43
1 234 3 431
2 34 34 34
2 54 3 123
3 1 32 32
2 341 1 32

Output Table:
1 123 23 43 234 3 431
2 34 34 34 54 3 123 341 1 32
3 1 32 32

or instead of combining rows, getting the result by combining columns with the same condition.How can I obtain such result?

View 3 Replies View Related

Reports :: Join Together Multiple Rows To One Field?

Jul 1, 2015

I have been struggling with combining multiple rows into one row for an order summary report.

Specifically, I have three tables (Products, Orders, and OrderDetails)

Products
=======
ProductID = AutoNumber
ProductName = String

Orders
======
OrderID = AutoNumber
OrderDate = Date/Time
OrderInfo = Memo
CustomerName = Text
CustomerAddress = Text
PaymentDetails = Text

OrderProductDetails
=========
OrderNumber = Number (Linked to OrderID)
ProductNumber = Number (Linked to ProductID)
Notes = Memo (Notes on product customization)

The OrderProductDetails table is in many to one relationships with both Orders and Products table. If I place an order with 3 products, the OrderProductDetails datasheet would be:

OrderNumber ProductNumber
========================
1 78 (Product 1)
1 89 (Product 2)
1 56 (Product 3)

On my report, instead of 3 entries for this order, I would like show just one entry under a column "Products Ordered" ---> Product 1, Product 2, Product 3.

I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.

I am using Access 2010.

View 10 Replies View Related

Modules & VBA :: Concatenate Multiple Rows Into One Field

Nov 16, 2013

I have a table:

"tb_skybox_Types", with the fields, [Policy_URN], [Box_Type]

Sample:

Policy_URN Box_Type
001 Standard
001 Sky HD
002 Sky +
002 Sky + HD

I'm mail merging this, so naturally it's giving me a row per box type against which I need to be one. Desired output:

Policy_URN Box_Type
001 Standard | Sky HD
002 Sky + | Sky + HD

I'm then going to make this into a temp table, then match the URN back to the customer table to get a single row per customer.

View 3 Replies View Related

Reports :: Concatenate Multiple Rows Into 1 Field

Jun 18, 2013

My database has a 'markets' table, where there could be multiple markets per campaign.

So right now in my report, it's displaying like this

campaign 1
Market 1
Market 2
Market 3
Etc.

Is there a way, to Concatenate multiple rows into 1 field. So it would look something like this

Campaign 1
Market 1, Market 2, Market 3

That would save a lot of space in the report, and make it easier to read.

View 11 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

How Do I Create A Query To Move 2 Fields On The Same Row To Be 2 Rows In One Field

Jun 6, 2007

Hello,
I have a big table like this:
ID Name Race1 Race2 Race3
-- ---- ----- ----- -----
1 a Asian russian

How do I create a query to be like this (either in query or report)?
I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value...
ID Name Race
-- --- ----
1 a Asian
Russian

In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?

Thanks so much!

Jenny.

View 1 Replies View Related

Modules & VBA :: Concatenate Data From Multiple Rows Into Single Row / Field

Nov 4, 2013

I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.

Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents

Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents

Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String

[Code] ....

View 2 Replies View Related

Combining Multiple Tables With Multiple Records

Jul 16, 2007

Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -

I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.

Unit A uses parking units p1, p2, p5 and storage unit s2.

Unit B uses parking unit p3 and storage unit s1.

Unit C uses parking unit p4, p6 and storage unit s3.

How do I write a query/report that would show:

Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3

Thanks,
Chester Campbell
ccampbell@jfreed.com

View 3 Replies View Related

Combining Fields

Oct 12, 2007

Hello,

I need to find a way to modify the following structure:

Name, Page, Grid
Acton Rd, G10, 12
Acton Rd, G10, 4
Acton Rd, G10, 8
Adams Ct, F6, 2

Into something like this:

Name Page Grid
Acton Rd, G10, '4, 8, 12'
Adams Ct, F6, '2'

What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.

Thanks,

View 3 Replies View Related

Combining Multiple Coloums Into One.

Feb 16, 2007

Hello all,

I have an issue I can't quite get my head around. I have a table with four coloums that list Emplyees by Number. I need to pull thase number and combine them into one colomn through a querry (I think). I don't need to join the coloums in the same record. Example...

Now... leader Pilot Admin Facilitator
11800 11801 11802 11803

Needed... Participants
11800
11801
11802
11803

Thanks for the help,

Keith

View 6 Replies View Related

Combining Multiple Columns Into One

Sep 4, 2007

I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:

Job1......Job2......Job3......Job4......Job5
Joe.......Bob.......Jim.......Fred......Jeff
Joe.......Joe.......Jeff......Jim.......Jeff
Bob.......Bob.......Jeff......Bob.......Jim

and so on and so forth.

I want to have a query that gives one column that looks like this:

Bob
Fred
Jeff
Jim
Joe

I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?

(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)

View 3 Replies View Related

Combining 2 Fields Into 1 Without Spaces

Jan 26, 2006

I have 2 seperate fields within a select query (In this example Field 1 - Country and Field 2 Number).

I have combined the 2 together into one field.

However a space appears within my results i.e

Field 1 Country Field 2 - Number
----------------- -------------------
England 4
USA 10

Combined fields:

Field 3 - Combined Country + Number
--------------------------------------------

England 4
USA 10

How can I remove this space within Field 3

Thanks

View 4 Replies View Related

Combining Fields On A Form

Dec 20, 2006

Hay Folks,
I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form.
The problem.
I cant get the expression right to show real values instead of key-values.
Here's the sample expression for the control-field:
=[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]

Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]...
SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;

Can anybody help me on track?
Thanks a lot.

View 10 Replies View Related