I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
View RepliesDear all,
I would like to seek your help on this problem.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Pls help. Codes are most welcome too.
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description
If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it
2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
Hello All,
I have a database where a user imports data into a table monthly. She saves the Excel file and then clicks a button in Access to import the data. My instructions say to only click that button once, but so far, she has clicked it THREE TIMES, thus importing the data three times. How can I prevent this? Here is a copy of my import code:
Option Compare Database
Option Explicit
Public Function ImportTableData()
On Error GoTo ImportTableData_Err
Dim strPeriod1 As String
Dim strMM1 As String
Dim strYY1 As String
Dim strYYYY As String
Dim strFormName As String
Dim strMDPath As String
Dim strMDFileName As String
Dim strDCPath As String
Dim strDCFileName As String
'No Warnings
DoCmd.SetWarnings False
'Import Current Month data in a temporary table
DoCmd.OpenForm "frmEnterAgingDates", , , , acFormReadOnly, acHidden
strFormName = "Forms!frmEnterAgingDates"
strPeriod1 = Forms!frmEnterAgingDates.Period1.Value
strPeriod1 = Format(strPeriod1, "mmddyyyy")
strMM1 = Left(strPeriod1, 2)
strYY1 = Right(strPeriod1, 2)
strYYYY = Right(strPeriod1, 4)
strMDPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300116405.100-AR Due from PAR Plans NASCO"
strMDFileName = "MD AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
strDCPath = "S:FinanceAccounting OperationsNational AccountsAccount Recs116405.100 and 117630.300117630.300-AR NASCO Refund Due from PAR Plans117630-2007"
strDCFileName = "DC AR Due from PAR Plans_" & strMM1 & strYY1 & ".xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strMDPath & strMDFileName, True
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblData_temp", strDCPath & strDCFileName, True
'Append tblData_temp to tblData
DoCmd.OpenQuery "qryAppend_tblData_temp_to_tblData", acViewNormal, acEdit
'Delete any blank rows that may have resulted from the import
DoCmd.OpenQuery "qryDeleteBlankRows", acViewNormal, acEdit
'Delete the temporary table
DoCmd.OpenQuery "qryDelete_tblData_temp"
Beep
MsgBox "The file has been successfully imported", vbInformation, "Files Imported"
ImportTableData_Exit:
Exit Function
ImportTableData_Err:
MsgBox Error$
Resume ImportTableData_Exit
End Function
Thank you for your help!
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
View 2 Replies View RelatedI need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies View RelatedI have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
I've got a problem I can don't know how to solve (since I'm a complete access noob). I've got an acces table that is populated with around 1000 entires, all starting with letter N, followed by numbers. Then I've got another table from last year which is also populated with 1000+ N entries AND 1000 V entries.
I would like to trasnfer/copy all V entires from last year table into this year table. I'm sure this is possible but I don't know how. In short, I would like to copy 1000 rows of last year data into this year table/data. What's the easiest way (if there is any)?
Hello everyone,
How can I transfer or import data (NOT TABLES) from two related tables. The two tables have parent-child relationship and have multi fields. I tried to use the import command but it imports tables rather than data. but I want to move data not the whole table. I can use copy and paste command but I couldn't copy both main data and sub data at the same time. I could be able to copy the main data all at once and paste them but they don't take the sub records along with them unless I copy them record by record and pate them to the corresponding file, which is very time consuming for a very big data.
Is it possible to create a command that imports data from two tables at the same time? Note that these tables I am trying import data from have the same format but different field names.
These data are collected using the same application but by different users at different locations and I want to transfer them to our main data base. If any of you can help me with this I really appreciate. Thanks.
Girma
I have 2 tables that have the same structure and same column names - Table A and TAble B. If field1, field2, field3, field4, and field5 match - I want to see that duplicates exist.
How do I do it with 2 tables? I know the query wizard check for duplicates can do it in one table - but I need to compare the two.
thanks.
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
View 3 Replies View RelatedI'm trying to perform a massive data dump. I have about 1,000 records that need to be added into my database. This is coming off an Excel spreadsheet.
I don't know if this is possible:
I have a table, tblTorders, which is linked to the table, tblItems.
When I open the tblTorders, I can click on the plus sign to expand and see the related data in tblItems.
How can I import data to populate the fields in both of these tables? I can't use the import function because you can only select to import it into one table.
Do I do it through a query.... I'm not sure where to go on this one.
Thanks so much
When i try to import data from an excel file to an existing table in MS Access database i get a pop up saying "Access was unable to import data". When i click OK it says "finished importing data" .But when i go to the table there are no new records added. Can anyone please help me with this ? All the fields in the excel file are as they are in the table.
View 1 Replies View RelatedI have a project concerning lists of external files. The spread sheet holds the source data on a document per line basis. Each row contains vital data ClientID, ClientName, DocDate, DocDescription, MainPage, Page1, Page2, Page3.... up to Page 585
I have done previous work where importing up to 50 columns has never been an issue.
For some reason in this case I am unable to import more than up to 255 columns and also I lost all the reference data past column 30.
I have tried importing directly to a SQL Server Db - same issue
Access 2010 and this in 2013
We are a travel company and I am just setting up a new database with two tables - [Client_Table] & [Enquiry_Table].
Most exisiting clients call in when they want to make a new enquiry so the 'user' can go it to a form which creates a 'new enquiry' for that client.
We also download 'new enquiries' from our website. This data includes info that goes in to the [Client_Table] and [Enquiry_Table]. They download in to a XL spreadsheet. Currently I copy and paste the data (not sure if there's a better way) from the XL speadsheet in to a query. This query creates a new client record & a new enquiry record which are both linked by a primary key [Client_ID].
This all works however the problem I have is that sometimes existing clients enquire through the website. They do fill in a field to say they're an existing client but if I paste them in to the query as explained above it creates a duplicate client record.
The only way around this I can think of is it to take out any exisitng clients from the XL spreadsheet first, search for their Client_ID and then paste these enquiries seperately with their Client_Id's in to different query which only adds a new record to the [Enquiry_Table] and links them to their exisitng record in the [Client_Table].
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
I'm fairly new to Access, but have set up a database containing 8 normalized tables. I would like to know if it's possible to update all those tables by importing an Excel file into each table, or importing the Excel file into one Access table and then appending that table to those 8 tables. AND STILL MAINTAIN THE RELATIONSHIPS. The Help directory only talks about importing or appending into one table.
View 7 Replies View Relatedhow to import data from excel 2007 like' suppose the field in DB table is A, B & C and the same is there in excel 2007, now i like to get B & C filed data from excel to DB table directly (import) which may be through button at form, because i have given my user the accdr file so they cann't go into the table and paste record.
View 1 Replies View RelatedI have a spreadsheet of generators with associated data: Make, Model, etc.
When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).
Example:
tblGeneratorMake
GeneratorID (PK)
GeneratorMake
John Deere has PK of 1
Kohler has PK of 2
If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".
I am building a database for a coworker. For one table, he'd like to extract data from different companies' financial reports, specifically a Schedule RC-B (schedule of securities). The format it comes in is PDF, but there are options to download XBRL, SDF and Taxonomy. My computer doesn't recognize any of these other formats. Plus, I'm guessing each company will have a slightly different format, so I don't know if there'd be an efficient way to set up an automatic import. Any way for importing/extracting data from these files?
View 2 Replies View RelatedI'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies View RelatedI'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.
What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.
What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.
An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.
I have 3 tables
tbl1 :
NameID
Name
tbl2 :
CatID
Category
tbl3 :
ProdID
CatID
NameID
Remarks
How can I avoid duplicate records in tbl3? Since remarks can be Null? product is later used in tblInventory...
First off, its been a long time since I have been here (since sometime in 2003 LOL), partly because I didn't have to create a new db in a while. Now, I have been handed a project and come across something new.
OK, here is what I want to do. I would like to be able to dump data from an Excel file into existing Access tables (two to be more specific). Now, I also have relating tables that add data into certain fields. I tried using a query to try to group data ad hopeful able to dump data through the query. I noticed that I couldn't import data through a query. Just for kicks, I tried to pull data through table and noticed that I can only create a new table, not able to update and existing table.
How would I go about performing this task? I have never attempted anything in the past.
Thank you in advance for your considered help,
Steve
I'm looking to import huge excel sheets in access, but normalization process in Access has forced me to divide all the columns in Excel to about 12 tables in MS Access.
How how could I import data from excel sheet columns to 12 different tables?
I am importing information into a table from excel. The number is formatted correctly in excel and is displayed as 10309976464180, but when it is imported into access it displays as 1.030998E+13. I have tried all of the possible formatting for numbers but nothing corrects this, and if I change to Long Integer it actually removes the numbers. What is the correct formatting in Access to get these numbers to display correctly?
View 2 Replies View Related