append query and duplicates

I have a two part question.  Access 97.

I want to know first if I can append from a Excel file that is a .csv, or do 
I have to convert it first to a .xls?

I know how to append from a .xls file.  

The .csv file (before conversion to .xls) is downloaded from another 
machine.  each time the the file is downloaded (every month), it contain the 
total data, which will consist of hourly readings.  Each download has the 
previous month's data, with the present month's data added to end of file.  
There is no set limit to number of months, that is, at present, my download 
for December 07 contains october and Novermber's data as well as December's, 
whereas my November's download had just oct. & Nov's data.  I can manually 
delete the unecessary data, keeping the current month'd data only.  This 
takes a little bit of work, and I want to get this setup so this work is not 
necessary.

My thought was to take the .csv file, append it straight-away (or after 
converting it to .xls)  to my table in Access.  The Append query needs to be 
setup to ignore duplicate info, which will allow only current data to be 
appended.  I'm just not sure how to do this.  

My table has an autonumber id field, time field, date field, and various 
"other" fields.  I won't be able to use the time field, as each day of each 
month will have the same time of readings.  The date field is where I think 
my criteria needs to be enetered, but I don't know what expression I need to 
use to keep the duplicate info from being appended to my table.

Or would it be better to allow the duplicates to be appended along with the 
new data, then just run a delete duplicate query?  Any help on the setup of 
this type would be appreciated.

Rick

 

0
Utf
1/3/2008 3:35:00 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
997 Views

Similar Articles

[PageSpeed] 6

To import a csv file, use the TransferText method instead of the 
TransferSpreadsheet method.

It would be a good idea to create an import specification for the import so 
you can get the field names and data types like you want them.  To do this, 
do an import manually (Files, Get External Data, Import).  When you get the 
import wizard, click on Advanced.  In there you can set up the 
specifications.  Then click on Save As and give the spec a name.  You then 
use the spec name in the TransferText (see VBA Help for details).

I would agree with importing the csv file into an existing table and using 
an append query to move the data to your production table.  There are a 
couple of ways you can filter the data.  You could use a form and have a text 
box for the user to enter the month(s) to import and reference the text box 
in the append query. Or if you want to get all records, regardless of month, 
that are not in the production table, you could use a sub query to filter for 
records that are not in the production table.  something like:

Where MyDate Field Not IN SELECT MyDateField FROM Production Table

-- 
Dave Hargis, Microsoft Access MVP


"sparky" wrote:

> I have a two part question.  Access 97.
> 
> I want to know first if I can append from a Excel file that is a .csv, or do 
> I have to convert it first to a .xls?
> 
> I know how to append from a .xls file.  
> 
> The .csv file (before conversion to .xls) is downloaded from another 
> machine.  each time the the file is downloaded (every month), it contain the 
> total data, which will consist of hourly readings.  Each download has the 
> previous month's data, with the present month's data added to end of file.  
> There is no set limit to number of months, that is, at present, my download 
> for December 07 contains october and Novermber's data as well as December's, 
> whereas my November's download had just oct. & Nov's data.  I can manually 
> delete the unecessary data, keeping the current month'd data only.  This 
> takes a little bit of work, and I want to get this setup so this work is not 
> necessary.
> 
> My thought was to take the .csv file, append it straight-away (or after 
> converting it to .xls)  to my table in Access.  The Append query needs to be 
> setup to ignore duplicate info, which will allow only current data to be 
> appended.  I'm just not sure how to do this.  
> 
> My table has an autonumber id field, time field, date field, and various 
> "other" fields.  I won't be able to use the time field, as each day of each 
> month will have the same time of readings.  The date field is where I think 
> my criteria needs to be enetered, but I don't know what expression I need to 
> use to keep the duplicate info from being appended to my table.
> 
> Or would it be better to allow the duplicates to be appended along with the 
> new data, then just run a delete duplicate query?  Any help on the setup of 
> this type would be appreciated.
> 
> Rick
> 
>  
> 
0
Utf
1/3/2008 4:38:03 PM
Hi,

No need to convert to Excel. You can import the CSV file into Access, or you 
could link table to that file if the file name stays the same.

To prevent duplicates, create a new index that combines all the fields which 
would make the data unique except for the autonumber field. Them make this 
multi-field query a unique index. That will prevent duplicates from appending 
to the table. However if you already have some dupes in the table, you'll 
need to clean them up as Access will not create the unique index if there is 
already a problem.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"sparky" wrote:

