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.



ADVERTISEMENT

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

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 Replies View Related

Combining Existing Fields Into One New Field - Version: 2000 (9.0)

I've looked in a number of books, searched the forum, tried a few things but am obviously missing something somewhere - I cannot help but think that this should be relatively easy to do.

A customer address is stored in the customer table in columns called Addr1, Addr2, Addr3 and Addr4. What I would like to do is have a single address field which combines all 4 (which I will then delete). How can I combine all 4 into a new Address column, preferaby with each of the original fields on a separate line in the new field (txt 250)

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.

Toni

View Replies View Related

Combine Multiple Rows Into One Field?

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 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.

Company|Address|City|State|Zip|Name
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:

Company|Address|City|State|Zip|Name
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

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 Replies View Related

Concatenate Text Field From Multiple Rows - Version: 2000 (9.0)

I have a database that I need to export data from to exel.
It is in this format:
ID FName LName Stuff Comments
123 Joe Smith blah Note about stuff last week
123 Joe Smith blah More notes about stuff today

What I need to export is:
123 Joe Smith blah Note about stuff last week. More notes about stuff today

I can concatenate two columns but I seem to be lost combining the rows...

Hope someone has a simple solution to this,

Mel

View Replies View Related

Combining Rows From Two Tables Into A New Table - Version: 2003 (11.0)

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

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 swear there's a way to do this, I just can't remember it!

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

Thanks,
Chester Campbell
ccampbell@jfreed.com

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.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
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.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
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,
Aldo

View Replies View Related

Multiple Entrites In One Row To Multiple Rows. - Version: 2003 (11.0)

I have four columns on a Table. The Access assigned ID number, Document name, Document Date, and Recipients.

The problem is in the recipients field, there are often multiple entries separated by a semicolon. What I'd like to do is create multiple records for each recipient while keeping the Document Name and Date the same. Any suggestions?

Any help would be appreciated.

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 ?

Thanks

Jim

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

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?

---
Example:

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(a.id), count(b.id)
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 Fields

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 Replies View Related

Relationship From 1 Field To Multiple Fields In Another Table - Version: 2003 (11.0)

I have tblTechs with fields TechID(PK) and TechNames and also tblWO with 4 fields WOID(PK),tech1FID, tech2FID,Tech3FID

to create relatinships, do I need to add the same table 3 times? I dont think thats right, but I tried making a single relationship with 3 rows, and that wouldnt work....

How do I create relationships from Each of the 3 fields to the single field in tblTechs.TechID and enforce intregrity ?

Thanks

View Replies View Related

Breaking Data In One Field Out To Multiple Fields - Version: 2003 (11.0)

Hello,

I'm a former mainframe programmer, but am an Access newbie. My manager has asked me to create a new Access database using data from an Excel spreadsheet. Once the data is imported, he wants to be able to add some more info in new fields to be defined in the Access database. So far so good - we know which fields we want to export, and exporting the data is simple.

(Hopefully I am using the right terminology; are 'field' and 'cell' interchangeable?)

One of the Excel fields is labelled "remarks", and it can contain variable data. The field might contain:

account # (6 char), name (could be up to 70 characters, ending blanks are removed), phone # (10), fax # (10), address (variable length, but ends in zip code) and symptom (variable length)

OR it might contain just symptom ("unit shut down unexpectedly at 2:00 a.m.")

There are no commas seperating the various parts of the 'remarks' field

If I was programming this in COBOL I would move the whole field to a table, check the first character to see if it is numeric, and if so then the 'remarks' field *should* be acct #, name, etc. I would then want to break up this field into the individual fields in Access. If the first character is not numeric, then I would move the entire contents of the field to the 'symptom' field, and the other fields (acct #, etc) would be blank.

1) Is this possible in Access and/or VBA?
2) If so, are there macros available for checking numeric data?
3) any other tricks that can be used for seperating this data out into its respective parts?

Thanks in advance for any help!

-Doug B

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


Thanks

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 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
11800
11801
11802
11803

Thanks for the help,

Keith

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:

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 Replies View Related

Distribute Data From One Field To Multiple Fields In A Table - Version: 2002 (10.0) XP

I am developing a simple system to track bin locations. We have product (large boxes) scattered among 10 or more bins in our warehouses, and we are taking inventory this week.

My last piece of the puzzle is a report that lists the bin locations. To save space, I want to display the bins in a row (or sequence of rows) instead of the normal column display.

I am at this point: The data will be entered via form into a table and then consolidated by bin location, which will be reformatted with the bin counts "bin(count)". At this point the data is in a single field labeled "bincount" I have another table called tmpBin which has 9 bin fields (bin1, bin2, etc.). I am working on redistributing the data from bincount across bin1, bin2, etc. so the report will print 1 row of bins if product is only in 9 or fewer bins.

I am decent with access, but I think this will need a loop in VBA which regrettably exposes my defiencies. Any help on setting up the loop to redistribute the data, or if you have a better solution, please post your reply!

Many thanks,

Thomas

View Replies View Related

Combining Fields To Filter For Same Addresses

Hello
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

Hi,

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

Combining Address Fields Into One Column

Hello,
How can I combine two seperate fields into one field? For example
field1 has 123 field2 has Smith St. I want to put it in one column
that has 123 Smith St.

Thanks

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved