Solved Excel export to Access, Update values, stop duplicates (1 Viewer)

[Micha3lS]

Hello all,

I'm getting excel data from production (from an SAP export) and also manual entries for the day each day. At the moment I call a access function from the excel workbook that executes the import. For the manual data I can stop duplicates by using an index, for the production however I can not do it with indeces, because in theory 2 entries can be the same on one day.

What would be the best way to stop importing duplicates and to update data.

For the update problem I thought about using the production date, deleting all records with the production date and then importing the new data. Unfortunatley I'm changing the Production date depending on the shifts. The production data runs from 00:00000 to 00:00000, however the shitfs don't

I was also thinking about using checksums and alike but I didn't find a nice way to accomplish that.

Did anyone have a similar problem and could point me to the right way?

Best
michael

 

[CJ_London]

would help to see some example data to illustrate the issue

perhaps consider using what is called an upsert query which will look something like this

UPDATE SourceTable AS S LEFT JOIN DestTable AS D ON D.ID=S.ID
SET D.field1 = S.field1, D.field2= S.field2, D.field3 = S.field3, D.ID = S.ID

important you include D.ID = S.ID

 

[Micha3lS]

This is the production data after import. The Schichtdatum (date, which might change with the import) and SchichtID are calculated during import.
Also the Gutmenge and Ausschuss are grouped by the materialnummer as smalles value.

The data is imported into the table Grunddaten and then everything is calculated and grouped with the following query:

 strSQL = "INSERT INTO ProductionDataDaily " _
        & "SELECT Arbeitsplatz AS AP, Bestandskategorie, Auftrag, AuschussUrsache, Sum(Ausschuss2) As Ausschuss, Materialnummer, Sum(Gutmenge2) As Gutmenge, Materialkurztext as Artikel, ShiftDate AS Schichtdatum, ShiftID As SchichtID " _
        & "FROM (SELECT ProductionLines.ShiftType, ProductionLines.Standort, getShiftDate([ShiftType],[Buchungsdatum],[Istende],[Standort]) AS Shiftdate, getShiftID([ShiftType],[Buchungsdatum],[Istende],[Standort]) AS ShiftID, Grunddaten.Arbeitsplatz, Grunddaten.Buchungsdatum, Grunddaten.Istende, Grunddaten.Auftrag, Grunddaten.Materialnummer, Grunddaten.Materialkurztext, Grunddaten.Gutmenge As Gutmenge2, Grunddaten.Ausschuss As Ausschuss2, Grunddaten.AuschussUrsache, Grunddaten.Bestandskategorie, Grunddaten.ID " _
                & "FROM ProductionLines INNER JOIN Grunddaten ON ProductionLines.AP = Grunddaten.Arbeitsplatz) a " _
        & "WHERE  Arbeitsplatz Not Like  '*[!0-9]*' " _
        & "GROUP BY Arbeitsplatz, Auftrag, Materialkurztext, Materialnummer, ShiftID, Shiftdate, Bestandskategorie, AuschussUrsache "
 
 DoCmd.RunSQL (strSQL)
 DoCmd.RunSQL ("DELETE FROM Grunddaten")

The productiondata from SAP look like this:

I will have a look at the Upsert query, thanks

 

[arnelgp]

does your sap have "autonumber" of some sort.
because what you have is very difficult.
for all you know you have already imported a data, but
actually another "edit" has just been made on some records?
how can you know the difference?
next import these records are "updates" not "inserts".

 

[Micha3lS]