> I have a two part question.  Access 97.
> 
> I want to know first if I can append from a Excel file that is a .csv, or do 
> I have to convert it first to a .xls?
> 
> I know how to append from a .xls file.  
> 
> The .csv file (before conversion to .xls) is downloaded from another 
> machine.  each time the the file is downloaded (every month), it contain the 
> total data, which will consist of hourly readings.  Each download has the 
> previous month's data, with the present month's data added to end of file.  
> There is no set limit to number of months, that is, at present, my download 
> for December 07 contains october and Novermber's data as well as December's, 
> whereas my November's download had just oct. & Nov's data.  I can manually 
> delete the unecessary data, keeping the current month'd data only.  This 
> takes a little bit of work, and I want to get this setup so this work is not 
> necessary.
> 
> My thought was to take the .csv file, append it straight-away (or after 
> converting it to .xls)  to my table in Access.  The Append query needs to be 
> setup to ignore duplicate info, which will allow only current data to be 
> appended.  I'm just not sure how to do this.  
> 
> My table has an autonumber id field, time field, date field, and various 
> "other" fields.  I won't be able to use the time field, as each day of each 
> month will have the same time of readings.  The date field is where I think 
> my criteria needs to be enetered, but I don't know what expression I need to 
> use to keep the duplicate info from being appended to my table.
> 
> Or would it be better to allow the duplicates to be appended along with the 
> new data, then just run a delete duplicate query?  Any help on the setup of 
> this type would be appreciated.
> 
> Rick
0
Utf
1/3/2008 4:49:02 PM
Reply:

Similar Artilces:

query destination field
How do you change a destination field of a query in Excel? I have the data from the query being sent to cell A4 but need it to go to cell A1. I tried the "edit query" option but with no luck albert I think you can just delete Rows 1:3 and it will adjust accordingly. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "albert" <anonymous@discussions.microsoft.com> wrote in message news:14E1E67B-F7A0-4FD3-A78E-C4E1CCC22453@microsoft.com... > How do you change a destination field of a query in Excel? I have the data from the query b...

Duplicate emails with different text body
Hi there all, I am having an issue with deduplicating two email messages. We use and MD5 hash of various mapi properties, including the text version of the body of the email. One of the messages comes from a PST file, the other is exported directly from an exchange EDB file. The messages are identical in every sense, same sent date and time, same author, same recipients same subject and the RTF version of the email is exactly the same. However, the text version of the body of the email differs slightly in that the section of the email that refers to "-----Original Message-----" i...

Simple query??
Hello All I have been wrestling for ages now with a query that I thought would be simple .. but can't get the output I want! I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY (and others). The field SUPPLIER is not a unique (key) field, but in fact it almost is: ~95% of the records in the table contain unique values in this field.Of the remaining ~5%, about half of the records for a given SUPPLIER value contain the same value of AUTO CATEGORY, and the other half contain different values for AUTO CATEGORY. I want a query that will return the number of diffe...

Cashflow query to return previous valid field if date has null val
Hello guys! Hope someone can help as usual! I have designed a simple query to control my cashflow, pulling data from a topay_query and toreceive_query. I have managed to make all the calculations, no problem. The thing is that I have an "Accumulated payed" and "Accumulated received". When there's no moviment in a day these fields come out blank. How can I do that when there's no movement, the query would consider always the value of the previous valid field? THanks mate! How are your queries set up? Are you linking by day? Is one of your criteria to only ...

Outlook Mail Item Duplicate "Display Name" Problem
Dear all, I'm currently writing a Persona Menu smart tag in Outlook 2003. I use dynamic caption and intend to modify the "Additional Actions" sub-menu depending on the contact(in the MAPIFolder) that the tag is referring to. Problem occurs in the mapping of the tag and OL contact. All information I can find from the smart tag action DLL(with ISmartTagAction2 interface) is just the tagged text string from get_VerbCaptionFromID2() but not the entity object that calls the persona menu. So my way to complete the chain is to : 1) search the sender and recipients' "display...

duplicate incoming messages
When opening Windows mail today, I have been getting duplicate emails. How do I fix this? -- God Bless! Vicki Lang v.lang@live.com Tools | Accounts. Is this account listed twice per chance? If not, turn off e-mail scanning. Turn off e-mail scanning in your anti-virus program. It is a redundant layer of protection that eats up CPUs, slows down sending and receiving, and causes a multitude of problems such as time-outs, account setting changes and has even been responsible for loss of messages. Your up-to-date A/V program will continue to protect you sufficiently....

Keeping duplicate records
There are many postings about deleting duplicate records but how in excel can I find only duplicate records. I have large spreadsheets that I want to find duplicate records maybe within a single column or if any 2 records in any field match and highlight these. Dear Steve Pay a visit to http://www.cpearson.com/excel/duplicat.htm you will find some very useful stuff there >-----Original Message----- >There are many postings about deleting duplicate records but how in excel can I find only duplicate records. I have large spreadsheets that I want to find duplicate records maybe withi...

