Update querymultiple columns (1 Viewer)

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have relationships like (not look at model data, this is only sample, you can propose your data for it like tblCats or something different) in attached picture.

and i am getting all data for tblDescription from Excel. I have the same field names there plus People. I have not only DescriptionID in ExcelTempTable so only way to check if some new descriptions was added is to create inner join between ExcelTempTable and tblDescription.

I am wondering it is good what i am thinking.
Fields data in tblDescription can be changed so i have to run update query and if there is not such description, add description to tblDescription and furthermore add this new descriptionID for spefic PeopleID in tblDescriptionPeople.

What i want to do is:
1. Import excel table
2. Join all fields via inner join in tblDescription and tblExcelTemp to be sure what we want to update
3. For example i will have only one field data different, eye color will change from blue to black for Man "Paul". How to check this only one? How to do proper query?
4. To check if specific DescriptionID exists, i have to join tblExcelTemp with tblDescription and what is not in tblDescription - append, correct?
5. After that i have to check if spefic DescriptionID for specific Man from tblPeople exists, if not add it to tblDescriptionPeople, correct?

So the last 3 points are very important and i need to help with them.

Please help,
Best,
Jacek
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    80.6 KB · Views: 103
  • sample.accdb
    420 KB · Views: 110

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
add a a column to your worksheet, then fill this with sequencial number.
after linking, you only need to match the sequencial number (link) against the one you already have in the table, then update the table.

eye color, hair color is not unique identifier.
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
Hmm thank you, good idea.

What if i will not have this possibility to add unique identifier and have to update values only using joined fields?

It is possible at all?

i know that i can check if specific description exists based on inner joins (join all the same fields), if not add to table Description and add this to specific PeopleID in tblDescriptionPeople, but update?

Best,
Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
yes, you are correct the only problem is the update.
what you will need to do is use Recordsets, one for the worksheet and the other your access table.
Code:
dim rsPeople as dao.recordset
dim rsDescription as dao.recordset
dim rsDescriptionPeople as dao.recordset
dim rsWorksheet as dao.recordset

dim strPeople as string
dim strDescription as string

dim db as dao.database

set db=currentdb

set rsWorksheet = db.openrecordset("ExcelTempTable", dbopendynaset)
set rsPeople = db.openrecordset("tblPeople", dbopendynaset)
set rsDescription = db.openrecordset("tblDescription", dbopendynaset)
set rsDescriptionPeople = db.openrecordset("tblDescriptionPeople", dbopenrecordset)

with rsWorksheet
	if not (.bof and .eof) then .movefirst
	while not (.eof)
		strPeople = !PersonID 'change this to actual field in linked worksheet
		strDescription =![Eye Color]
		
		' find this person to tblPeople
		rsPeople.FindFirst "[PersonID] = '" & strPeople
		' if we did not find, add it together with eye color
		if rsPeople.NoMatch Then
			rsPeople.AddNew
			rsPeople!PersonID=strPeople
			rsPeople![Eye Color] = strDescription
			rsPeople.Update
		Else
			' this person already exist, replace eye color
			rsPeople.Edit
			rsPeople![Eye Color]=strDescription
			rsPeople.Update
		End If

		' find description to descripion table
		rsDescription.FindFirst "DescriptionID = '" & strDescription
		' if not found, add
		Id rsDescription.NoMatch Then
			rsDescription.AddNew
			rsDescription!DescriptionID = strDescription
			rsDescription.Update
		End If
		'
		' same thing with DescriptionPeople
		' find, if found edit.
		' if not found, add it
		.MoveNext
	Wend
End With
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
thank you,

ok i am not getting this exactly, i mean process not code.
Assume i will add new field to tblDescription,
ExcelTempTableID.

And now i can check if any record was updated.
This is ok.

But if my Excel record for example number 2 was deleted how can i check this?
It could be deleted and added new one or only added new one.
Only inner join all fields based on fields data?

Like in screen attached?

Source data in Excel it is cumulative data, some rows can be added, other delete, some of them updated.

In attachment please find example database,