Since I have to use excel as an intermediate step (what I really don't like, but that what it is at the moment) I could try to generate a number here, to atleast identify the records, delete them and import them from new.

I will ask about the autonumber from SAP, but I highly guess there is nothing like this implemented.

 

[arnelgp]

will ask about the autonumber from SAP, but I highly guess there is nothing like this implemented.

don't tell us, you don't even bother to have autonumber on all your Access tables?
aside from normalization, this is another you don't want to ignore.
it uniquely identifies "each" records.

you can save the "sap generated autonumber" to your table.
you only need to inquire the table if the number already exists in your table.
if it does "update" the record.
if not then "append" the record.

 

[Pat Hartman]

At the moment I call a access function from the excel workbook that executes the import. For the manual data

This sounds like you are importing the data FROM Access into the Spreadsheet. Or this may be a translation problem. If the code is running in Excel and the data is going to Access, you are exporting it, not importing it.

I don't understand why, if you can define uniqueness manually, that you cannot create an appropriate unique index in your Access table. The table's PK should be an autonumber but you can define a multi field unique index if you need to. To define this multi-field index, you MUST use the Index dialog. You cannot do it by just marking a field as unique because that method only creates single field indexes and it is the combination of several fields that defines uniqueness unless SAP can send you a unique ID. And then, if SAP can actually send a unique key, how will that affect data entered manually? This is a pretty scary process. Is SAP the "master" or is it not? I've had apps that interfaced with SAP and was always disappointed in the discipline of the data. For some reason, SAP never did proper editing so records would have invalid values or be duplicated. Apparently 30 years later, SAP is still sloppy and they still won't let you link directly to the tables or even a view.

Also, I'm not sure why the code is running in Excel to export rather than in Access to import. Who decides when the spreadsheet is ready to import? Whichever side you run it from, you could also use a totals query to summarize away the duplicates but I would be more worried avout how the duplicates get there in the first place.

 

[CJ_London]

It is not uncommon for systems such as SAP not to supply a unique ID.

You'll need to check for duplicates but it may be that by combining the date and time fields is sufficiently unique.

But you are doing a grouping in your insert query so that will destroy any chance of having a unique ID.

Normal process would be to import the data on a one for one record basis, then do your grouping/summing further down the line

another thought re duplicates - are these duplicates within the same import file? or duplicates with data for previous imports? or both?

 

[gemma-the-husky]

Can you identify a duplicate by inspection?

If you can't, how can a computer process identify it?

Is the date and time taken together a unique identifier?

 

[Micha3lS]

Good morning,

thanks for all your replies. I didn't have time to answer on the weekend.

Just to calrify: The code for the data import runs in access. The function however is called in the excel sheet with a button press, so the enduser does not have to interact with excel at all. We want someone from production to fill in the excel sheet and then save the data in the database. They are used to excel but never worked with access.

Duplicates can only occur, when someone presses the button twice. There are no dublicates in the data itself, it just happens when you import the data multiple times.

SAP doesn't give me a unique ID unfortunately. But even if it would, I would loose that information by the grouping. The grouping is done to reduce the entries in the database. I don't need every single record in SAP, I just need the production volume based on the materialnumber for later analysis.

For one day import, the grouping reduces the number of entries from ~7100 to ~1400. Keeping all that data is probably not a valid aproach?
With the grouping I also loose the time information. So date + time index is also no option. And of course I'm using an autonumber for all my tables.

Data entered manually is not the production volume, it's completely different and is saved in another table. With there manual data there will be no grouping, so no problem with identifying duplicates / stopping them from happening

 

[CJ_London]

To summarise what you are saying is you want to prevent data being imported more than once by a user running the code more than once?

Normally I would say you need to use something like the file name as a unique 'import' ID - is that possible? Then when the importing code is run that file name is included in the import - and you can run a check first to see that name already exists in the database

Alternatively is the date field a unique 'import' value i.e. you only import data for the 28th September 2021 once, it does not appear in other imports?

 

[gemma-the-husky]

could you store something in a cell as part of the button click to record that the import has been completed, so it can't be done twice?

 

[Micha3lS]

Yes I could use the filename or even the production date as check to prevent duplicates. Would you create a seperate table to save, which files or dates have been imported already?

However I want to have the possiblity (maybe via user dialogue choice) to update the data.

I guess to accomplish that, I have to delete all entries of that date, then reimport them.

 

[CJ_London]

guess to accomplish that, I have to delete all entries of that date, then reimport them.

If you can't identify a unique id for the data (as opposed to the file name) then yes, you'll need to delete and reimport.

Suggest you store the filename in as separate table
perhaps code along these lines

dim importID as long
importID=nz(dlookup("ID","tblImportFileNames","FileName='" & filename & "'"),0)
if importID=0 then
    add filename to tblImportFileNames, perhaps with other data such as date, number of records etc
    import
else
   if msgbox ("this file already imported, do you want to reimport?")=vbyes then
      delete data in access table
      maybe delete filename from table as well then reinsert with fresh date/whatever (depends on what you want to be be able to do)
      or update filename table with 'data deleted date' and create a new import so you have a history what has happened with your imports
      import
   end if
end if

make sure your importID is included in your main table as an indexed field so you can easily delete the required records.

you could just include the filename in your main table - but that will consume more space

think nz function doesn't work in excel, so you may need to do something else - or call a function in access

 

[Micha3lS]

Alright. I will go with that approach. Thank you all for your help!

 

[Pat Hartman]

They are used to excel but never worked with access.

There is absolutely no reason that the users need to even know that the application you create for them is written with Access. If you create an actuall application, all they ever do is to interact with forms and reports and press buttons and enter data. They see NONE of the back stage objects. The app opens to a menu. They choose what they want to do. They don't create queries. They don't get to see code. It is YOUR job to isolate them from all the technical stuff.

If you have the option to replace the spreadsheet with an Access application, that is a superior solution. It will give you much better control over the import.