UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update And Insert New Records All Within The Same SQL Line, Access 2013    
 
   
gint32
post Nov 8 2018, 12:59 AM
Post#1



Posts: 284
Joined: 8-May 09
From: Australia


Hi all,
Following on from the below .. Converting Query To SQL Error
https://www.UtterAccess.com/forum/index.php...2051394&hl=

CODE
  Dim dtStart As Date, dtEnd As Date, strSQL As String

  dtStart = Me.txtDateStart
  dtEnd = Me.txtDateEnd

  strSQL = "UPDATE tblStaff " & _
           "INNER JOIN Attend " & _
           "        ON tbl_Staff.StaffID = Attend.AttStaff " & _
           "  SET Attend.AttType = 3 " & _
           "WHERE Attend.AttType <>99 " & _
           "  AND Attend.AttStaff = " & [Forms]![CalendarWithSub_BkOffs]![Frm_Sub_StaffBkOffs].[Form]![StaffID]  & _
           "  AND Attend.AttDate >= " & Format(dtStart, "\#yyyy\-mm\-dd\#") & _
           "  AND Attend.AttDate <= " & Format(dtEnd, "\#yyyy\-mm\-dd\#") & ";"

  Debug.Print strSQL
  CurrentDb.Execute strSQL, dbFailOnError


Is it possible to insert a certain amount of new records to an existing table based on a date range and also within the same SQL statement if any records exist with that range also update them to the reflect the new data?
As currently the only way I know how to do this is create a Delete query based on a date range, then a create a Make Table and/or create insert query with the same date range to accomplish this out come.

Go to the top of the page
 
theDBguy
post Nov 8 2018, 01:19 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,751
Joined: 19-June 07
From: SunnySandyEggo


Hi,

The idea is possible, but I’ll let you adapt this method to your specific situation. But if you get stuck, just let us know.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gint32
post Nov 8 2018, 01:47 AM
Post#3



Posts: 284
Joined: 8-May 09
From: Australia


Thanks, but that link , but..it was to far off the mark for me to begin from, so my question now would be.. is the normal way to do this for most people not to SQL savy to create a Delete query based on a date range, then a create a Make Table and/or create insert query with the same date range to accomplish this out come?
Go to the top of the page
 
Minty
post Nov 8 2018, 04:19 AM
Post#4



Posts: 123
Joined: 5-July 16



The "Upsert" technique described by theDBguy is the only real way to accomplish your requirement in one go, and won't work in every scenario.
I'm slightly concerned that you delete the records rather than update them, which would be more the norm. A workflow for this type of update / insert task would be;

1. Find matching records and update the existing values.
2. Add the new records.
3. Compare your results against the import data for any errors.

Deleting records isn't good news if you have any autonumbers ID fields and can cause issues with referential integrity of child records.
Go to the top of the page
 
projecttoday
post Nov 8 2018, 05:00 AM
Post#5


UtterAccess VIP
Posts: 10,252
Joined: 10-February 04
From: South Charleston, WV


Why does it have to be done in one step?


--------------------
Robert Crouser
Go to the top of the page
 
LPurvis
post Nov 8 2018, 05:30 AM
Post#6


UtterAccess Editor
Posts: 16,291
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Obviously, I fully endorse the question, why does this need to be performed in one action?
For example, the method suggested by theDBguy is something of an Access quirk, you'll not find an Update query in SQL Server adding rows implicitly to satisfy such an Update statement.

That said, it should work for you for the most part - with some caveats.
You said "...it was to far off the mark for me to begin from". Do you mean you didn't see how to apply it to your scenario?
The clear difference is that you have an INNER JOIN and the method mentions requires an OUTER join. That said, that's not enough. You also have criteria applied to the OUTER table, and therefore that would effectively re-create an INNER JOIN in your query. So you'll need to include those in your JOIN.

Something more like:
CODE
  strSQL = "UPDATE tblStaff " & _
           "LEFT JOIN Attend " & _
           "        ON (tbl_Staff.StaffID = Attend.AttStaff " & _
           "  AND Attend.AttType <> 99 " & _
           "  AND Attend.AttStaff = " & [Forms]![CalendarWithSub_BkOffs]![Frm_Sub_StaffBkOffs].[Form]![StaffID]  & _
           "  AND Attend.AttDate >= " & Format(dtStart, "\#yyyy\-mm\-dd\#") & _
           "  AND Attend.AttDate <= " & Format(dtEnd, "\#yyyy\-mm\-dd\#") & ")"
           "  SET Attend.AttType = 3, Attend.AttStaff = " & [Forms]![CalendarWithSub_BkOffs]![Frm_Sub_StaffBkOffs].[Form]![StaffID]  & ", Attend.AttDate = Date()"


Obviously, you have to actually set the fields you want to specify - so I'm guessing what you'd want the Attend.AttDate to be as that's found by an inequality.
It'll either work for your needs or it won't, in which case you'll need to consider... more than one action. ;-)

Cheers

--------------------
Go to the top of the page
 
gint32
post Nov 8 2018, 05:31 AM
Post#7



Posts: 284
Joined: 8-May 09
From: Australia


No, it does not have to be in one step, though even at that I am having difficulty with just inserting new records as I can only manage to amend the existing records, no matter what I have tried so far, so I will need help with this part .
So far I have managed to create a query that will create a new table with the existing records from a selected table for a pre-specified date range, then I have another query to update a (single) field on those new records within this new table (for the same date range), but I can't seem to figure out how to add new records within this date range for the dates that are missing reasons being if those particular dates don't exist in the original table, then they will not pull through to the new make-table.. example..if the date range is between 1/1/2001 <> 31/012001 and there happens to be one record for each date within that range apart for 4 days (from the 2,3,4 & 5/1/2001) then I am stuffed for adding these dates into the original table as they don't exist. As I was hoping to then move these record back to the original table and then delete the new maketable . Hope this makes sense to you
Go to the top of the page
 
