In Access 2002, I have created a Make Table query to add a new field “Base$” through a custom function, moving average, that averages each 8 weeks of data. However, I also need to calculate a different result if there is an entry “X” in the Flag field--where there is a Flag "X", I want to duplicate the 8-week average number of the previous record. What is the VBA code to provide that alternate result? Here is a sample of the data, followed by the custom function.
ID WkEnd ItemNbr Sales$ Flag
464 9/3/05 Item1 22397
527 9/10/05 Item1 21742
591 10/22/05 Item1 22392
649 9/17/05 Item1 21581
715 9/24/05 Item1 21747
780 10/1/05 Item1 21424 X
845 10/8/05 Item1 21477
910 10/15/05 Item1 23806
984 10/29/05 Item1 22927
1049 11/5/05 Item1 33252 X
1114 11/12/05 Item1 20768
1179 11/19/05 Item1 20247
1447 12/17/05 Item1 26336
1513 12/24/05 Item1 32947
1583 12/31/05 Item1 20829
1651 9/3/05 Item2 23097
1719 9/10/05 Item2 44363 X
1835 10/22/05 Item2 38518
1902 9/17/05 Item2 32232
1970 9/24/05 Item2 32468
2036 10/1/05 Item2 41633 X
2105 10/8/05 Item2 27651
2174 10/15/05 Item2 26819 - Function MovAvg(ItemNbr, startDate, period As Integer)
-
-
Dim rst As DAO.Recordset
-
Dim sql As String
-
Dim ma As Currency
-
Dim n As Integer
-
-
sql = "Select * from tblSalesData "
-
sql = sql & "where ItemNbr = '" & ItemNbr & "'"
-
sql = sql & " and WkEnd <= #" & startDate & "#"
-
sql = sql & " order by WkEnd"
-
-
Set rst = CurrentDb.OpenRecordset(sql)
-
rst.MoveLast
-
For n = 0 To period - 1
-
If rst.BOF Then
-
MovAvg = 0
-
Exit Function
-
Else
-
ma = ma + rst.Fields("Sales$")
-
End If
-
rst.MovePrevious
-
Next n
-
rst.Close
-
MovAvg = ma / period
-
-
End Function
9 1582
Not 100% sure what you need but I think you should use a variable that gets updated with the value of the field you need to look at in hindsight.
Then, when you see your 'x' data, you'll have the variable from the previous record stored.
Not 100% sure what you need but I think you should use a variable that gets updated with the value of the field you need to look at in hindsight.
Then, when you see your 'x' data, you'll have the variable from the previous record stored.
Thank you for your reply. Your assumption is correct. I'm not very adapt at VBA coding. Can you provide the code to accomplish your suggestion? I'm trying to learn VBA on my own, but I seem to have a mental block on the steps involved in setting variables and then manipulating them.
Thank you for your reply. Your assumption is correct. I'm not very adapt at VBA coding. Can you provide the code to accomplish your suggestion? I'm trying to learn VBA on my own, but I seem to have a mental block on the steps involved in setting variables and then manipulating them.
Dimension a variable (done once at the beginning of your sub)
Then, use it to store you value: -
myVar = rs("Field1").Value & ""
-
Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.
Dimension a variable (done once at the beginning of your sub)
Then, use it to store you value: -
myVar = rs("Field1").Value & ""
-
Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.
Thanks again for your help and patience.... but I'm still need help with coding an "IF" scenario. If you'll look at my desired results for the "Base$", you'll see that if the value of "promo" is null, I want to calculate Base$ as a moving 8-week average. However, if the value of "promo" is "X", I want to copy the previous record's value for Base$. See an example of my expected results for Base$, particularly for weeks ending 17 Sep through 01 Oct.
ItemNbr Week Ending Sales $ Base$ Promo
10025 16-Jul-05 11349 0
10025 23-Jul-05 13593 0
10025 30-Jul-05 12709 0
10025 06-Aug-05 10104 0
10025 13-Aug-05 8693 0
10025 20-Aug-05 8712 0
10025 27-Aug-05 8532 0
10025 03-Sep-05 9553 10406
10025 10-Sep-05 9713 10201
10025 17-Sep-05 14827 10355
10025 24-Sep-05 13938 10355 X
10025 01-Oct-05 14102 10355 X
10025 08-Oct-05 11052 11304
I can accomplish my goals by using an Iif statement in an Access Make Table query to get the Base$ for "non-promo" weeks and then running a Update query with Dlookup to retrieve the previous record Base$, but I was hoping to accomplish this in one VBA function -- moving average -- as indicated in my initial post.
Dimension a variable (done once at the beginning of your sub)
Then, use it to store you value: -
myVar = rs("Field1").Value & ""
-
Then, you would check for the X value. If it exists, you'll have previous records value. So make sure you check for X before giving the variable the latest value.
How do I "check for the X value"?
How do I "check for the X value"?
What I meant by the X variable was to persist (save) the value you needed to perform the calculation when coming across X on the next iteration.
It seems that your logic is based on a backwards read of a subset of records and not on the entire table. Since there are a gazillion ways of doing things, your best bet is to focus on your own logic and take it from there.
What I meant by the X variable was to persist (save) the value you needed to perform the calculation when coming across X on the next iteration.
It seems that your logic is based on a backwards read of a subset of records and not on the entire table. Since there are a gazillion ways of doing things, your best bet is to focus on your own logic and take it from there.
Thanks, VBWheaties for your patience. My logic is "based on a backwards read of a subset of records". My VBA coding skills are extremely limited. I have the calculations working in Excel where each subgroup is on a separate worksheet, but I need to move to Access because of the increasing number of worksheets.
I have created a working moving average custom function in Access (referenced earlier) which I use in a Make Table query, which works fine. However, I'm having problem updating the values, when the function returns a zero value. These are my needs --
1 - Moving Average function -- Calculates Weekly 8-week moving average for each of 80+ items in my database.
2 - When an item is "on promotion", indicated by an "X" value in the promotion field, I want to repeat the Moving Average value for (1) that item and (2) in the previous week.
I have the above working in an Excel formula, but because of the size of the database, I need to a way to do the same in Access.
Thanks, again. If I don't hear back, I'll assume you've given up on me, and I'll start over elsewhere.
Thanks, VBWheaties for your patience. My logic is "based on a backwards read of a subset of records". My VBA coding skills are extremely limited. I have the calculations working in Excel where each subgroup is on a separate worksheet, but I need to move to Access because of the increasing number of worksheets.
I have created a working moving average custom function in Access (referenced earlier) which I use in a Make Table query, which works fine. However, I'm having problem updating the values, when the function returns a zero value. These are my needs --
1 - Moving Average function -- Calculates Weekly 8-week moving average for each of 80+ items in my database.
2 - When an item is "on promotion", indicated by an "X" value in the promotion field, I want to repeat the Moving Average value for (1) that item and (2) in the previous week.
I have the above working in an Excel formula, but because of the size of the database, I need to a way to do the same in Access.
Thanks, again. If I don't hear back, I'll assume you've given up on me, and I'll start over elsewhere.
I haven't given up, I just don't want to dilute the logic with mine that may cause more confusion.
For the average, when you have your values in Access, you can get the average like so: -
SELECT AVG(NumberField) AS 8WeekAverage
-
FROM MyTable
-
WHERE MyItem = 'ThisItem'
-
If you wanted to show averages for all items: -
SELECT AVG(NumberField) AS 8WeekAverage, ItemNumber
-
FROM MyTable
-
GROUP BY ItemNumber
-
If that doesn't do it, post again so others can have a go at it.
I haven't given up, I just don't want to dilute the logic with mine that may cause more confusion.
For the average, when you have your values in Access, you can get the average like so: -
SELECT AVG(NumberField) AS 8WeekAverage
-
FROM MyTable
-
WHERE MyItem = 'ThisItem'
-
If you wanted to show averages for all items: -
SELECT AVG(NumberField) AS 8WeekAverage, ItemNumber
-
FROM MyTable
-
GROUP BY ItemNumber
-
If that doesn't do it, post again so others can have a go at it.
Thanks... I already have the averages for all items. My problem is not in calculating the averages -- it is updating the table to copy the previous item weekly average when the week is a promo week. A promo week is identified in my table's field "Promo", where "Promo" = "X". In my Access query, I calculated the weekly averages where Promo <> "X".
Again, thanks for your help. I'll work tonight on how to clarify my question and repost.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: mark |
last post by:
Access2000
How do I write a query that combines the CTC field from each record below into
one record? I need to concatenate the CTC field with a separator, like below:
BattID VehicleID STDATE STTIME CTC
LKO500HF 00000000 10/27/2003 4:13:51 AM 4
LKO500HF 00000000 10/27/2003 5:13:51 AM 5
LKO500HF 00000000 10/27/2003 10:13:51 AM 6...
|
by: Gene |
last post by:
When entering a record in a form, I would like a value in a field of
the previous record to be entered automatically into a different field
of the current record. Which way should I go? Is it also possible to
do this based on criteria from the previous record?
|
by: Daniel Tan |
last post by:
Are there anyway to copy rows of records from one query to another
query and then hide the records in source query ? Pls advise. Thanks.
Regards,
Daniel
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.
I have a Tab...
|
by: Bruce |
last post by:
What is the most efficient (ie easiest for me) way to copy a record
from an existing table, appending it to that same table but
incrementing a field's value? (code is fine)
Cheers very muchly
Bruce
| |
by: Chris |
last post by:
Before I started to create table, etc to track unique form field record
number assigments I thought I'd check to see if there is now a better way to
do this in .NET.
I have a parent form (table) and children form (table). Relationship equals
one to many. I'd like to auto number the fields accordingly and traditionaly
I assign a unique...
|
by: Brett |
last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a
record in table2, then the not null fields in table1 that correspond to
the records in table1 needs to be updated to match the field in table2.
What I have is a form that is linked to Table2. If the users want to
change a field in the main database (table1), they fill the...
|
by: Swinky |
last post by:
I hope someone can help...I feel like I'm walking in the dark without a
flashlight (I'm NOT a programmer but have been called to task to do
some work in Access that is above my head).
I have code that will successfully copy a record and append the
information to a new record in the same table (parent table) within a
form. However, there...
|
by: roveagh1 |
last post by:
Hi
I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same logic for a date field. i.e.
theValue.defaultvalue = "#" & theValue.value & "#"
I can't get this to work for some reason and I can't figure out...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
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. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
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.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |