Combining Rows

Jan 30, 2008

View Replies

Queries :: Combining Rows Into One Field

May 12, 2013

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

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

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

View 2 Replies View Related

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

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

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

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Combining Tables

Feb 19, 2006

I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.

View 3 Replies View Related

Combining ID Numbers?

Apr 6, 2007

Hello,

I maintain an Access database at my workplace, and have been asked to bring in information from other company locations. Unfortunately, there are conflicts; e.g. part number 12345 might be something totally different at another facility than it is here. My first instinct was pretty obviously to add a Location column and all queries would include that as well as the ID. However, someone else is telling me we should build metadata into a new part numbering convention. An example:

PlantA.12345 (all one field) would be a totally different part than
PlantB.12345

He says it would be very beneficial to ensure that, seeing only a part number, you would know where it was from. I understand his point, but it just seems like strange database design.

Has anyone done something like this, or considered it??

Thanks in advance.

View 6 Replies View Related

Combining Tables

Feb 1, 2005

Hi,

I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?

Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.

Thx,
Agamemnon

View 9 Replies View Related

Combining Records

Jun 22, 2007

I'm modifying a database for someone in our marketing department. The DB tracks shirts that are given to employees.

The area I'm having a problem with has to do with the shirt style.

The current categories offer:

Long Sleeve (White)
Long Sleeve (Black)

etc..

I want to combine those two so they fall under the simple category "Long Sleeve"

The problem I'm having is that when I do a find/replace, it will allow me to change all of the Long Sleeve (White) to Long Sleeve, but then when I go to change Long Sleeve (Black) to "Long Sleeve", it creates duplicate entries because the same order contained x amount of Long Sleeve (White) and x amount of Long Sleeve (Black).

Is there an easy/ automated way to combine these results into "Long Sleeve" and add the quantity fields together to show the total amount of shirts ordered?

View 3 Replies View Related

Combining 2 SQL Statements

Jun 1, 2005

Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.

I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.

SQL Statements

FIRST QUERY

SELECT tblApp.Time
FROM tblApp
WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));

SECOND QUERY

SELECT tblTime.Time
FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time
WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));

Rixx

View 4 Replies View Related

Combining GROUP BY And SUM

Jul 3, 2005

http://www.warwickwebvisions.co.uk/MA/query.gif

The image above shows results from a query I've created. However, I need to group the results by Product Id and display a sum of the individual Product Id's. So I should have something like:

Commercial Loan Mr Steve F Cobblestone LNCLCLRE �299,491.33
Demand Deposit Mr Steve F Cobblestone DD033 �7,402.81
Demand Deposit Mr Steve F Cobblestone DD043 �3,929.78
Time Deposit Mr Steve F Cobblestone TD013 �12,195.60
Time Deposit Mr Steve F Cobblestone TD018 �961,924.12
Time Deposit Mr Steve F Cobblestone TD025 �113,483.58

Any ideas how I construct a query to display the results as per above??

View 1 Replies View Related

Combining Values

Sep 9, 2005

I am pretty new to access and struggling with a query problem. I currently have a table containing data like:

Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue

And I would like to be able to get to

Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green

Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.

Many thanks in advance

Chris

View 5 Replies View Related

Combining Queries

Dec 13, 2005

hi

i want to comibine some queries into one table

the first quierie contains like 6 records and the second one contains 3 records

the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?

View 2 Replies View Related

Combining Tables... HELP!

Feb 22, 2007

:confused: I need help desperately. I have two tables I am trying to comine in a query. Seems easy enough right? Yet the simple task has become impossible. I have two tables with the same feild for data, both containing 8 coulmns. Ex:

Table A-January
Columns
A B C
Data: 1,2,3

Table B-February
Columns
A B C
Data: 4,5,6

