Hi,

I have two tables as follows:

___________________________
tableA:

projectid capital
1 200
2 200
3 300
4 400

tableB:

projectid capital
1 100
2 200
3 300
____________________________

I want to create tableC that is a merge of tableA over tableB as follows:

projectid capital
1 200
2 200
3 300
4 400

Can someone tell me how I can get this done please?

I have been struggling with query syntax on this for about 2 days now not knowing how to get this done. Thanks for responding.

Not too sure why you want to create table C which is identical to table A. But the answer to your question is to create a query which will have Table A and Table B as the source table. Then drag and drop the required fields into the panel beneath (QBE) one at a time. To make it really usefulin the criteria enter [PLease enter Project ID] this will then prompt the user to enter the ProjectID each time the query is ran.

If you wish then to create forms make sure that the "data entry is set to No" in the form property for viewing recorded information and likewise to "Yes" for entering new information.

My apologies! I did not explain my problem well. What I am trying to do is this:

1. One of our users need to import spreadsheet found in a specific directory (say c:\excelfiles\) into the Access db as a table.

2. If the user runs the above procedure more than once it SHOULD NOT append the records again - the procedure should only update the records.

For instance, lets say the spreadsheet looks like this below:

projectid capital
1 200
2 200
3 300
4 400
5 500

After running this procedure once, the user went ahead and changed the "capital" value of projectid =1 as follows:

projectid capital
1 100
2 200
3 300
4 400
5 500

Now, when he ran this import procedure again, the table should only have the above latest information and should not look like below:

projectid capital
1 200
2 200
3 300
4 400
5 500
1 100
2 200
3 300
4 400
5 500

How can I get this done? I know I could have articulated this problem better, but I am just brain dead now. If I am still not clear please do let me know.

Many thanks for helping me out.

Here's a link to a query that updates existing rows and appends new rows. To use it, you would link to your spreadsheet rather than importing it directly into the permanent table.
Append and Update query (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49061&highlight=append+AND+update+AND+query)

I couldnt have figured this out myself. I am working on trying to incorporate this idea on my database.

I am importing a spreadsheet into a temptable in Access using the transferspreadsheet method. When I do this the field "projectid" takes the data type Number by default. I want this to take the data type Text after it is imported.

Anyway to solve this problem ?

There are issues with how Access and Excel see data types. The only sure way is to import into an already existing empty table so that Access controls the final data type rather than having to determine it as it imports the spreadsheet.

I will create an empty temptable with the proper data types and import the spreadsheet using the transferspreadsheet method.

Thanks for your suggestions. Much appreciated.