Queries :: Concatenating Multiple Rows In Access 2010

Feb 11, 2015

I have a query that has multiple IDs and different information in numerous fields. For example:

ID Field1 Field2 Field3
1 x
1 m
1 b

I need to find a way to concatenate the data so that it shows the information like this:

ID Field1 Field2 Field3
1 x m b

I'm working in Access 2010.

Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.

View Replies


Access 2010 - Table With Same Fields And Rows

Aug 24, 2011

i'm using Access 2010 and i created a main form , from which i want to let users to open new table with fixed fileds and rows, also the name of the table will be as per user choice. so if a user want to open new table from the main menu, he will have the choice to press on a command button and a dialoge box will ask to name the table. the new table will then be open with the same fileds and rows names( name only) . the new table will be edited using a form.

View 3 Replies View Related

Update Queries On Multiple Rows

Mar 16, 2007


Is it possible to update multiple rows in a Table at one time using Update Query?


View 5 Replies View Related

Queries :: Concatenate Multiple Rows Without VBA

Sep 23, 2014

I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.

So I am desperately trying to find a way of doing this without VBA code.

Example of data:

Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105

I am trying to achieve below:

Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104

View 14 Replies View Related

Queries :: Multiple Rows In A Query?

Jun 10, 2013

I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:

292020 M 5/11/2012 ($33.95)
292020 11 5/14/2012 $33.95
292020 A 5/30/2012 ($33.95)

View 3 Replies View Related

Queries :: Convert Multiple Rows Of Data Into 1 Row

Oct 11, 2013

I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.

View 7 Replies View Related

Queries :: Transform Table With Multiple Rows To One Row?

Dec 22, 2014

I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:

10000Frost Bite

And I'd like to convert it to this:
Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4
10000 HypothermiaFrost Bite
10001 Fatigue DehydrationExhaustion
99999 Exhaustion Hypothermia

The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.

View 3 Replies View Related

Queries :: Match Multiple Rows From Same Column

Apr 2, 2014

I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.

Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,

PersonList -= Meds
Regimen -= Meds as well

Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)

Quy 1 Result:
PersList T1Component
Andrew Med 1
Andrew Med 2
Brett Med 1
Brett Med 3
Brett Med 4
Charles Med 2
Duane Med 1
Duane Med 4

Quy 2 Result
Regimen T2Component
Goody1 Med 1
Goody1 Med 3
Goody1 Med 4
Goody2 Med 1
Goody2 Med 2

I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..

"Good" would return
Person Regimen
Andrew Goody2 (he has med 1, 3, and 4)
Brett Goody1 (he has med 1 and 2)

"Bad" would return

What they "almost match" does not matter; it tells people which ones we need to check into a bit more.

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

Concatenating Various Values From Multiple Columns

Nov 25, 2005

I am trying to concatenate values from seperate columns. Which is fine, however if there are no values in any of these columns I wish to include a dot (.). I also need to add a dot (.) inbetween values.

So for example

Column1 Column 2 Column 3

B11 Europe

would concatenate as:


View 1 Replies View Related

Queries :: Retrieving Multiple Rows And Placing In One String

Jan 31, 2014

I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.

I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.

Example below: >>>

The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.

Sample Data:

459Authorized Privileges:
467Default Privileges:

Output Expected:

The delimiter can be anything.

View 1 Replies View Related

Queries :: Display Multiple Rows Into One Row Query Result

Mar 25, 2013

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

View 5 Replies View Related

Queries :: Query Bringing Back Multiple Rows Not Just One?

Apr 10, 2014

I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.

View 9 Replies View Related

Creating Report From Multiple Sources In Access 2010

Jun 12, 2013

I am trying to create a columnar report that relies on information that are expressions in various queries. Unfortunately, I can't seem to create them in one query because the criteria is mutually exclusive. I used the report wizard to start the report based on the single expression in the first query. Now I want to start adding the other data but I can't seem to get it to work. All I really want to do is include fields that have the have the value, which is already summed, from the other query. When I try that I get error messages like "you tried to execute a query that does not include the specified expression 'expressionName' as part of an aggregate function' and it just goes further downhill from there. What is the easiest way to get this simple values into the report? Is there a way I can create a field in the report that uses a SQL statement for just that field so I don't have to have the report linking to 50 different queries?

View 14 Replies View Related

Modules & VBA :: Concatenating Data From Multiple Checkboxes

Apr 15, 2015

I have 8 checkboxes. Each checkbox has several e-mail addresses as string. Therefore, each checkbox has a string variable declared. I was wondering what should I do when selecting multiple check boxes. This is my code:

If Me!chkAGDLLA = True Then
Forms!email.lstName = Null
Forms!email.chkComercial = False
Forms!email.chkOperacional = False
strAGDLLA = "email1, email2, email3, email4"
strMail = strAGDLLA & ", " & strISA & ", " & strMAYA & ", " & strSANGER & "," & strSANSE & ", " & strSede & ", " & strGC & ", " & strCSR
MsgBox strMail
End If

Problem is that if I only select chkAGDLLA, then strMail will be "email1, email2, email3, email4, , , , , , ,"

I do not want all those commas, but how to make this work. I was thinking maybe a SELECT CASE so that strMail will accumulate data based on what's checked, but then there will be a problem of there being no comma between cases.

View 6 Replies View Related

Queries :: Return Multiple Rows From A Single Record In Database

Jul 16, 2014

For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows

1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.

I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:

ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....


I could then quickly count how many obs periods started within the desired month.

View 14 Replies View Related

Access Data In Multiple Rows In A Form

Feb 23, 2006

Hi guys ,

I'm very new to Access , but I need to do this and I can't figure it out. I have a form that looks like this :

http://img64.imageshack.us/img64/5566/formemail4qz.jpg (http://imageshack.us)

I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?

(Sorry if this is a documented question , I've tried searching but couldn't find anything )

View 3 Replies View Related

Modules & VBA :: Export A Table From Access 2010 Into Existing Multiple Tab

Sep 25, 2014

I am trying to export a table from Access 2010 into an existing multiple tab excel 2010 spreadsheet.I want it to overwrite the "data staging" tab each time.I have it adding the tab into the existing spreadsheet but it names it "data_staging" however if I run this a second time I get excel found unreadable content in 'data staging' Do you want to recover the contents of this workbook? if you trust the source of this workbook click yes.

Code I am using

'export to existing spreadsheet data staging
Private Sub Command5_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Phx Data Staging", "F:My DocumentsWorkSGN est est data staging.xlsx", False, "data staging"
MsgBox ("Completed")

View 1 Replies View Related

General :: Prevent Multiple Instance Of Access 2010 Runtime

Jun 21, 2014

is it possible to prevent

1- running multiple instance of MSACCESS.exe in runtime
2- running multiple instance of same database in runtime.

i have found seems not to work on access 2010 runtime.

View 7 Replies View Related

Queries :: MS Access 2010 - Export Queries Into One Workbook But Different Worksheet

Jul 16, 2013

I have three Queries and I need to export three queries into one workbook but different worksheet,

Currently I am using ExportWithFormatting , but the result came out is three different workbook .

Is there any way I can export to one workbook ?

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


PHP Code:

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


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

Modules & VBA :: Update Access Database (multiple Rows)

Jan 7, 2014

I have a table called "Mov" and its columns are:

Number | Link | Name | Status
1899 | htto://example.com/code1 | code1 | Done
2 | htto://example.com/code23455 | code23455 | Done
3 | htto://example.com/code2343 | code2343 | Done
13500 | htto://example.com/code234cv | code234cv | Deleted
220 | htto://example.com/code234cv | code234cv | Null
400 | htto://example.com/code234cv | code234cv | Null

So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .

Simply I want to update "Null status" to become "Done" according to its numbers according to this list


Something like this

I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...

View 4 Replies View Related

Queries :: Access 2007 / 2010 - Combining Two Queries Into One?

Jan 24, 2014

I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.

The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.

I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.

I am running ACCESS 2010 Tables are ACCESS 2007.

OS is Windows 7 Ultimate.

I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.

View 7 Replies View Related

Forms :: Build Up A Database For Multiple Choice Questions Quiz In MS Access 2010

Aug 23, 2014

I'm trying to build up a database for Multiple choice questions quiz in MS access 2010.My requirement are as follows

My quiz consist of 15 questions. Each questions carry some weightage. Suppose a question carries a weightage of 5 and there are two correct options like A & C. If user selects option A then he gets half marks.Multiple users can access the test at the same time.

View 1 Replies View Related

Retrieving Rows From Multiple Table To Fill MS Access Report.

Sep 20, 2006


I am using VB.NET 2003 and MS Access XP for a desktop application. While developing the application we have a reached a situation where we want to print a report which retrieves records from four tables. Till here it is easy to think that it can be done by a simple SQL JOIN query, but following is the complexity:

The first table stores a single row.
The second table stores multiple rows related to the Primary Key field defined in Table One.
The third table stores a single row related to the Primary Key field defined in Table One.
The fourth table stores a single row related to the Primary Key field defined in Table One.

The above SAVE RECORD option is performed when a user fills a Form of my application. As stated above, all the four tables are inter-related with a Primay Key field (TNo) defined in table one.

I also have a MS Access Report that will print information retrieved from all the four tables. The Report has some of the fields from each of the above table. The SAVE operation is performed in this way:

(1) A unique TNo is generated for a new record that is about to be created.
(2) All the entries are saved in their respective tables (mentioned above.)
(3) An access query will fetch the records pertaining to this TNo from all the tables to fill the report.

I want to know how to write such a query when I have to fetch multiple rows of a table in between. Is there any way that I can pass the TNO as a parameter to this query that is saved in MS Access?

View 1 Replies View Related

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