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 Replies


ADVERTISEMENT

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

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

Queries :: Entries Separated By Commas - Split A Cell Into Multiple Rows

May 7, 2014

I'm a new to Access, SQL. I need the following output:

Input:

PHP Code:

PO              Vendor       State
a, b             1,2            IL
c, d, e          3              TX
f                  5              CA 

Output:

PHP Code:

PO          Vendor   State
a             1,2        IL
b             1,2        IL
c             3          TX
d             3          TX
e             3          TX
f              5          CA 

I found the following SQL code from another forum, which splits entries in a cell separated by commas into 2 rows, output being:

PHP Code:

PO          Vendor   State
a             1,2        IL
b             1,2        IL
c             3          TX
d,e          3          TX
f              5          CA 

SELECT * INTO ImportedData
FROM (
SELECT PO, Vendor, State
FROM SourceData

[Code] ....

How to run a loop so that if a cell has n entries separated by commas, I want them to be split into 'n' number of rows.

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

Queries :: Rows To Columns

Mar 26, 2013

I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:

Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000

There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:

Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget

Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?

View 2 Replies View Related

Queries :: Turning Rows Into Columns?

Jan 20, 2015

I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns.

My data set contains about 1,000 rows, so I cannot simply paste special in Excel.

Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567

I have tried Pivot and Transform, neither of which seems to work.

View 13 Replies View Related

Queries :: Crosstab To Convert Rows To Columns

Sep 16, 2014

I have data like:

Code:

SubjectID VisitID VisitNum VisitDate VisitDetail Rspns
1 5 1 2/10/2001 Fever_1 Yes
1 5 1 2/10/2001 Age_1 12
2 7 1 5/08/2010 Fever_1 No
2 7 1 5/08/2010 Age_1 18
2 8 2 9/30/2010 Fever_2 Yes
2 8 2 9/30/2010 Age_2 18
etc.

I need to convert it to a non-normalized wide format, like this:

Code:
SubjectID VisitDate_1 Fever_1 Age_1 VisitDate_2 Fever_2 Age_2
1 2/10/2001 Yes 12
2 5/08/2010 No 18 9/30/2010 Yes 18
etc.

If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:

Code:
TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;

View 1 Replies View Related

Queries :: Transpose Table Columns Into Rows

Jun 18, 2013

Is there a way to create a query that will transpose table columns into rows:

Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy

Is there a way to display the table in a single row separated by commas:

xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy

View 11 Replies View Related

Queries :: Multiple Rows Into 1 And Creating New Columns?

Nov 26, 2014

I have a query which is used to pull data from 2 tables.

1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.

2. Need to see the different dates amd know the difference.

Currently Query Looks like this.

Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014

The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.

So I am hoping my end goal it would look something like this.

Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days

I am trying to avoid using VBA or SQL. I do not know where to input it.

I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.

View 5 Replies View Related

Queries :: Summing Columns Into Multiple Rows?

May 13, 2013

I have a linked table. The table looks like:

Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3

I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:

Course name | Course Grade
Student 1
Student 1
Student 2
Student 2

What is the best way to do this?

View 8 Replies View Related

Queries :: Query Results Displayed In Rows Not Columns

Aug 1, 2013

I have the following query which returns 2 columns, where 2 fields are summed :-

Code:

SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices]
FROM Stats
WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));

This displays :-

345 - 988

How can I have the data displayed as

345
988

when I run the query.

View 4 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Modules & VBA :: Union All Query - Transposing Columns To Rows With Variable Columns?

Aug 8, 2013

I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.

Example:

original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5

original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22

The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).

When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".

Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.

View 2 Replies View Related

Converting Rows To Columns And Columns To Rows

Jul 31, 2013

I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff

I would like to covert it to....

Customer 000462 000224

Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.

View 12 Replies View Related

Reports :: Hiding Rows Based On Cell Value

Aug 21, 2013

I have generated a report with my entire list of personnel, and I have created a column that generates thier age based of another column using this:

=DateDiff("yyyy",[Birthday],Now())+Int(Format(Now(),"mmdd")<Format([Birthday],"mmdd"))

Now my question, im trying to hide the rows of those who age is older than 26. If thats not possible maybe highlight the entire rows of those under 26, i used conditional formatting to highlight the age, but not the entire row.

View 1 Replies View Related

Combining Rows

Jan 30, 2008

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

Export Multiple Rows Into A Single Cell In Excel?

Sep 18, 2012

I need to export a list of data into a single cell in Excel.

As an example the list looks like this in Access:

450a

650b

320c

4100d

and exports into individual cells. I need it to export into one cell and look like this:

450a, 650b, 320c, 4100d

View 4 Replies View Related

Populating A Table With Value From Cell Above Across Multiple Columns

Sep 15, 2014

Doing Ctrl + ' will put in the value from the cell above it. But I want to do this across 4 columns. On one row, I am selecting across 4 columns and doing Ctrl + ' and only the first cell populates with the value above it.

Is there a way to get the 3 other columns to populate with the value of them (the values are different across all columns).

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

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







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