Append query problems, from Access to Excel

[Xunil]

Hi everyone. I have a problem with an append query runing from a small Access database and would very much appreciate some input.

The append query from Access is as follows:

INSERT INTO TransferTable ( Name, Surname, Company, Address1, Address2, [Town/City], County, PostCode, Phone, Fax, Email, [Client type] )
SELECT [Clients].[Name], [Clients].[Surname], [Clients].[Company], [Clients].[Address 1], [Clients].[Address 2], [Clients].[Town/City], [Clients].[County], [Clients].[Post Code], [Clients].[PhoneNumber], [Clients].[FaxNumber], [Clients].[Email Address], [Clients].[ClientType]
FROM Clients
WHERE [Name]=[Forms]![Client Form]!Name;

This works up to a point. What it does is send the selected data (based on the name of the client currently being viewed on the Client Form) to the TransferTable which is a link table to a sheet in an Excel workbook.

So far so good.

Each time the query is run though, it inserts the selectd data on the line below the previous run, even if the information that was inserted last time was not saved. So on the first run the query will drop the desired information into row A2 of the TransferTable, then even if that is deleted and the changes to the workbook are not saved, the next time the query is run it goes down to row A3, then A4 and so on. no doubt this is the exact point of an append query, but it's really aggravating me.

What I'd like is a query that will get the data into the same row of the TransferTable each time it is run, since another sheet in the Excel workbook runs some calculations based on the data held in the cells of the TransferTable sheet.

I've tried the SQL editor in Excel, but my very limited knowledge has left me completely frustrated. Does anyone have any ideas how I can achieve this outcome before I go completely nuts ?

I don't mind if the query runs from Excel or Access and I was toying with the idea of using

Cells.Select
Selection.ClearContents in a macro, except I am not a programmer by any stretch of the imagination.

I'm going to post this in the main Excel forum too, since I'm not sure quite where it is most suitable to present this issue.

Thanks in advance to anyone who offers any input.

 

[Tanis]

Can you not run a straightforward select query?

 

[Xunil]

Tanis

Thanks for taking the time to respond.

I have no idea how to run a “straightforward select query” from Excel that will return the information that I want.

The end of the query code that runs from Access that I have listed gives away what I need the query to return:

FROM Clients
WHERE [Name]=[Forms]![Client Form]!Name;

This is to place the Access formview recordset into the TransferTable sheet of the Excel workbook, based on whoever’s name is currently displayed.

Say I have 200 clients in the database, and say I browse to client number 101 whose details are entered as:

John
Doe
101 The Street
This Town
Zip Code
Phone
Fax
Email…

The query I have returns all of the fields for John Doe (Name, address and so on) since his name is currently on view, but if I browse to record 102 who is Jane Doe and run the query, it likewise returns only her contact information.

The only alternative I can think of is to export the entire client list from the database to Excel and run a filter by name on the processing sheet of the workbook to display the contact details for that person whose name is selected. This has obvious implications where the sheer volume of records will rapidly get out of hand and filtering by name only may return duplicate entries.

If I’ve overlooked something obvious in terms of the select query idea, please point me in the right direction, keeping in mind my relatively non-technical level.

 

[Xunil]

Problem solved

I can’t believe how obvious the solution turned out to be.

I changed the append query in Access to a create table query, based on the formview recordset, and linked the Excel sheet to the newly created Access table which obviously only contains that information I require.

Subsequent runs of the Access query simply overwrites (well deletes and re-creates with the same table name) the table as part of its create table query instructions, so I click one button in Access and Excel obligingly displays the new information.

I think I need a good kick in the pants for overlooking something like this.

Thanks to everyone who looked at this post and special thanks to the suggestions made in both of the forums it was is.

Cheers everyone.

I’m off for a drink…