Merging 2 Records Into 1 Record
Aug 24, 2006
Can anyone help me with this one ?
Attached is a spreadsheet showing data from an Access table. The Ivd Id field has produced 2 records.
I need to show this detail on 1 row. I have tried using crosstab queries to do this, but there a lot more fields than the ones shown and the processing required is too much.
I have colour coded the data on the spreadsheet to show the 2 records for Ivd Id =36926 (rows 2 and 3 on the spreadsheet), and how they would need to appear in an access query or table (row 16 on the spreadsheet)
View Replies
ADVERTISEMENT
Apr 9, 2015
My database was working fine before there was an update to my external data source.
This was my original Append Query
INSERT INTO RLCR4000_CODED ( FY, PayPeriod, ProjCode, DelTask, RegHrs, OTHrs, TotalHrs, FYTDTtlHrs, ProjDesc, TtlCost,
FYTDRegHrs, FYTDOTHrs, FYTDTtlCost, TaskDesc, EmpName, ORG, EmpId, CostCtr, PPBegDate, PPEndDate,
Level2Nodes, Level3Nodes, Level4Nodes, Level5Nodes, Level6Node )
SELECT RLCR4000_pp5_RAW.[Fiscal Year], RLCR4000_pp5_RAW.[Pay Period], RLCR4000_pp5_RAW.[Delphi Project Code],
[Code] .....
The data source changed and the result is now I have a bunch of "Near duplicate" records. I built this query and found the duplicates:
SELECT [RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode, [RLCR4000_-_Labor_Cost_by_Organi].DelphiTask, [RLCR4000_-_Labor_Cost_by_Organi].EmpId, [RLCR4000_-_Labor_Cost_by_Organi].CostCenterCode, [RLCR4000_-_Labor_Cost_by_Organi].RecordTypeCode, [RLCR4000_-_Labor_Cost_by_Organi].FiscalYear, [RLCR4000_-_Labor_Cost_by_Organi].PayPeriod, [RLCR4000_-_Labor_Cost_by_Organi].RegularHours, [RLCR4000_-_Labor_Cost_by_Organi].OvertimeHours, [RLCR4000_-_Labor_Cost_by_Organi].TotalHours, [RLCR4000_-
[code].....
There is a field called "RECORDTYPECODE" that was inserted in the external data source. It has one of four values : R, L, N, C
Ideally this is what I want
WHERE ((([RLCR4000_-_Labor_Cost_by_Organi].DelphiProjectCode) In (SELECT [DelphiProjectCode]
FROM [RLCR4000_-_Labor_Cost_by_Organi] As Tmp GROUP BY [DelphiProjectCode],[DelphiTask],[EmpId],[CostCenterCode]
HAVING Count(*)>1 And [DelphiTask] = [RLCR4000_-_Labor_Cost_by_Organi].[DelphiTask]
And [EmpId] = [RLCR4000_-_Labor_Cost_by_Organi].[EmpId]
And [CostCenterCode] = [RLCR4000_-_Labor_Cost_by_Organi].[CostCenterCode])))
Then R+L+N+C=
NOTE and in some cases the fields are null
I have attached a sample of the data. And the second tab is desired result. I have over 9000 near duplicates so it is imperative I create a formula to merge them
View 5 Replies
View Related
Feb 15, 2008
I am trying to import some data (job details for a machine shop), that is being exported from a CNC program.
The export has all the information we need, in a delimited format, but it spreads this over several lines. I also contains lots of junk we don't need.
Access imports this, but those lines then form an individual record in a tempory table.
I missunderstood what the 'UNION' query was, and whilst this has created nicely organised data, with just the stuff we need, it gives it over 3 individual records.
I either need to combine the 3 records from the output of the UNION query into a single record, or (and I suspect this is the case, as the UNION query doesn't actually do anything if I think about it), find a way of combining different fields from the temp import table into a single record in the query.
I've done a fair amount of searching on 'merging records', but can't seem to find a similar problem. Can anyone point me in the right direction?
View 4 Replies
View Related
Nov 2, 2004
I have 3 Dell laptops with data that was used in registrating participants for a conference (names, address, position title, email, and amount paid). Is there a way to combine the data from each computer to make one database? Please provide step by step instructions?
Mona
View 2 Replies
View Related
Jan 6, 2012
I have an Access table with a number of records which refer to the same [InvoiceNo] but with data in different fields. Normally, my database works like this:
1)Import data from Excel File to tblMain in Access
2)Import data from Excel File to tblVendorLine in Access
3)Import data from Excel File to tblDunning in Access
4)Query is sorting from two first tables only records that refer to the third one (by InvoiceNo)
5)We have only items from tblDunning with data from other two tables to be send (mail to the vendor).
The point is, that when I have relation between those three tables, I receive only few records, but sometimes - for example - tblDunning refers to 5 records in tblMain and to 7 records in tblVendorLine - but there is only 4 records related between tblMain and tblVendorLine, so query shows me only two records. Ive made a query for each table (tblMain & tblVendorLine - relation with tblDunning). Then make one query of those two queries. It append data to new table tblDunnAll
So for example the table would look like this after
Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 23/12/2011
233332 121212 GB18 16/12/2011
233332 121212 Complete K
134444 777777 GB12 Complete
100000 23333 GB11 Complete K
What I'd like to have is a table with:
Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 Complete 23/12/2011 K
233332 121212 GB18 Complete 16/12/2011 K
134444 777777 GB12 Complete
how to go about merging the data for each name into one record. There are lots of very useful posting about using SQL to delete duplicate but I can't find a posting with addresses this issue.
View 1 Replies
View Related
Jul 30, 2015
I am linking to an SQL database that is designed in such a way that the problem description is recorded in multiple records. See a simplified example below. I want to run a query that will merge the information in the PROBLEM_DESCRIPTION and return only one record for PROB_ID 55678.
PROB_ID ORIGINATOR DATE PROBLEM_DESCRIPTION
55678 Jim 01/01/2015 While cleaning my computer, I noticed
55678 Jim 01/01/2015 the screen was cracked. I called
55678 Jim 01/01/2015 the IT department and they said I had
55678 Jim 01/01/2015 to write a work order. However it
55678 Jim 01/01/2015 was rejected without reason.
View 7 Replies
View Related
Oct 24, 2007
Hi.
Stupid question, but I have Access 97 database with ssn in each record.
I need to create a comma-delimted text file with these ssns.
When I use the export text file with a query that lists each record's ssn, it does not produce comma-delimited file.
How?
Russ
View 5 Replies
View Related
Aug 28, 2014
Users are too lazy to check whether a patient record exists or not. Consequently, we have a number of duplicate records that I would like to merge.
My problem is finding a way of linking 2 subforms to a parent that is not based upon a query.
On the parent form, I want two Comboboxes called 'Patient_to_Keep' and 'Patient_to_Discard'.
The two subforms called Frm_DUP_Patient1 and Frm_DUP_Patient2 should then display the data of the respective combo boxes.
Once I have this working, I can write the necessary code to compare and update the data as required.
View 5 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
Nov 24, 2014
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
View 9 Replies
View Related
Feb 5, 2007
My network was using Access 2000 and Word 2000. We recently upgraded to Access 2002 and Word 2002, but now none of my merge forms are working. I don't get any error messages, and the merge function does open Word, but all I get is the original source document and not the actual merge information. I checked my VB references and nothing is glaringly missing. What's the deal?
MDW
View 4 Replies
View Related
Aug 24, 2005
I am new to Acess, I am trying to merge 2 Access DBs and am not sure of the steps to accomplish this, if it can be accomplished and/or are there any tools to reach the same goal.
View 5 Replies
View Related
Aug 24, 2007
Hi, I'm very new to access so sorry if this request seems a bit stupid.
I'm trying to get one csv file for a database of cars so I can upload it to my open-realty account.
In one table is the car ID and there are fields like Price, Extras, Mileage etc associated to that ID. (this is how I want everything to stay) I would like additional fields like Photo 1, photo 2, photo 3....photo 25 related to each car.
In the second table there are photos of the cars but each photo has its own unique ID but a field that relates to the ID of the car it is associated to.
How do I merge the two tables into one simpler version?
I've attached screen shots of the two tables.
Thanks for any help.
View 7 Replies
View Related
Oct 25, 2007
Hello-
We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.
Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??
THANKS
View 7 Replies
View Related
Jan 27, 2005
Hello
I need to merge a set of Access tables. I am creating a database which will hold a large amount of records (upto 250 000 in one table). I am importing the data from an excel spreadsheet. As each spreadsheet is limited to 65000 records I had to save the file in 4 different spreadheets. However in Access I need all these files saved in one table. Can anyone please help me?
Thanks
Nathan
View 4 Replies
View Related
May 6, 2006
Hi I have search the forums for an answer to this..i have 4 fields address 1, 2,3,4 and i would just like to all all the information to one field call address..how can i merge the information..please bear in mind i am useless at access..i thought there might be a way of copying the columns and just having a merge function to do so ..but cant find anything...please help..Williebear:(
View 4 Replies
View Related
Jun 14, 2006
Hello first post here, I need some help!Sorry not sure if this should go under queries or tables.Anyway I'm working with quite a large access db (64mb).I want to merge data from several tables into one table.Its a supermarket db (not a real one). Its got a customer and product tableIts also got a table called agg_c_special_sales_fact_1997with the fields customer_id and product_id this shows what customers bought what product.I want to merge the data from customer, product and agg_c_special_sales_fact_1997 into one big table. I want the merge to be permanent so I can see the which customer (and all their details) bought which product (all details) in one big table. I ve been trying to do this all day (im quite new to SQL) I copied the data in agg_c_special_sales_fact_1997 into a new table "new_sales_fact". I added a row called brand_name to that I want to copy from the product tableSo far I ve come up with this:SELECT product.brand_name INTO new_sales_fact FROM product, agg_c_special_sales_fact_1997WHERE product.product_id = agg_c_special_sales_fact_1997.product.id;unfortuantely access asks me enter a parameter value when I run the query.Could this becuase agg_c_special_sales_fact_1997 is huge, its got 86,000+records?Can anyone help? I'd be most greatful this has been driving me insane all day!!thanks in advance! P.S the database is the test database that comes with Mondrian......P.P.S I know its not good practice to have all data in one big table but for the purposes of what im doing it will simplfy things a lot!
View 4 Replies
View Related
Aug 14, 2007
Hello all
I'm not really an Access programmer, but I maintain a couple of databases for my own use (music collection etc.). I hope no-one minds me posting here :)
I've made a bit of a mistake. I had two copies of a database on different machines, and I entered new data into both of them.
Is there any way of merging two tables, discarding any records which are identical?
Many thanks in advance for any responses!
Cheers
Gareth
View 6 Replies
View Related
Nov 14, 2007
Hi all :) I have 22 tables in my Access database, which is my catalogue data. I have it set up on dreamweaver. Now tho, i need a search engine going thro it, i have a script in access/dreamweaver that works. Only thing is, that the script only works thro one table. Now, is there some way to merge tables into one? Not just copy and paste, but all have the same specific name that i maybe able to link? (ie Name, description, code) Any ideas? A tutorial link would be great if anyone has come across a similar thing, thx!
View 1 Replies
View Related
Aug 25, 2005
I have frequented this board on several occasions, and have used it extensively to help me on my project. But I am at a situation that I cannot seem to find a proper solution for, and feel like I need to solicit some input from more experienced people.
Problem: What I want to do is create a form utilizing two queries that will not only display the shoes and comments, but as I tab from record to record (each record a different shoe), the form will also display the total minutes for the shoe. (frmShoes in the database shows what I ultimately want)
Background: I am creating a database to track my exercise routine and jogging times, along with other variables. I have three tables (I deleted all the other extraneous garbage to alleviate any potential for confusion):
tblRunSession
Run Session ID pk
Shoes ID
-one-to-many w/tblLaps-
-one-to-one w/tblShoes-
tblLaps
Laps ID pk
Lap Number
Lap Time
Run Session ID fk
tblShoes
Shoe ID pk
Shoe Name
Comments
I want to use a form built off tblRunSession to enter a new day of running. On the form I would enter multiple laps, and minutes each lap was completed, along with the shoes (only one pair used per day) I wore that day. I accomplished the input utilizing tblLaps as a subform to tblRunSession.
I tried creating two queries: one (Query 1) from tblshoes, and the other (Query 2) from tblRunSession and tblLaps. I summed the individual lap times in Query 2 for each shoe. But when I try to create a third query based on Queries 1 and 2, it gets all messed up and confusing. I tried to use dlookup, but I quickly found that was beyond my knowledge level. I think some of the problem I’m experiencing (besides my limited brain capacity) is the fact that I am trying to use three tables to pull data from, and trying to make it all mesh. I’m sure it can be done, but I’m at a loss.
I attached what I have been working on in case anyone wants to look at it and set me straight. Thanks in advance for any suggestions or ideas anyone has to offer.
View 6 Replies
View Related
Dec 10, 2006
Hi,
I have a few tables with the same row and column headings but contain different data in their cells. Is there a query whereby I can basically merge everything to give me one view with everything in it?
Thanks
Greg.
View 1 Replies
View Related
Apr 9, 2007
Hi, this is really puzzleing me!
What i want to do is take a field from one table and a field from another and join them together to make a single field.
I know this wouldnt work in practice without selection statements etc so only one field is selected, but if you could just help me on this code i would be greatful!
For example, assuming that the tables tblMyDatatable, and tblOtherData consists only of one record with each containing a letter of "y" then:
SELECT [TheData FROM tblMyDatatable] & OtherData AS ThisQuery
FROM tblOtherData;
Should produce the following:
ThisQuery
yy
It dosent! Can anybody help?
View 2 Replies
View Related
May 3, 2007
I have an excell spreadsheet with member names
the member names are duplicated up to 6 times each depending on how many months from october - march they have been members.
The excellspreadsheet looks like this
Member name Member number October care level November care....
triscuit83 1234 1
Triscuit83 1234 1
Is there a way in access I could make triscuit 83 show up once and have all the care levels stay on the same line.
Please let me know if there are any clarifications needed.
I need help hardcore
****edit****** the example doesnt show up formated right basically 1 will be in the october column the first time and a 1 will be in the november column the second line.
View 1 Replies
View Related
Sep 25, 2007
Hi,
I'm an access newbie and was wondering how to merge two tables. for example, i have:
table1
col1 col2 col3
and
table2
col1 col2 col3
They have the same column names, I just want to put the data from the first table in the second one. I don't care about duplicates for the moment, there is no primary key.
Thanks
View 3 Replies
View Related
Jul 29, 2006
I have 2 form, I want to merge them into one. they do different things but they interconnect with each other in one field.
for example one form has customers, the other form is sales.
I want to merge them so I could see what the customer has bought over time.
any suggestions, it is just I cannot get them sincronized with each other.
View 2 Replies
View Related
Jan 9, 2005
Hi,
i am required to merge 2 tables and to be able to present the data out in a report. the prob is i do not noe how. both the tables do not haf an unique field which means all entries in each field are repeated. the size of each table is different. is it a must for the 2 tables to be the same size?the fields for the 1st table are as follows:Country, Company Code, Company, Product Group, Material, Date, Actual Qty and Final Forecast. the fields for the other table are: Country, Company Code, Company, Product Group, Material, Date, Sales adjusted forecasted and final forecast. the "date" field from each table are different. the 1st table will show the actual qty of goods delivered while the latter will show the forecasted qty. i am required to be able to extract out the data for eg to show the actual qty delivered from jan to jul and the forecasted value from aug to dec.
pls help....
View 2 Replies
View Related