Queries and Now()
Hi Using Access 2007. I have a database in mdb format that records the information of customer sales on a web site. In the OrderDate field I am using Now() to record the date and time the order is placed. I also have a query which displays only currently open orders. When I view either the table or query data locally within Access the date/time information of each order displays correctly. Likewise, when I view the table data online via an ASP VB web page, the date/time information of each order displays correctly However, when I view query data online via an ASP VB web page, the da...

multi record query
I am taking 4 fields from a linked AS 400 database, the 4 fields are IDAREA = T IDAISL = RA IDBAY = IL1 IDLEVL = 9 I need the word "TRAIL19" to be my parameter for the information I need for a query to fill in the form and report Is there any way to combine these 4 feilds? On Mon, 11 Jan 2010 20:09:01 -0800, Norm <Norm@discussions.microsoft.com> wrote: select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField from myTable (of course you change myObjectNames to yours) -Tom. Microsoft Access MVP >I am taking 4 fields from a linked AS 4...

Text "=SMTP:" appended to email addresses
Not sure if this is a server or an Outlook issue, so please bear with me if in wrong place! I've got one user (Outlook 2007) who is finding that when she replies to an email, the text "=SMTP:" is appended to the front of the destination email address, which renders the email undeliverable. ? Any ideas ? "Baz" <Baz@discussions.microsoft.com> wrote in message news:4E9CC0D5-B7C9-44AF-BFB9-54190CEBA96B@microsoft.com... > Not sure if this is a server or an Outlook issue, so please bear with me if > in wrong place! > > I've got one ...

Duplicating a record leaving one field blank for editing
Hi - I've seen many different posts regarding duplicating a record - I am able to do this fine, however I do have a need to leave one of the fields (Position.NewLineNo) blank so that the user can fill it in. This field is not a primary key - I have a PositionID field which uses the autonumber feature. Since my VBA skills are not very advanced I'm looking for a solution to leave the field blank. I've included the code that I am using. This code is using a cmd button with the OnClick() event: Private Sub cmdDuplicateRecord_Click() On Error GoTo Err_cmdDuplicateRecord_Click...

Queries independent of accents?
Hello! We have a DB of people from many countries. Some names contain chars with accents like � or � and other types of accents. Is there a way of searching all names in the DB containing any kind of accent without specifiying the acctents used? Is there a function like f("�") = "e" where f is the function I look for. Thx in advance! Fritz Fritz Do you store the data as NAVARCHAR(n) datatype? Have you specify N just before a value in WHERE condition? Hmm, I think may want to take a look into REPLACE function to remove accents "Fr...

