Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Combining Fields From Multiple Rows Into One Row & Field

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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Combining Rows From Multiple Tables Into A New Table
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 Replies !   View Related
Combining Multiple Like Fields And Totaling
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 Replies !   View Related
Combining Multiple Fields Into One Colomn

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.


View Replies !   View Related
Multiple Rows - Same Field Name
I am trying to automate a form and create a word document from it. It is a form which users fill out to request part number files be sent to suppliers. There can be multiple part numbers and file types requested per form with no maximum (except for page space)

I am having trouble figuring out how to code it to get all of the part numbers entered into the word document that will be created.

I have attached screenshots in hopes to explain more clearly what I am trying to do.

Thanks for any suggestions.


View Replies !   View Related
Combine Multiple Rows Into One Field?

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.


View Replies !   View Related
Combining Rows
I have what is probably a simple thing to do in a qry but I just can't figure it out.

I have a spreadsheet that I imported into access. The basis of this spreadsheet is just basic company info.

AAA Comp|123 Street|Anywhere|CA|90210|Mr. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Anybody
ABC Comp|345 Street|Anywhere|CA|90210|Mr. Anywho
XYZ Comp|678 Street|Anywhere|CA|90210|Mr. Whosthat

For some companies, the information will repeat, like ABC Comp. What I need to do is to combine the rows with the same company information ie:

AAA Comp|123 Street|Anywhere|CA|90210|Mr. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Somebody, Ms. Anybody, Mr. Anywho
XYZ Comp|678 Street|Anywhere|CA|90210|Mr. Whosthat|Pres.

Is there a way to do this in a qry?

Thanks in advance!

View Replies !   View Related
How Do I Create A Query To Move 2 Fields On The Same Row To Be 2 Rows In One Field
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

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!


View Replies !   View Related
Combining Multiple Tables With Multiple Records
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

Chester Campbell

View Replies !   View Related
Breaking Down A Field Into Multiple Fields
I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.

Thanks in advance,

View Replies !   View Related
Splitting A Field’s Data Into Multiple Fields
Hi All

Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)

Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?



View Replies !   View Related
Multiple Fields In A Row Changing Colors Yes/no Field.
I have 6 fields entered by the user but reviewed by another user to check for completed assignment. At the end of the row I have a check box with active access only to the user viewing. I want the row to change from black to red when the box is checked off. How do I build to get this event to happen. I'm learning the building and event properties area so I'm still green here. Please help.

View Replies !   View Related
1 Form, 2 Tables, 1 Field To Multiple Fields
Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.

I have a form that updates information on one table, and has a subform displaying info from another table.

the subform is filtered, and only shows data from what is specified from the filter of the main form.

If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?

if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.

like Docmd.write something?

View Replies !   View Related
Combining Fields

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.


View Replies !   View Related
I'm Sure This Is Very Easy: Update Multiple Fields Based On One Field...
I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...

What is the code to have multiple fields updaterd based on what is input into a field?


A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)

I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.

Thanks a million!!!

View Replies !   View Related
Multiple Count Rows, Multiple Tables
I want to count based on 1 (same) field from each of two tables, based on if ID is in "X".

select a.groupid, count(, count(
from ta a, tb b
where value in ('a','b','c')
group by a.groupid

Thats sort of the psuedocode but Im not getting the right results.

Any help would be appreciated.

View Replies !   View Related
Combining Multiple Queries Into One
How does one combine a query of a query into one query. I have some complex queries that query a query because I don't know how to do the same task in one query.

For example, let's say I have a table with EMPLOYEE, DAY, HOURS. I want to pull ALL the data for those employees that work on saturday. That means if they work monday, tuesday, saturday then all of their data will show up. How do I accomplish that in one query?

Currently I do this with two queries:
query1: Select EMPLOYEE from table where day = 'Saturday'
query2: I dont know the correct syntax on this one, but the gist is... using the table and query1 I join them so that everything from table shows that match query1.

Is it possible to do something along these lines:
select EMPLOYEE, DATE, HOURS from (select EMPLOYEE from Table where date = 'Saturday' inner join..blah blah)

The blah blah is whatever the correct innerjoin syntax is.

Thanks for your help.

View Replies !   View Related
Combining Multiple Coloums Into One.
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

Thanks for the help,


View Replies !   View Related
Combining Multiple Columns Into One
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:


and so on and so forth.

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


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 Replies !   View Related
Combining 2 Fields Into 1 Without Spaces
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


View Replies !   View Related
Combining Fields On A Form
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 Replies !   View Related
Combining Fields For Display
I have fields lastname, firstname, spousefirstname, spouselastname -

I would like to display:

Koenig, Mark & Jane (not a problem)

- but in some cases the spouselastname is different so then I would like to display:

Koenig, Mark & Jane Doeinger

This is how it displays with my code:

Koenig, Mark & Jane & Doeniger

I can't seem to get rid of the & between the spousefirstname and spouselastname.

Code:SELECT Potentials.Agent, [LastName] & ", " & [FirstName] & IIf([SpouseFirstName] Is Null Or [SpouseFirstName]=""," "," & " & [SpouseFirstName]) & IIf([SpouseLastName] Is Null Or [SpouseLastName]=""," "," & " & [SpouseLastName]) AS Expr1, Potentials.Address, Potentials.City, Potentials.State, Potentials.ZipFROM Potentials;

How do I get rid of the second &

View Replies !   View Related
Combining Multiple Query Results

I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.

I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.

For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...

So at the moment i have two queries per responsibility, and one pulling those two together i.e.:

New Work Items:
SELECT Count( AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count( AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));

Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;

Is there anyway of combining the first two queries into one, rather than have 3??

Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....

Many Thanks!

PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...

View Replies !   View Related
Combining Multiple Update Queries Into One.
I have a table with a field called "description"
another field in fame table called "length of cable"
In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..

I have another field that I need to put the length in a standard form like xx feet
(The field will have different units in it later not just feet.)
I am using update query
criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot"
with update to (of the length of cable field ) set to 3 Feet
The data has many lengths
and one....

I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft

' and " are the symbols for inches and feet.
Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character
need to do the same to that as well.

Any suggestions '''
before I lose any more hair ...

View Replies !   View Related
Combining Fields To Filter For Same Addresses
I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:

Expression: (Trim(StrConv([AddressStreetNumber]&" "&[AddressAptNumber]&" "[AddressStreetName],3)

Then I set the Total line to FIRST

Is it my syntax or is it that I can't join separate fields together like you can in a report ?

View Replies !   View Related
Combining 3 Fields Space Problem

Im combining 3 fiels with the following code.

Newfield: [Field1] & " " & [Field2] & " "[Field3]. So after eacht field a space is placed but when field 2 had no data there are 2 spaces between field 1 and 2. How do i solve this

You can react in euther english or dutch

Tank you in advance

View Replies !   View Related
Combining First Letters Of Two Text Fields
In a form I can easily combine and display the content of two text fields in a third text field with the formula =[field1] & [field2]. Very well. Now I would like to combine and display only the first letter of each of the two text fields. This I would expect to go somewhat like this =(letter,1,1[field1]) & (letter,1,1[field2]). How is the correct formula?

View Replies !   View Related

Copyright © 2005-08, All rights reserved