Problems with append query (1 Viewer)

arnoldg

New member
Local time
Today, 11:13
Joined
Mar 29, 2020
Messages
6
Hello i'm trying to import a table with companys (tblImport)
all records if not doubleshould be imported, but i searched a lot and tryid a lot, but cant get it right.
All the time all records are imported, so after several time the recorders are doubled.

tblImport.F6 and tblBedrijven.ServiceLocatie should be compared.

field ServiceLocatie isnt set as unique.


Below is what is in query designer.

1585851651104.png
 

plog

Banishment Pending
Local time
Today, 05:13
Joined
May 11, 2011
Messages
11,611
...all records if not doubleshould be imported

What constitutes a "double"?


All the time all records are imported, so after several time the recorders are doubled.

I'm kind of confused. If you do this process a second time, obviously its going to import the data a second time. Why are you doing it a second time? I'm confused by your statement--is it suppose to be an issue that needs to be worked around?

tblImport.F6 and tblBedrijven.ServiceLocatie should be compared.

How, why and to what end? Your JOIN in the image you posted is between tblImport.F5 and tblBedrijven.Bedrijfid, and none of the criteria compares them. You are only including values of tblBedrijven.ServiceLocatie that are NULL--and you can't compare NULL values to anything (not even other NULL values). Again, this statement confuses me as well.

Also, in the image the criteria for F1 is that it doesn't equal the literal string "Postcode". It's not using tblBedrijven.Postcode as criteria its literally using the value "Postcode". I'm pretty sure this is incorrect.

Lastly, a source of duplicates is the JOIN I mentioned above. If tblBedrijven has multiple values for Bedrijfsid then the query could produce more records than what are in tblImport.

Since there is so much that I don't understand, I suggest you demonstrate your issue with data. Show me some sample data from both your tables and then show me what your query is producing and also what you expect it to produce based on that sample data.
 

arnoldg

New member
Local time
Today, 11:13
Joined
Mar 29, 2020
Messages
6
Well it is verry simple, when importing a company ther servicelocation (sl) should be compared int the tblbedrijven, if it is present it shouldent be imported.
Why run twice ord more, because the imported table is an export list (Excel) of oure erp. And once in a while i need to update the list in my database.
 

Micron

AWF VIP
Local time
Today, 06:13
Joined
Oct 20, 2018
Messages
3,476
I think I get it because I've done this sort of thing before - as a nightly download from the company backup and it made more sense to not worry about what was already in the local tables when you're searching through millions of records. That would only slow things down. So my 'fix' was to set unique indexes on the necessary target fields and turn off warnings, which is something I don't usually do. If I must, then I make sure there is an error handling routine that will turn them back on if the handler causes the procedure to terminate. If I use the Execute method, it is rare that I don't use the dbFailOnError option so it probably wouldn't be appropriate here. The result was that the queries updated and appended what they could without intervention. Since there were several update and append queries to run, I passed their names to a function:
Code:
Function runQuery(qryName As String) As Boolean
'uses name of query supplied and employs transactions to allow rollback in case of failure
On Error GoTo errHandler
Dim db As DAO.Database

runQuery = True
Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb
Set qry = db.QueryDefs(qryName)

wrk.BeginTrans
qry.Execute
wrk.CommitTrans

endHere:
Set wrk = Nothing
Set db = Nothing
Set qry = Nothing
Exit Function

errHandler:
'''MsgBox "Error Number " & Err.Number & " : " & Err.Description
runQuery = False
Err.Raise 5000
wrk.Rollback
Resume endHere

End Function
 

strive4peace

AWF VIP
Local time
Today, 05:13
Joined
Apr 3, 2020
Messages
1,003
hi arnoldg,

I suspect that joining in tblBedrijven is causing the doubles.

I don't speak your language so I don't know what the fields mean, but it would be better to Append the new records and then do an Update query to fill ServiceLocatie for records that are blank. BUT! perhaps Bedrijfsid isn't unique? In those cases, how would you choose which ServiceLocatie to use?
 

arnoldg

New member
Local time
Today, 11:13
Joined
Mar 29, 2020
Messages
6
Hello, tblBedrijven = tblCompanys

I'm trying to explain it better, i think that my select isn't right.

1. clear tblImport and load with current export from erp

2. create a select to filter out all records that already ar in the tabel tblCompanys (how do i do this)

3. Copy new records to tabel tblCompanys


A servicelocation can only be one time in the table's so on this field i could do a select / compare.

how to do this ?
 

strive4peace

AWF VIP
Local time
Today, 05:13
Joined
Apr 3, 2020
Messages
1,003
hi Arnold,

Thanks. If Bedrijfsid should be unique, then make a Unique Index on that field so duplicates won't be allowed to go in.

1. Go to the Design View of tblBedrijven
2. Click on Bedrijven to select it
3. In the lower Pane, set the Indexed property to --> Yes (No Duplicates)

If you already have duplicates, you'll have to find them and remove them before this will be allowed. You can create a Find Duplicates Query with the Query Wizard to help you.

When importing, add Bedrijfsid to the grid with this criteria:
Is Null

and make sure it doesn't have anything filled in the cell to choose which field to append to -- what you are currently doing for service location, since Bedrijfsid is the field you're joining on
 

arnoldg

New member
Local time
Today, 11:13
Joined
Mar 29, 2020
Messages
6
what would happen if i import records that ar duplicates, don't i get an error, which says that there are duplicates ?
 

Micron

AWF VIP
Local time
Today, 06:13
Joined
Oct 20, 2018
Messages
3,476
what would happen if i import records that ar duplicates, don't i get an error, which says that there are duplicates
Hmmm. Seems I covered that and the unique index(es). It didn't make sense?
 

strive4peace

AWF VIP
Local time
Today, 05:13
Joined
Apr 3, 2020
Messages
1,003
Arnold, if you set a unique index and filter out companies already there, this shouldn't happen.

Maybe the join is supposed to be on service location not company?

if that is the case, then service location needs a unique index -- and then the table should be called serviceLocations, not companies ... or maybe there should be a serviceLocations table also? I'm just guessing since I don't know your data.
 
Last edited:

Users who are viewing this thread

Top Bottom