Question re: Importing data from excel and append to my Access table

[Stacy Rueda]

Hi everyone,

Please help me with my problem regarding importing data from excel and append to my Access table error. Everytime I import my table to Access using macros, record become duplicated.

This is the details of my Access tool

Table : 1. FG info 2. PL list

Query : 1. Append_FG info 2. Append_PL list 3. Delete_FG info 4. Delete_PL list

Macros (Details)

1. OpenQuery for Delete_FG info
2. OpenQuery for Delete_PL list
3. ImportexportSpreadsheet (for FG info)
4. ImportexportSpreadsheet (for Pl list)
5. OpenQuery for Append_FG info
6. OpenQuery for Append_PL list

Please help me, in what step or method i made mistakes. Thank you all.

 

[stumac]

You are importing the spreadsheet - into the table and then running a query to append the data into the table... you shouldn't need the append (unless I am missing something). That could be causing all records to be duplicated.

 

[Joe4]

Are you importing the data into your FINAL table, or a temporary table, and then using the Append Query to post them to your Final table?
Is the issue that you are importing records that already exist in your Final table?
If so, you will want to make those two Append Queries unmatched queries between your temporary and Final tables, so that you are only adding new records (and not adding records that already exist in the Final table).

 

[Stacy Rueda]

Hi Stumac, thank you for your reply.

This should be the result of my tool.

I want to create a form and subform, an FG nos. (main form) will make a PL list (sub-form). MS Access will import excel file and append to PL list table-main table(without duplicating) and import also FG nos. (which is unique so i set primary key).

Here are more details:

FG nos. table (same format with excel that is for import to)

FG no. Desc Rev. Name Remarks
X123 0988 2 PWB ROHS



PL list (for X123) sub form
FG no. PN Loc. Desc Qty Size Set No. Machine

X123 444 R23 Board 1 8cm 2 Bro78
X123 321 Y65 Reel 8 9mm 4 Bro100
X123 897 P76 Board 2 9cm 5 Bro78


Hoe u can help me. Thank you very much

 

[Stacy Rueda]

Hi Joe4, thank you for the reply.

Ok, that's a good idea, sorry I'm a newbie in MS Access.. you are correct, I need table that is location of my imported excel data, then I will append to Final table.. Thank you.

 

[Stacy Rueda]

I have question also if you know this and pls. help me.

What If I want to change some data in sub form of this particular FG no. without affecting other FG nos. sub form.

Thank u