I'm trying to create a query that will contain the data 1,2,3,4,5,6 but am getting 11111111111,222222222,33333333333,4444444444,55555 55555,6666666, or getting 1,3,5. I've tried refining the data in many ways, even tried just created a table. Hand keying it in seems rediculous due to the end numbers being over 10,000. Any advice??? Please??? :(

View 14 Replies View Related

Combining Records

May 7, 2007

Hello,

I seem to have a similiar problem to others and I tried to read through this forum, but I am not well versed in the code and I frankly find some of the explanations way to complicated. I was hoping someone can help me with a problem. I have data that looks like this:

Name Comments
J Smith 1/1/07-ATO
J Smith 1/10/07-UAU
S Smith 1/13/07-ATO
D Smith 1/2/07-OT
D SMith 1/3/07-OT
D Smith 1/4/07-ATO

I want it to look like this:

Name Comments
J Smith 1/1/07-ATO, 1/10/07-UAU
S SMith 1/13/07-ATO
D Smith 1/2/07-OT, 1/3/07-OT, 1/4/07-ATO

ANy suggestions?

View 4 Replies View Related

Combining Data

Jun 12, 2007

I just had to pull a large amount of data from an off-site SQL server. Due to speed issues, I broke up the data into 3 pulls and exported the data as text.

I have now imported the data into Access and wish to combine the three tables into one. All of the fields are the same, the only difference is that I broke up the pulls by date ranges.

Would this be done via an Update or Append query? AND, how would that query look? Sorry, still a bit new to this. :)

View 7 Replies View Related

Combining Two Query's

Jul 13, 2007

table : match
fields : season, matchday, clubA, clubB, goalsA, goalsB, points

I'v filled the table with matches. And also calculated how many points every match has. (1 for draw, 3 for a win). So I have 306 matches, for a league with 18 clubs.

Now I want to make a query that makes a ranking based on points.

I have made two queries.
1. PARAMETERS Waarde Value;
SELECT DISTINCT match.[Club A]
FROM match
WHERE (((match.season)=[Waarde]));

Which lists the 18 clubs distinctively in the season.

2. PARAMETERS Waarde Value, Club Text ( 255 );
SELECT Sum(match.points) AS SumOfPoints
FROM (Clubs AS CA INNER JOIN match ON CA.ID = match.[Club A]) INNER JOIN Clubs AS CB ON match.[Club B] = CB.ID
WHERE (((match.[goalsA])>[GoalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])>[goalsA]) AND ((CB.[Club naam])=[Club])) OR (((match.[goalsA])=[goalsB]) AND ((CA.[Club naam])=[Club]) AND ((match.season)=[Waarde])) OR (((match.season)=[Waarde]) AND ((match.[goalsB])=[goalsA]) AND ((CB.[Club naam])=[Club]));

which gives me the total points of a club in a league if I input the season with the value Waarde and the Club with the value text.

How do I combine these two. For every resultrow of (1) I want the result of (2) presented next to it.

I'v tried these two queries and they both seem to work okay.. but how do I combine them ?

View 1 Replies View Related

Combining Two Cells

Jul 18, 2007

In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?

View 1 Replies View Related

Combining Fields

Oct 12, 2007

Hello,

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

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

Into something like this:

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

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

Thanks,

View 3 Replies View Related

Combining Certain Field Into Another Row

Oct 24, 2007

I have a table of UPCs that we use for the tee shirts we manufacture. The problem is that our MRP system organizes the tables with S,M,L,XL UPCs in one row and the XXL UPCs in a seperate row(we use different pricing for XXL). I need to figure out how I can make it so that there is only one row per style number taht has both the S,M,L,XL,XXL UPCs in one row, but can't seem to figure out how to do this. I've attached in image for reference.

View 1 Replies View Related

Combining 2 Queries

Jan 10, 2008

Thank you in advance for your assistance.

I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.

I have a Project table linked to an Invoice Table (1 to many respectively)

The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.

I also have a Reclass table that is linked to the Project table.

Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.

The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.

The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.

I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).

I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).

Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.

I know my explanation is long but I think it will help paint the bigger picture.

Thanks again for reading this!

View 4 Replies View Related