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
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?
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)
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
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.
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.
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
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.
Hi,
I've asked this on a few other websites with limited or no success.
this is from one db. this table originally had about 20-30 different fields,
WarehouseNum ItemNum load_date Shipped
101 31 6/26/2006 200
101 31 6/27/2006 180
101 338 6/26/2006 162
101 338 6/27/2006 357
this table is from another db. Notice how WarehouseNum is '9' instead of '1' and itemNum is '000'. This is just one of many impediments I face. As well, none of the table fields are the same, I changed them here to make it easier to work with.
SupplierNum ItemNum WarehouseNum PlannedDelDate DeliveredQty
869747 00031 901 6/26/2006 250
869747 00031 901 6/27/2006 300
869747 00338 901 6/26/2006 490
869747 00338 901 6/27/2006 490
The goal here is to query the two simultaneously, perhaps with linked tables, type in an ItemNum using a parameter and know for instance that on the 26th, we received 250 item31 and shipped 200 for a remaining balance of 50. The 50 is what I'm after. Basically, my company uses one program for sales and another for purchasing and absolutely nothing for inventory management. I started with this company about 3 weeks ago and unless i can implement some kind of temporary solution, it will drive me insane.
Advice? Best approach? As well, this db will use data on two different servers
Thanks for the help.
As well, my access experience is limited and our company I.T. is seriously backlogged, besides which, I would like to learn how to do this myself.
Thanks again
I have an Access spreadsheet that I can't get to merge with a word document. It freezes up every time I try and the only message I get is overflow. Does anyone know how to fix this problem?
View 3 Replies View RelatedI am building a database for my job. I am trying to find a way that I can mail merge an Image that store in Access to a Word document. I tried doing it by creating a field in Access for the picture and its field type was set to OLE Object and I placed the merge field in its appropriate area in Word. When I go to merge it, its comes up with a message that says "Long Integer or Binary Number". If anyone has an idea as to how I can make this happen....please let me know, your help would be greatly appreciated.
Thank you
I really never use Access but a client gave me a database with a couple thousand records in it. Two columns were "Area Code" and "Home Number". What I have to do is merge those two so that the area code and number appear in a single column. How do I do this?
View 6 Replies View RelatedHello All,
I need some assistance.
First a little background on the database:
The back end is in AQL the front end is in Access.
The main form is called the "Register", here they enter claim information and if they need to create a printed loss they click on a button called "Loss Notice" This brings up another form which is filtered based on the claim number in the Register. Here they fill in additional information. On this form they have the option to print the Loss Notices by clicking on a button that Opens up Microsoft Word and all the fields are mapped to the Word Template where they need to be.
On my computer when I set this up it works great. I test it all the time and works perfectly.
However when the users try to do this from their computer they are getting a pop up box asking for the claim number. The Loss form is filtered and should only pull that claim number.
Why are they getting this pop up box and I am not?
If you need any additional information I will try and give that to you.
Thanks for trying to help!
Apologies ... but I am quite new to Access. My database contains about 1000 peoples' addresses.
The problem I have is that I need to merge, say, 20 of these addresses into a word document. In the word document I can select records using the "Surname" field but it will not give me the option to select more than 6.
I am sure there must be a way that I can select more than 6: I have tried separating the surnames with a "," or "or" but nothing seems to work!
Help, please!
Thank you,
Emma
Hi,
how do i get the current record form access 2003 to merge the data into word 2003 template that I created.
The code I'm using is :-
Private Sub Command313_Click()
Dim oApp As Object
Dim doc As Object
Dim strDocName As String
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
strDocName = "K:Supported Living ServicesdatabaseDB-Personnel.dot"
Set doc = oApp.Documents.Open(strDocName)
doc.FormFields("Title").Result = Forms!Personal!Title
doc.FormFields("fristname").Result = Forms!Personal!firstname
Set doc = oApp.Documents.Add(strDocName)
End Sub
I'm using 2 fields here but will be adding a lot more once I got it working.
Thanks inadvance
ps (only a novice at access)
Any simple code that merges multiple pdf documents that they are willing to share. I don't seem to be able to find anything that works so far.
View 2 Replies View RelatedSay 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...
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
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 RelatedHi, 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.
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
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
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 RelatedHello 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 RelatedHello 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
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