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 Replies


ADVERTISEMENT

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 Columns

Feb 7, 2006

Hello,
I am trying to combine 2 columns from 2 different tables. Table names are Center and TeachingSite. These tables are stored in an Oracle database, so i can't really change the table structure. I need to combine the Center.Name and TeachingSite.Name field into a list box on a form. This way, everything is listed into one list box instead of a list box for Center.Name and 1 for TeachingSite.Name. Is there a way to create a SQL statement or anything else that would work that will combine these into one listbox and be able to select any item from the list to show related data on subforms and so on. Thanks.
Jared

View 8 Replies View Related

Combining 2 Columns For Retrieval

Jan 11, 2008

Hi,

I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?

Thanks

View 1 Replies View Related

Combining Some Columns In Access Table

Jun 21, 2012

I have a table in Access with 184,000 rows or records of data and 20 columns or field names. When looking at the table ten of the columns represent the identifying information for the data stored in the other ten columns. I need to convert the last ten columns of data into one column with a second column added to identify the original field name. Then end result would be one current record of data being converted into ten records with the original 10 columns of identifying data, a new column containing the original field name and a column for the data value from the original column identified in the new column.

I have been able to do this in Excel using a consolidation pivot table, un-checking the rows and columns then clicking on the final sum value to get a detailed list of the records. Unfortunately there still appears to be a cap because I get a message saying some of the data has been dropped after a little over a million rows.

I am using the 2010 version of Access and Excel.This is the smaller table. I have several others that have up to 200 columns that need to be converted the same way.

View 6 Replies View Related

Tables :: Combining Two Text Columns Into One In A Table?

Aug 16, 2013

My Approach database contains records relating to nearly 800 sites in London. There are radically different amounts of data held about each site. The database contains a lot of different tables, each containing a different class of information. Not all the sites listed in the database have information in all of the tables.

The unique thing that holds it all together is the unique site reference number. Unfortunately this is split into two separate columns in the Approach database, "Reference" and "Suffix". Not all sites have a suffix. (The purpose of the suffix is to identify sub-sites which are subordinate to the main site reference, but need to have their own individual records.) Because many sites have no suffix, most of the fields in the second column are blank.

In order to link all the tables together in access I need a Primary Key which is unique to each site. In this case the reference/suffix number is the obvious (only) candidate. There is no problem using two different columns to create a primary key. The problem I face is that it doesn't like the fact many of the fields in the second column are blank.

My solution to this is to combine the two columns into one. That would give every site a unique reference, and none of the fields in the combined column would be blank. Can I work out how to do it? All I want is a new column that displays the reference and suffix (if any) in a single field, no spaces.

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

Queries :: Combining 4 Columns - Leading 0s Lost

Apr 18, 2013

I'm trying to combine 4 columns. Some of the columns have leading 0's and when I run my query I loose the leading 0's.

BATCH SOURCE NO SEQLINE
11012011 P 004800525

SELECT [BATCH] & "" & [SOURCE] & "" & [NO] & "" & [SEQ] AS RECEIPT, [7ef].LINE
FROM 7ef;

screen shot attached

View 6 Replies View Related

Queries :: Combining Columns And Replacing Certain Values

Sep 29, 2014

I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.

I have an example of what I'm working with (access file) and what result I want (excel file).

View 2 Replies View Related

Combining Date Columns From Different Tables And Retrieve Data

Aug 17, 2012

I'm trying to retrieve information from 3 tables (Order, Receive and Issue) to one table as Inventory Table/Query. it looks like as follows

Order Table:

OrderID
ProductID
OrderDate
OrderQTY

1
1
3/1/2012
30

2
1
4/1/2012
10

3
2
4/1/2012
20

Receive Table:

ReceiveID
ProductID
ReceiveDate
ReceiveQTY

1
1
3/3/2012
30

2
1
4/3/2012
10

3
2
4/2/2012
15

Issue Table:

IssuedID
ProductID
IssuedDate
IssueQTY

1
1
3/5/2012
20

2
1
4/6/2012
10

3
2
4/5/2012
5

I'm looking for Inventory table/query which will give all of the data per date, which is like the following

Inventory:

Date
ProductID
OrderQTY
ReceiveQTY
IssueQTY

3/1/2012
1
30

3/3/2012
1
30

3/5/2012
1
20

4/1/2012
1
10

4/1/2012
2
20

4/2/2012
2
15

4/3/2012
1
10

4/5/2012
2
5

4/6/2012
1
10

View 3 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

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

Queries :: Consolidate Multiple Columns Into Two Columns

May 14, 2014

I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.

What the simple query looks like: [URL] ...

Second what I want the query to look like: [URL] ....

View 2 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 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 Query Results

Dec 4, 2006

Hi,

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(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) 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!
Gareth

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

Combining Multiple Update Queries Into One.

Mar 12, 2007

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
with
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
1ft
2ft
3ft
4ft
5ft
6ft
7ft
10ft
12ft
14ft
15ft
20ft
25ft
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 6 Replies View Related

Combining Data From Multiple Listboxes?

Oct 4, 2012

I have created a DB who's sole purpose is to track employee availability and job positions available (a poor man's version of a scheduling database, if you will). I have 2 listboxes on one form. Listbox A shows all the employees available for work, and Listbox B shows the available work positions that require an employee. I am trying to create a functionality behind a button that will allow me to highlight one row in Listbox A and one row in Listbox B to combine the information in a seperate table (still not created) and refresh each Listbox to remove the entries. Similarly, I would also have to create functionality behind another button to undo the above, if needed.

View 1 Replies View Related

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

Combining Multiple Toggle Button Filters

Sep 17, 2013

I have a form that I use to filter a sub-form. In the form I have 4 toggle buttons that filter the corresponding fields in the sub-form quite well. What I would like to have is when one toggle is selected, the user can select a second or third toggle to further refine their inquiry. I am attaching the code that I used for the individual filters.

Code:

Private Sub Toggle_Filter_DOB_Click()
If Me.Toggle_Filter_DOB = True Then
[Forms]![Phase 2]![FormPhase2_sub].Form.Filter = "DOB = #" & Me.ATS_DOB & "#"
[Forms]![Phase 2]![FormPhase2_sub].Form.FilterOn = True
Me.Toggle_Filter_DOB.Caption = "Filter On"

[Code] ......

View 4 Replies View Related

Modules & VBA :: Combining Multiple Tables Of Similar Type?

Jul 17, 2014

I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.

I would like to combine all of the tables which come from similar worksheets into one table (one table per name).

I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. t how to code this successfully?

View 14 Replies View Related

Modules & VBA :: Combining Multiple Data Sets To 1 Table?

Aug 8, 2014

I'm trying to set up a way to import and combine excel files that contain multiple data sets. So for example, each excel file has a summary heading which consists of the first 3 rows.

Each data set thereafter consists of approximately 50-60 rows of data that I would like. There are approximately 1400 groups of data. Each group has a label which includes the state and the store number. I would like to automate a way to copy the state and store number information down each data set as well.

I have approximately 200 excel files that I want to load into access and have it format it so it basically will end up 1 big file with State, Store Number, relevant information from the data set. I've seen a module do this before, can't remember how to do it.

View 4 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

Queries :: Put Multiple Records In Multiple Columns

Jul 31, 2013

I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).

The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!

What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.

The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).

Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.

See the attached file ....

View 2 Replies View Related







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