I can't believe it but I don't have a clue how to update a table using VBA!
What I'm trying to do is:
1) Do a search on the last 3 periods turnover for one client in particular and check to see if they are all over 60k
2) Go to a table which states rebate rates for each client and change that one clients rate based on whether they are over or under 60k for the 3 months
I'm assuming for part 1, I'd have to set up a 3 record array and have a SQLquery feed the results of a check for the last 3 months turnover into it, and then check each part of the array for the 60k mark.
Then part 2 I would have to update the relevant field. Although I'm not entirely sure how to go and pinpoint that record and update it.
Point out any flaws with my part 1 assumption. Thats how I believe I'd do it.
6 4235 Rabbit 12,516
Recognized Expert Moderator MVP
I don't know what "last 3 periods turnover" means. Can you provide some pseudo data?
Sorry. Entries like
Date field Amount Field
2007-05 £40000
2007-06 £45000
2007-07 £55000
2007-08 £60000
I'm having a go at writing the bit where I find the last 3 periods turnover, am I doing this in a totally long winded way?? -
-
Sub TweakRebates()
-
-
Set cnn = CurrentProject.Connection
-
Set Tweak = New ADODB.Recordset
-
Dim Rec As Record
-
Set Rec = New Record
-
-
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
-
& " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].[Base Amount], [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
-
& " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
-
& " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))" _
-
-
With Tweak
-
.Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
-
End With
-
-
a = 1
-
-
Set Tweak = Rec.GetChildren
-
-
-
While Not Tweak.EOF
-
Set MonthCheck(a) = Tweak(a)
-
Debug.Print MonthCheck(2)
-
Tweak.MoveNext
-
a = a + 1
-
Wend
-
-
-
Debug.Print frcSQL
-
-
End Sub
-
-
What I'm doing is running a query and populating a recordset with the 3 periods turnover in question. I can't figure out how to reference them.
What I was going to do was load them into an array and check to see if they were all over 60k.
Now is this totally unnecessary? Because what I want is quite simple, but the process of doing it is getting increasingly more and more difficult. You should be able to see from the above that I'm not entirely used to using Recordsets and generally bumble about using F1 help trying different techniques.
Right, I've figured out the above now: -
-
Sub TweakRebates()
-
-
Set cnn = CurrentProject.Connection
-
Set Tweak = New ADODB.Recordset
-
-
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
-
& " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
-
& " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
-
& " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))"
-
' Debug.Print frcSQL
-
With Tweak
-
.Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
-
End With
-
-
a = 1
-
-
While Not Tweak.EOF
-
Set MonthCheck(a) = Tweak![SumOfBase Amount]
-
Debug.Print MonthCheck(a)
-
Tweak.MoveNext
-
a = a + 1
-
Wend
-
-
End Sub
-
-
This will help me identify if the periods all go over the £60k turnover amount.
Now I have to figure out how to get into the table of rebate rates, go to the relevant suppliers record and amend the rebate rate.
I would assume I could use a find record to get to the relevant place BUT how to change the correct field?
Would an update SQL query work in this case?
And finally, my solution which works! -
Sub FRCTweakRebates()
-
-
' the below skips all of this stuff if its not necessary
-
-
If (Year(Period) <= 2007 And Month(Period) < 8) And (Year(Period) = 2007 And Month(Period) > 4) Then
-
RebateToSet = 1
-
GoTo SkipTo
-
ElseIf (Year(Period) <= 2007 And Month(Period) <= 4) Then
-
RebateToSet = 3
-
GoTo SkipTo
-
End If
-
-
'sets up recordset to hold the last 3 months from period specified turnover in drop down box
-
-
Set cnn = CurrentProject.Connection
-
Set Tweak = New ADODB.Recordset
-
-
frcSQL = "SELECT (Sum([Rebates_CBS Transaction Data].[Base Amount])*-1) AS [SumOfBase Amount]" _
-
& " from [Rebates_CBS Transaction Data] GROUP BY [Rebates_CBS Transaction Data].Name, [Rebates_CBS Transaction Data].[Rebate Date]" _
-
& " HAVING ((([Rebates_CBS Transaction Data].[Rebate Date])='" & Period & "' Or " _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period1 & "' Or" _
-
& " ([Rebates_CBS Transaction Data].[Rebate Date])='" & Period2 & "')" _
-
& " AND (([Rebates_CBS Transaction Data].Name)='Frc Furniture Resource Centre'))"
-
' Debug.Print frcSQL
-
With Tweak
-
.Open frcSQL, cnn, adOpenForwardOnly, adLockReadOnly
-
End With
-
-
a = 1
-
-
' writes the turnover to an array
-
-
While Not Tweak.EOF
-
MonthCheck(a) = Tweak![SumOfBase Amount]
-
Debug.Print MonthCheck(a)
-
Tweak.MoveNext
-
a = a + 1
-
Wend
-
-
RebateToSet = 0
-
-
' test to see if they pass the 60k mark
-
-
FlagTest = MonthCheck(1) > 60000 And MonthCheck(2) > 60000 And MonthCheck(3) > 60000
-
-
' sets rebate based on test
-
-
If FlagTest = True Then
-
RebateToSet = 1
-
ElseIf FlagTest = False Then
-
RebateToSet = 2
-
End If
-
-
Tweak.Close
-
cnn.Close
-
Set cnn = Nothing
-
Set Tweak = Nothing
-
-
SkipTo:
-
-
'automatically updates the FRC PFH Rebate amount accordingly based on date and/or 60k check
-
-
Set cnn2 = CurrentProject.Connection
-
Set RebTab = New ADODB.Recordset
-
-
RebTabSQL = "SELECT Rebates_Supplier.PfHRebateAmt from Rebates_Supplier WHERE " _
-
& "(((Rebates_Supplier.Name) = 'Frc Furniture Resource Centre'))"
-
-
RebTab.Open RebTabSQL, cnn2, adOpenKeyset, adLockOptimistic, adCmdText
-
-
RebTab!PfHRebateAmt = RebateToSet
-
RebTab.Update
-
-
cnn2.Close
-
Set cnn2 = Nothing
-
-
-
-
End Sub
-
Let me know if I've really beat around the bush on this.
Rabbit 12,516
Recognized Expert Moderator MVP
I suspect you can do what you want using subqueries but I have trouble following what it is that you want to do.
I only know of two fields and what they represent in your table. You've given me [Rebate Date] and [Amount] as your relevant fields. But you mention a bunch of other fields and I don't know what they represent or how they relate to those two fields.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: avinash |
last post by:
hi myself avi
i am developing one appliacaion in which i am using vb 6 as front end,
adodb as database library and sql sever 7 as backend.
i want to update one table for which i required data from other table. and
iretrive data from second table by giving some condition. when i get data,
then to update first table i need to use do while loop. instead of that i
want to use select statement directly in update query.
plz give me some help....
|
by: David Shorthouse |
last post by:
Hello folks,
I have a problem with an update query on an asp not updating the table
in an Access db. The code runs, I have no errors, but when I examine the
table, nothing was updated. The query works as it should in Access. Could
anyone direct me to some sources that describe solutions to what is likely a
common problem? The strange thing is, I have another update query for a
different db on an asp that works just great. Is there...
|
by: cainlevy |
last post by:
I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table...
|
by: mo |
last post by:
I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite
get it.
I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET...
|
by: rdraider |
last post by:
I am looking for some assistance with an update query that needs to link 3
tables:
This query ran and reported over 230,000 records affected but did not change
the field I wanted changed, not sure what it did.
I did notice that the "name" in "GM_NAMES.name" was colored blue in Query
Analyzer. Is it bad to name a column "name"?
UPDATE ABSENCES
set CustomerContactID = cicntp.cnt_id
| |
by: Brandon Mackie |
last post by:
I am incredibly new to access and trying to learn as I go. I have set up a
few queries one of which is executed by a docmd.runsql in visual basic.
Because it is an update query it asks for confirmation every time it wants
to update the table. Is there any way to get around this?
|
by: deko |
last post by:
I'm trying to update the address record of an existing record in my mdb with
values from another existing record in the same table. In pseudo code it
might look like this:
UPDATE tblAddress SET
AddressDescription of Entity 456 = AddressDescription of Entity_ID 123
Address1 of Entity 456 = Address1 of Entity_ID 123
City of Entity 456 = City of Entity_ID 123
|
by: Tony Wainwright |
last post by:
Hi guys
I'm pretty new to mysql and was just wondering if you can update multiple
fields at the same time i.e:
UPDATE tablename SET field1 = value1 AND field2 = value 2 etc... WHERE
field0 = value;
Cheers
Tony
|
by: JamieF |
last post by:
Sorry if I've missed something obvious, but I'm trying to do a really basic update query in Access 2007, and I can't get it to work.
UPDATE DISC INNER JOIN DISC2 ON DISC.DiscID = DISC2.DiscID SET DISC.Filename = .;
The Filename field in DISC is null throughout. Some of the values (62 records) in DISC2 are populated. Other than this, both tables are identical.
I'm trying to get the DISC2 values into DISC. The results grid shows just...
|
by: giovannino |
last post by:
Dear all,
I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !).
1) Given that I'm not a programmer I can't understand why numbering doesn't start always, running more times the update query, from the same starting parameter assigned (1000000000).
It seems to me that it takes memory last number calculated and running prox update it starts from last table row updated. I need...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |