Combine Datatable Rows Based In Common Value
Nov 27, 2012
i have been trying to combine data from 2 different sources, to make unique rows: here it is my situation Data in DGV already in dgv, Unbound:
column1 | Qty
"Sugar", 100
"Salt", 100
"Color", 200
"Malto", 150
Data in datatable:
column1 | Qty
"Sugar", 80
"Salt", 60
"Apio", 25
"Lemon", 60
"Color", 60
So i want a control that matches the column1 in datatable against DGV.Column1, and if value matches only add second column value and if not, then make a new row in DGV.
Final decided outcome:
column1 | Qty|Qty2
"Sugar"| 100 | 80
"Salt" | 100 | 60
"Color"| 200 | 60
"Malto"| 150
"Apio" | | 25
View Replies
ADVERTISEMENT
Jul 30, 2013
I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
View 1 Replies
View Related
Mar 28, 2008
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 2 Replies
View Related
Oct 16, 2013
I have a table which shows when a product is available in the stores.
For example.
Product Store Available
A NS Jan, 2013
A BC Marc, 2013
How can I combine the available/store field which just shows
Product Available:
A - Jan, 2013 in NS AND Mar, 2013 in BC.
View 1 Replies
View Related
May 31, 2013
I am trying to form a join between two separate databases. Database 1 has a column (Gene_Name2) that consists of common names of human genes. In this database there is only one name for each gene per field. The other Database 2 has a column (Gene_Name2) that has various names for the same gene in each field. Most genes have been given different names over time.
I want to join the databases into a separate query database so that all of the information in Database 1 is retained and only when the name in Database 1 matches one of the names in Database 2 does the row of information get tacked on to the query database. The problem is that in a normal query the text in both Gene_Name columns do not match and the query returns no matches. I have tried Instr and like criteria statements with no luck. The following is an example of what I have and what I need.
Database 1
Chrom Gene_Name1 Data
Chr1 ATC XYZ
Chr2 IDH2 ZYZ
Database 2
Gene_Name2 Data
ATC;ORF34;NM_123 ABC
ORF65;IDH2 IGH
Joined Query
Chrom Gene_Name1 Data Gene_Name2 Data
Chr1 ATC XYZ ATC;ORF34;NM_123 ABC
Chr2 IDH2 ZYZ ORF65;IDH2 IGH
View 7 Replies
View Related
Apr 16, 2013
My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.
So in simple terms, return all trips for each docket number then number each trip.
View 5 Replies
View Related
Sep 23, 2005
Alrght I'm new to access databases so I don't want to make the mistake of assuming I know more than I do, so please help me out if you can. Thanks.
I would like to insert a DataTable that is created from reading an ASPX page's TextBoxes into an Access table or two. So here is my code for the insert.
Code: C#
PHP Code:#region Insert
public bool Insert(ref DataTable InsertData)
{
OleDbConnection myConnection = new OleDbConnection();
OleDbCommand myCommand = new OleDbCommand();
OleDbDataAdapter myAdapter = new OleDbDataAdapter();
OleDbParameter param;
DataTable dt = new DataTable();
StringBuilder SQL = new StringBuilder();
try
{
// Build SQL Statement
SQL.Append("INSERT INTO Driver (");
SQL.Append(" Driver.ID,");
SQL.Append(" Driver.ReservationID,");
SQL.Append(" Driver.PrimaryContact,");
SQL.Append(" Driver.FirstName,");
SQL.Append(" Driver.LastName,");
SQL.Append(" Driver.Address1,");
SQL.Append(" Driver.Address2,");
SQL.Append(" Driver.City,");
SQL.Append(" Driver.State,");
SQL.Append(" Driver.Postal_Code,");
SQL.Append(" Driver.Ownership,");
SQL.Append(" Driver.HomePhone,");
SQL.Append(" Driver.CellPhone,");
SQL.Append(" Driver.AutoInsurance,");
SQL.Append(" Driver.Agent,");
SQL.Append(" Driver.Policy,");
SQL.Append(" Driver.Phone,");
SQL.Append(" Driver.Birthday,");
SQL.Append(" Driver.DriverLicNum,");
SQL.Append(" Driver.DriverLicState,");
SQL.Append(" Driver.DriverLicExpDt,");
SQL.Append(" Driver.DrivingRecord,");
SQL.Append(" Driver.DrivingRecordDetails,");
SQL.Append(" Driver.DriverNumber,");
SQL.Append(" Driver.IsDriver,");
SQL.Append(" Driver.InsertDt,");
SQL.Append(" Driver.ReservationID)");
// Insert Values
SQL.Append(" VALUES(");
SQL.Append(" @ReservationID,");
SQL.Append(" @PrimaryContact,");
SQL.Append(" @FirstName,");
SQL.Append(" @LastName,");
SQL.Append(" @Address1,");
SQL.Append(" @Address2,");
SQL.Append(" @City,");
SQL.Append(" @State,");
SQL.Append(" @Postal_Code,");
SQL.Append(" @Ownership,");
SQL.Append(" @HomePhone,");
SQL.Append(" @CellPhone,");
SQL.Append(" @AutoInsurance,");
SQL.Append(" @Agent,");
SQL.Append(" @Policy,");
SQL.Append(" @Phone,");
SQL.Append(" @Birthday,");
SQL.Append(" @DriverLicNum,");
SQL.Append(" @DriverLicState,");
SQL.Append(" @DriverLicExpDt,");
SQL.Append(" @DrivingRecord,");
SQL.Append(" @DrivingRecordDetails,");
SQL.Append(" @DriverNumber,");
SQL.Append(" @IsDriver,");
SQL.Append(" @InsertDt,");
SQL.Append(" @ReservationID)");
SQL.Append(" )");
// Define Connection
myConnection.ConnectionString = _ConnectionString;
// Build Command
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = SQL.ToString();
// Execute
myConnection.Open();
myAdapter.SelectCommand = myCommand;
myCommand.ExecuteNonQuery();
}
catch( Exception myException )
{
// Raise Error
throw new System.Exception("Exception occurred in: " + MODULE_NAME + ".GetListByRVType(). Exception Error: " + myException.Message, myException);
}
finally
{
// Clean up
myConnection.Close();
myCommand.Dispose();
myAdapter.Dispose();
}
}
#endregion
View 1 Replies
View Related
Nov 27, 2006
How to autofill datatable columns with increasing numbers - like the way in Excel? :confused:
Given a datatable with 2 columns : ID & Data.
(a) How to fill the column "Data" with consecutive numbers - 1000, 1001, 1002 ...?
(b) How to fill a specified range of continuous records - eg. records ID100, ID101, ID102 ... ID300 - with (a)?
Many many thanks for any help and discussion!
View 2 Replies
View Related
May 8, 2007
Hi all,
Very very new to Access, moved department a week ago and inherited a couple of databases that need tweaking. Although I'm learning I am stuck by something very simple and can't find it by searching this forum or Google :)
I have a query that originates from 2 tables, there is one custom record made from 2 of the fields -
Free Stock: [stk_stkqty]-[stk_allstk]
Now I want a record that shows -
Product where the free stock = backorder stock
so theroetically my guess as a beginner is -
SELECT * FROM [Product] WHERE [Free Stock] <> [Back Order]
But it fails miserably on syntax. Can someone point me in the right direction, and I apologise for my lack of understanding in advance.
Boofuls :)
View 5 Replies
View Related
May 23, 2005
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Parameter..........................Member Count
01/01/04 thru 01/20/04...............298
02/01/04 thru 02/20/04...............287
03/01/04 thru 03/20/04...............301
04/01/04 thru 04/20/04...............254
And so on, usually for a calendar year.
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
Thanks,
Hanna
View 2 Replies
View Related
Nov 23, 2005
I have an address database where people have been allowed to type in any characters they want in place of just leaving a missing field blank. For instance the Zip code was not a required field (don't as me why because I don't know. It just wasn't) so over the years when the person entering the data didn't know what the zip code was they would put a ?, a 0, a 000 or a period or what ever else they could think of at the time.
I would like to add a filter in my query where I could eliminate any thing with less then a certain amount of characters.
I thought something like this would do the trick
Zip: IIf(Len([V_ZIP]<5),[V_ZIP],"0")
But I am getting a data type mismatch in criteria expression. Any ideas of what I am doing wrong?
View 4 Replies
View Related
Sep 1, 2006
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
View 5 Replies
View Related
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
May 11, 2015
I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:
DATE ID
01/01/2015 1
02/01/2015 1
What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.
I'm unsure what the easiest way to do this would be (or how to do it at all).
View 2 Replies
View Related
Jul 24, 2012
I have an infopath form, which people in my company can fill out to order parts. The form is linked to an Access database. The primary keys for the database are the Order Number and the Line Number (where the specific part info is in the system). The form also takes info about who entered the order, when they entered it, etc.
My problem is that while the form will be used for only one order, it may be used to order multiple parts. So the infopath form has a repeating section with a table where they can enter multiple line numbers and part names, etc.If I use the form to enter just one part, everything works great. But when I use the repeating section to add multiple parts, the information specific to the repeating section is added to the database but the rest of the information (who entered, when the entered, etc) isn't.
My intial idea was that since the first part is entered correctly and the subsequent parts are missing some information which is already in another line in the database. Maybe if there was a way to pull the information from another line into the lines with missing info within access would solve my problem. So basically automatically populating the rows with matching Order Numbers with the information that is missing.
View 7 Replies
View Related
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
Apr 22, 2014
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
View 4 Replies
View Related
Dec 19, 2011
I have an access DB.I have an XLSM Data Sheet.In the XLSM I have A few Rows with Fields.In the Fields I have a few matching fields as in the Access DB.I also have a few extra fields that i want to add to the Access DB
I want the new fields of the ADB to Populate from the XMLS in the proper rows based on the ID number in the ADB.
View 5 Replies
View Related
Apr 15, 2014
I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.
View 3 Replies
View Related
Jul 24, 2014
I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.
I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).
The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color.
i.e.
3 = green
2 = yellow
1 = red
View 4 Replies
View Related
May 20, 2005
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.;
- Add Groupings
- Add Grouping Headers//Footer, sorting option
- Add bound textbox
:eek:
Needless to say this is very annoying.
:mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5
FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5
FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5
FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5
FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5
FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5
FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows?
:confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
View 7 Replies
View Related
Mar 10, 2015
I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
View 8 Replies
View Related
Sep 5, 2005
I have 2 tables called Table1 ( has field called Mydata) and table2 (has Field called YourData).
Mydata has followind data 1, 2, 3 and yourData has 2,3,4
2 and 3 are comman data. how can I write query or type of function I can use to bring comman data.
I have used Union table that brings all record.
View 2 Replies
View Related
Nov 27, 2005
I am trying to add a common dialog to my form from the toolbox and get the following error 'you dont have the license required to use this ActiveX control. Is there any way I can sort this out? I am using Access 2002. I am getting desperate and any help would be much appreciated
View 3 Replies
View Related
Sep 27, 2006
Hi,
I did a search on common access errors and other errors that you would want to trap and got the following results:
Cannot update. Database or object is read-only
Operation must use an updateable query
General error Unable to open registry key
Could not find file
Could not use '(unknown)'; file already in use
Table 'tblTable' is exclusively locked by user 'Admin' on machine 'MyMachine'
Too few parameters. Expected 1
Either BOF or EOF is True, or the current record has been deleted
Item cannot be found in the collection corresponding to the requested name or ordinal
The search key was not found in any record
If you know any extra errors, can you please add them to this list?
Regards,
B
View 6 Replies
View Related
Mar 27, 2008
I am OK with a number field as I can put it in Excel and use =Mode().
But that wont do text such as lastname, suburb etc. I can do suburb by doing postcode but no such equivalent from some others I want to do
View 4 Replies
View Related