Tables/Queries
Hello, I've seen some posts regarding a similar problem to what I'm having but cant find the resolutution! I have a database with tableA which contains basic inventory info. I have a field in tableA named location. There is a second table (tableB) which has a list of all the locations our company delivers to. I've been warned to stay away from lookup fields, so I'm using combo boxes on the form to look up the location and then populate the location field in tableA when the value is selected. Here is the issue (and again, I've found some posts similar): Running a...

Help on muliple table queries
Hi all, I wonder if you can help. I have 5 tables of data with the same column names. I want to run a query which includes all records from all tables within a certain time period using a from/to date. Is this possible/simple, and how do I do it!! Thanks Natalie Hello Natalie. "Natalie" wrote: > Hi all, > I wonder if you can help. I have 5 tables of data with the same > column names. I want to run a query which includes all records from > all tables within a certain time period using a from/to date. > > Is this possible/simple, and how do I do it...

query form 12-29-09
I have a form that opens when you run a query for an employee name. It does exactly what it is suppose to do. It displays all information that I have requested. The problem I am having is when I enter a name of an employee that is not in the database the form comes back as a grayed out box. Is there any way to set the form or query to prompt the user that the name is not in the database and allow the user to run the query again. Eric Check to see if record exist before you open the form. If you are using a command button to open your form containing code like the follow...

Make Table Query Question 07-21-07
Hello All: Access 2003 I have a make table query and one of the field is a $$$ dollar amount. I set the properties to "currency". However, in the table, the field type is "Number". Any ideas why this is happening? -Joe You could try typecasting the field in your query. For eample, if the design view shows the field like this: Amount: [Quantity] * [UnitPrice] try changing it to: Amount: CCur(Nz([Quantity] * [UnitPrice],0)) In general, the better solution is to design the table the way you want it, and then use an Append query to populate it, rather than a Ma...

Error Exporting Access Query Results to Excel
I'm having 2 different problems exporting query results from Access to Excel (Office 2007). Problem 1: I do what I've always done, in Access VBA get ADO recordset from query results, instantiate Excel, get a range object, use the range object's CopyFromRecordset method. This has always worked in the (pre-Office 2007) past for me. It is much easier than iterating records and columns and assigning values to cells. Now, I get an error -2147467259 Automation Error Unspecified Error. I'm guessing this is because 2 of my recordset fields are pictures in Attach...

MS Query instead of "Edit OLE DB Query"
Hello all, I am having a problem with editing some existing queries within Excel 2003 workbooks. When clicking on the "Edit Query" button from the External Data toolbar, I now get the "Edit OLE DB Query" dialog box, instead of MS Query, which was the interface that I previously would get. Is there a way to switch this back? I have been using Office 2003 on Windows XP Pro SP2. I recently had Microsoft Update upgrade my version of Office 2003 from SP2 to SP3. The problems appear to have started after that. I can still create new queries in Excel using MS Query, which is...

Moving Parameter Queries
I have created a Parameter Query that imports transactions from an Access Database on my 'C' drive to "work out the bugs". It works just fine, however when I move it (as well as the related dqy file and the database) to a shared network drive so that others can access it, it sends back an ODBC driver error message. I am at a loss as to how to remedy this situation without recreating the query (very time consuming) at the new location Has anyone run into this before Any efforts will be much appreciated Tom Sounds like a confused link - am assuming your query pulls from a...

xPath query #2
I'm trying the select a element based on values of two attributes. In the Xml doc i need to look at an attribute minVal and maxVal. if the Value I'm passing in in the xPath is greate than min "and" less than max, I'll want to pull all the attributes of that element. Is this possible with xPath? I'm able to easily query 1 attribute, but not two. Thanks TR TR wrote: > I'm trying the select a element based on values of two attributes. In > the Xml doc i need to look at an attribute minVal and maxVal. if the > Value I'm passing in in th...

Append Query 08-21-07
I have two tables. Table A has 349,353 records. Table B has 377,787 records. I need to add the 28, 434 records that are not in table A but are in table B. Table A and B have a unique 6 digit number as its primary Key. When I attempt to run an append query from B to A, I get an error message stating it did not add 28, 434 records due to Key Violations. Sorting the unique 6 digit numbers tells me right off the bat that there are some in B that are not in A, yet when I attempt an append query, it tells me there are Key violations. I even selected on specific record whose 6 digit number I ...

Excel DNS query
Hello, Is there a way for an Excel function to query a DNS server? Thanks. Soundy Not that I know of, but you can turn on the macro recorder, use 'get external data' and tailor the resulting code into a user function of your own. E.g. I've used this to create a button to get MS-Access data from a query that has the same name as the sheet (tab) name. It saves me a lot of copy-paste actions. Bas Hartkamp <soundy@gmail.com> schreef in bericht news:1151940450.029823.127570@j8g2000cwa.googlegroups.com... > Hello, > > Is there a way for an Excel function ...

Learning XPath Query Language Resource Material/Books/White Papers
I am a total beginner to XPath and using Native .NET XML Classes (although I have used XML with Datasets before). I have "XML for ASP.NET Developers" which seems like a good .Net XML overview book but would like to learn more about writing XPath Queries. What do I need to learn the XPath Language? Thanks Earl http://www.zvon.org/xxl/XPathTutorial/General/examples.html -- This posting is provided "AS IS" with no warranties, and confers no rights. "Earl Teigrob" <earlt777@hotmail.com> wrote in message news:u9bfw%23fUDHA.1012@TK2MSFTNGP11.phx.gbl... &g...

Importing from delimited text file & removing duplicates
Hi I have a problem which I hope someone can help me with because I really don't even know where to start with it. I am using Access 2003. I have a delimited text file which contains about 200,000 lines. There are only 2 fields, Role and ID. The role field contains many duplicates, out of the 200,000 records I think there are only 16,000 unique roles. Role ID AB_F2S_COST_PLAN_CLK EE1 AB_F2S_COST_PLAN_CLK LT10 AB_F2S_COST_PLAN_CLK LT18 AB_F2S_COST_PLAN_CLK LT3 AB_F2S_COST_PLAN_CLK LT7 AB_F2S_COST_PLAN_...

Query
I need to take the individuals name off of all my customers shipping addresses. Can anyone help me write a query? Thanks Never Mind. I really want to eliminate the individuals name that show up on invoices (on the bill to or ship to) and I want just the customers business name to show. Is this possible? Thanks This is a multi-part message in MIME format. ------=_NextPart_000_03D3_01C6E96F.E89ECA00 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable GE, You will have to modify the Receipt Template. =20 Make a copy of the one you are using...