Queries :: Merging Records Into One Record?
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 Replies
ADVERTISEMENT
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 3 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
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
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
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
Apr 8, 2008
Hi all - new here - hope to get some help :)
Situation: I have a bunch of individual databases (approx 70) - each holding one table called "Meter" with some GPS reads and additional information about the GPS point.
Am looking for solution to create one big database file holding all GPS reads.
One idea i have is linking all tables into a new database and run a create table query and then an append query.
Is there a way to somehow have the one append query run automatically on all existing linked tables (remember approx 70 tables so i really want to avoid of creating 70 append queries ;) )
To make it more complicated: data sets in the original individual databases could be modified or new data could be added. From what I know linked tables are updated but is there a way of keeping the my big, newly created (appended) database up2date automatically as well?
Hope it all makes sense?
Thanks a lot in advance
Regards from Ireland
View 7 Replies
View Related
Nov 15, 2013
I am trying to update an existing table in Access with current information that I have imported into Access from excel into a separate table. I was able to use an unmatching query and append all records that were not already in the original table. But there are records in the update table that match the original table, and there in information in fields within that records have new information.I need to find the records that match in the two tables and update the missing data.I am able to create a query that displays the matching records but when I run it as an update query the information in not entered into the original table.
View 7 Replies
View Related
Oct 2, 2013
I have two column of expressions
1=(<(Now()+365)-([Service Interval]*30) Or <(Now()*365)-(([Service Interval]*2)*30))
2= (<(Now()+365)-(([Service Interval]*2)*30) Or <(Now()*365)-(([Service Interval]*2)*30))
I want to merge these two columns into one but then have two entry's not one !
And then I can sort by date in a report (i am trying to sort all service due for the customer in the next year)...
View 10 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
May 15, 2015
I have a table which has each claim as a single record. A client wishes us to provide the data in a format that lists the each procedure on its own line. I have attached a highlevel example of what the data currently looks like and what the query results should look like.So I need to create a query that would repeat the Claim Number and place all the Procedure fields into one field.
View 1 Replies
View Related
Oct 28, 2013
I have a large database which runs various reports however i am struggling with the last bit to get this report working.
Under each "Works Order" is a set of scrap which is associated with this batch. Instead of rows of data i would like to add all the "reason for scrap" to a single text box for each works order:
i.e. WO: 259953 | Scrap: Sub contract inner open; Untraceable short; short to plane etc etc.
I have attached an image in its current format.
View 1 Replies
View Related
Sep 4, 2006
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
View 3 Replies
View Related
Jun 12, 2013
Currently am trying to get together two separate Access Databases into one, which is the way it was always supposed to be.
Basically, this person accidentally created a duplicate of the database in question, and has been updating both independently at different times, meaning that whilst a lot of the database information is duplicate, there are some instances where each separate database copy has unique information on both existing contacts, and new unique contacts.
The database itself is about schools, and has tables concerning information such as calls made, contact details, school details, etc etc. Obviously all of this needs putting back together into one set of tables in one file.
I've been messing around with append and import functions, but am having issues with duplicate ID fields with append queries, and a general lack of direction on the best way to go about fixing this problem, if its even possible.
Here's an example of what i've been trying to do:
INSERT INTO Contacts ( ContactID, ContactTitle, Name, Surname, JobTitle, TelephoneSTD, Telephone, FaxSTD, Fax, Locale, SubjectKeyword, SchoolID, Reply received] )
SELECT [1Contacts].*, [1Contacts].ContactID, [1Contacts].ContactTitle, [1Contacts].Name, [1Contacts].Surname, [1Contacts].JobTitle, [1Contacts].TelephoneSTD, [1Contacts].Telephone, [1Contacts].FaxSTD, [1Contacts].Fax, [1Contacts].Locale, [1Contacts].SubjectKeyword, [1Contacts].SchoolID, [1Contacts].[Reply received]
FROM 1Contacts;
View 9 Replies
View Related
Feb 27, 2014
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT"
The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
View 2 Replies
View Related
Oct 22, 2014
Say you have multiple excel sheets where forecasting is done daily.
For example, sheet one headings: Depot, department, location, 01/09/10, 02/09,10, 03/09/10
and then second sheet is similar but: Depot, department, location, 02/09/10, 03/09/10, 04,09, 10 - as you can see each day the forecast starts a day after and ends one date late (14 days each in the real one)...
How to I join all these forecasts into one table; vertically with each forecast identified by the day it start for analysis later.. rather than doing cross-tabulate which would take ages, I just need to keep adding more excel sheets but the headings change as they are dates...
View 7 Replies
View Related
Aug 3, 2013
I have the two tables in my access DB:
Property Information
Lease Information
I need to write a query which shows where a property does not have an associated lease, or where there is no current lease associated with it.
Property Info:
PropertyID
Address
Lease Info:
LeaseID
LeaseStartDate
LeaseEndDate
PropertyID (linked field to property table)
How to show expired / not current leases, but can't seem to show where there has never been a lease on a property.
View 4 Replies
View Related
Mar 27, 2013
I have record like below:
id s q
1 11 11
2 14 15
I want to sum record 1 and record 2 and put them in next record.
View 2 Replies
View Related
Jan 22, 2014
How can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?
View 3 Replies
View Related
Sep 29, 2014
I have a file of transaction history from the accounting system. All of the payroll cash payments are coded as ZG. Payroll accruals are coded as ZC. I need a sum of payroll accruals by department that have the same date or later than the last payroll cash payment. How do I write that query?
View 1 Replies
View Related
Jul 1, 2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
View 5 Replies
View Related
Aug 8, 2014
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
View 2 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