gint32
post Nov 8 2018, 07:32 AM
Post#8



Posts: 284
Joined: 8-May 09
From: Australia


Thanks for that SQL, I'll have a go with changing it to suit.
Whilst searching the web I found and adapted the following slightly and this seems to fill in the date gaps with the values I want.

I suppose I can now run an docmd.run query to update these values now that I have a record for each date.

But, if continue to use this method (and I'd prefer to rather than jump between methods) below how can I change it to actually edit the new and existing value that are not 99 within the same date range?

If I manage to achieve this then I the problem is solved as I have my solution resolved...
"Silly me thought "all I'd have to do is now change one line from:"


CODE
rsAttend.AddNew
to
CODE
rsAttend.update
or
CODE
rsAttend.updatable


but none of these seem to work, does anyone have any suggestions that would suffice


CODE
Dim rsAttend  As DAO.Recordset
                    Dim NextDate    As Date

Me.startingdate = startingdate
Me.EndingDate = EndingDate

                       Set rsAttend = CurrentDb.OpenRecordset("Select Top 1 * From attend")
                        NextDate = Me!startingdate.Value
                    
                    While DateDiff("d", NextDate, Me!EndingDate.Value) >= 0
                            rsAttend.AddNew
                                rsAttend!AttType.Value = "99"
                                rsAttend!AttStaff.Value = 326851 '''' staffs id number which I''l set as a variable later
                                rsAttend!AttDate.Value = NextDate
                                rsAttend.Update
                        NextDate = DateAdd("d", 1, NextDate)
                    Wend
                    rsAttend.Close
                
                    Set rsAttend = Nothing
Go to the top of the page
 
gint32
post Nov 8 2018, 08:18 AM
Post#9



Posts: 284
Joined: 8-May 09
From: Australia


QUOTE
Re-You said "...it was to far off the mark for me to begin from". Do you mean you didn't see how to apply it to your scenario?


I meant I wasn't savvy enough to manipulate it to suit my needs thanks.
Go to the top of the page
 
projecttoday
post Nov 8 2018, 08:54 AM
Post#10


UtterAccess VIP
Posts: 10,252
Joined: 10-February 04
From: South Charleston, WV


QUOTE
So far I have managed to create a query that will create a new table


Are you still creating a new table and, if so, why?

--------------------
Robert Crouser
Go to the top of the page
 
gint32
post Nov 8 2018, 09:43 AM
Post#11



Posts: 284
Joined: 8-May 09
From: Australia


Thanks, and No, I was only going down the make table road because that's all I could do at my level of competency.

Though not the way I wanted, the following combination of code does actually do the trick, but I would rather have stuck to the same type of code structure for both and not jump from DAO.recordset then to a SQL update.

CODE
Dim rsAttend  As DAO.Recordset
                    Dim NextDate    As Date

Me.startingdate = startingdate
Me.EndingDate = EndingDate

                       Set rsAttend = CurrentDb.OpenRecordset("Select Top 1 * From attend")
                        NextDate = Me!startingdate.Value
                    
                    While DateDiff("d", NextDate, Me!EndingDate.Value) >= 0
                            rsAttend.AddNew
                                rsAttend!AttType.Value = "99"
                                rsAttend!AttStaff.Value = 326851 '''' staffs id number which I'll set as a variable later
                                rsAttend!AttDate.Value = NextDate
                                rsAttend.Update
                        NextDate = DateAdd("d", 1, NextDate)
                    Wend
                    rsAttend.Close
                
                    Set rsAttend = Nothing

then

CODE
Dim dtStart As Date, dtEnd As Date, strSQL As String

  dtStart = Me.txtDateStart
  dtEnd = Me.txtDateEnd

  strSQL = "UPDATE tblStaff " & _
           "INNER JOIN Attend " & _
           "        ON tbl_Staff.StaffID = Attend.AttStaff " & _
           "  SET Attend.AttType = 3 " & _
           "WHERE Attend.AttType <>99 " & _
           "  AND Attend.AttStaff = " & [Forms]![CalendarWithSub_BkOffs]![Frm_Sub_StaffBkOffs].[Form]![StaffID]  & _
           "  AND Attend.AttDate >= " & Format(dtStart, "\#yyyy\-mm\-dd\#") & _
           "  AND Attend.AttDate <= " & Format(dtEnd, "\#yyyy\-mm\-dd\#") & ";"
           Debug.Print strSQL
              CurrentDb.Execute strSQL, dbFailOnError

This post has been edited by gint32: Nov 8 2018, 09:44 AM
Go to the top of the page
 
gint32
post Nov 8 2018, 09:23 PM
Post#12



Posts: 284
Joined: 8-May 09
From: Australia


Thanks to everyone who put there time and effort in, I truly appreciate it. smile.gif
Problem solved !!!
Go to the top of the page
 
MadPiet
post Nov 8 2018, 09:39 PM
Post#13



Posts: 2,547
Joined: 27-February 09



If you really wanted to, you could look up UPSERT, but if you were doing this in SQL Server, you could do something like

BEGIN TRANS
UPDATE MyTable....;
INSERT INTO OtherTable...;
COMMIT TRANS

You would just put in a some logic to see if both succeeded, and if not, roll the transaction back. Much easier to debug and write if you do it that way.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2018 - 12:18 AM