Appending Data from SAP Excel File - duplicate date

[fortwrestler]

Hello,
I'm working on a database that imports data from an Excel file generated by SAP. The excel file shows all of the inventory(HU) that are blocked for certain reasons.
Once the HU is removed from the blocked status in SAP, it is no longer generated onto the Excel file.

Is there a way in Access to say if "xx HU" is not present, then the block status is lifted without manually changing the status?

I know this may be phrased in a confusing manner so I'll try to give an example.


Day 1 Excel :
HU: 1000 is Blocked
HU: 1001 is Blocked
HU: 1002 is Blocked


Day 2 Excel:
HU: 1001 is Blocked
HU: 1002 is Blocked
(HU:1000 block is lifted so it is not present in this file)

So, I want HU1000 record to remain in the Access Database, i just want the status of it changed to "unblocked" or "completed".

Is there a way to do this when appending an excel file? The HU number is indexed with no-duplicates to prevent it from becoming multiple records.

 

[plog]

You haven't really explained your process (where/how does this data get imported?) or table (name? fields?) that well, but I get the general idea and have a general suggestion.

Ultimately the key to this is a LEFT JOIN. You need a table with all your HU values and field in that table to hold the status:

YourTableName
HU, Status
1000, Active
1001, Inactive
1002, Inactive
1003, Active

Then you get your blocked data for today:

tmpBlocked
HU
1000
1002

To update the status you would join those two tables via their HU fields. You would then change that join to show all records from YourTableName (thats the LEFT JOIN). Then, change the query to an UPDATE query and bring down the Status field to the lower portion of the query. You would then create a calculation to determine the new status of the Status field. This would be that calculation:

Iif(IsNull(tmpBlocked.HU), "Inactive", "Active")

So if a record is in tmpBlocked it gets set to Inactive, if its not in there it gets set to Active.

 

[CJ_London]

and to follow on from Plog, reverse the left join and criteria to find new records in excel which are not in the table and append (rather than update)

 

[fortwrestler]

Thanks Plog, I was successful in setting what you described up. I was thinking this was impossible and you made it simple.

CJ, Since I have the HU Index set to No Duplicates, would just appending the tmpBlocked table to the "master" table suffice to add the new records?

Also...
What would be the best way to bring in the Data from Excel? "Import the Source Data into a new table" or "append a copy of records to table"
The issue I'm seeing with the import into new table is that the update/append query would have to be adjusted each time for the new table name and the issue with the append is that the "old/no longer blocked" data would still be present.

While I understand how to import the data correctly, the individuals who will be using this database aren't familiar with the backend setup of the database.

 

[CJ_London]

CJ, Since I have the HU Index set to No Duplicates, would just appending the tmpBlocked table to the "master" table suffice to add the new records?

I would think so

Also...
What would be the best way to bring in the Data from Excel? "Import the Source Data into a new table" or "append a copy of records to table"
The issue I'm seeing with the import into new table is that the update/append query would have to be adjusted each time for the new table name and the issue with the append is that the "old/no longer blocked" data would still be present

depends - I prefer to link to the excel file and run action queries so I have more control (for example a column in Excel needs to be converted to a FK from a lookup table or some formatting is required, only some columns are required, tag the records with which file they came from etc)

By linking all you need to do is have a generic name for your excel file ('month end results', 'daily queries', etc) and swap the old file for the new file - the original file name can remain as that in your archive. Then no need to create temporary tables - the linked table will see the data in the new file.

So for example have an 'import' folder and an 'archive' sub folder. User browse to new file (called say 'month end results 201608'and copies or moves to the archive folder. Then the file is copied and pasted to the import folder, stripping off the suffix to replace the existing 'month end results' file.

This can all de done in VBA - all the user has to do is to identify the file to be imported by browsing to it.

 

[fortwrestler]

Perfect...I never thought about using a linked table that way.

Thanks for the help and quick reply.

 

[CJ_London]

be aware that excel (or any non db file) will not be indexed so if your file is very large you may get better performance by importing to a temporary table which is suitably indexed rather than using a linked file.

Also, thinking about the 'no duplicates' setting, that will block any changes to an existing record - so if you have in the table

HU.....Date
1000..01/01/2016

and try to import

HU.....Date
1000..02/01/2016

it will not update the existing record