Best,
Jacek
 

Attachments

  • Screenshot_3.jpg
    Screenshot_3.jpg
    59.7 KB · Views: 102
  • sample.accdb
    592 KB · Views: 92

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
I am curious of what data this worksheet holds.
does it have any unique identifier that you can identify, such as employee id, sss no, tel no, card no, etc. that is unique?
if there is then this entity will be your unique identifier.
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
i do not have unfortunately any od unique identifier. I can only add this sequential number i think.
So this is a problem.

Real Data is from Azure Cloud and there a volume settings.
In sample i do not have also unique identifier and question is how to solve this?

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
Ok,

if i would have sequential number i can check if sequantial unique numer in excel exists in tblDescription and if yes - update it only , if not, add to tblDescription,
if sequantial number exists in tblDescription and not exists in Excel table - delete it.

But condition is to have unique sequantial numbers.

What if have not uniqeu numbers or fields. I can only check using inner join what row was deleted (link each field from Excel Table to each the same field from tblDescription) and was is new, correct?

Best,
Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
if you can upload a sample excel worbook, we can work on something.
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
In attachment.
 

Attachments

  • ExcelTempTable.xlsx
    9 KB · Views: 104

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
what i mean is the "raw" data you are getting.
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
the raw data what i am getting is what i attachec without unique IDs...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
my advice, dont link on seqno, as you said rows maybe added/deleted on the sheet.

to update and add new record, use left join:
Code:
update tblDescription as t1 left join ExcelTempTable as t2 on 
    ti.[eyecolor] = t2.[eyecolor] and 
    t1.[hairstyle] = t2.[hairstyle] and 
    t1.[noseshape] = t2.[noseshape] 
set 
    t1.[eyecolor] = t2.[eyecolor], 
    t1.[hairstyle] = t2.[hairstyle], 
    t1.[noseshape] =  t2.[noseshape];
to delete records from tblDescription which is not in ExcelTempTable:
Code:
delete t1.* from tblDescription as t1 
    where t1.[eyecolor] & t1.[hairstyle] & t1.[noseshape] 
       not in 
    (select [eyecolor] & [hairstyle] & [noseshape] from ExcelTempTable);
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
o wow , thank you.

so with cumulative data like provided using seqno is not the best option?
so only text comparison within joins?

I want to be sure that this is the best option here.

Best,
Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
seqno is not advisable since everytime you download the excel file you will again put the sequence to it, which is not guaranteed to be with the previous one you set.
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
yes it is true. thanks!
So this should be not sequantial number but unique ID in Excel...

This is very nice approach to update and add new records using sqls.

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
One more thing my Friend,

when i checked what should be in my tblDescription i have also in Excel column with Person name for each Description. I should check if specific person has the specific subscription and delete or add it. I should use the same mechanism?

I mean Person and description are in different tables and this is a little confusing for me.

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
The sample in attachment
 

Attachments

  • ExcelTempTable.xlsx
    9 KB · Views: 113

jaryszek

Registered User.
Local time
Today, 03:10
Joined
Aug 25, 2016
Messages
756
Hi,

i changed record ExcelID = 2 in ExcelTempTable. I changed NoseShape field to "Medium" from "Small" and used your sql:

Code:
update tblDescription as t1 left join ExcelTempTable as t2 on 
    ti.[eyecolor] = t2.[eyecolor] and 
    t1.[hairstyle] = t2.[hairstyle] and 
    t1.[noseshape] = t2.[noseshape] 
set 
    t1.[eyecolor] = t2.[eyecolor], 
    t1.[hairstyle] = t2.[hairstyle], 
    t1.[noseshape] =  t2.[noseshape];

and boom, i got empty row in table tblDescription, it was not updated and furthermore whole fields in this record was erased (deleted or set es empty) for fields EyeColor, HairStyle amd NoseShape.



Please help, why this is happening?

Best,
Jacek
 

Attachments

  • Screenshot_13.jpg
    Screenshot_13.jpg
    52.2 KB · Views: 180

Users who are viewing this thread

Top Bottom