Populating Blanks based on prior value

I am connecting two tables of data to combine values, using unique ID.

Goal is to obtain totals in two fields (users / revenue), and for most customers, this works.  For customers paying quarterly, while the revenue is listed for each month, total users only displays 4x.  Can I create a field in my query that looks at the prior value and populate for each customer?  Example:

Customer     Users  Month
ABC Comp     5          1/1/15
ABC Comp     0          2/1/15
ABC Comp     0          3/1/15
ABC Comp     9          5/1/15
ABC Comp     0          6/1/15
ABC Comp     0          7/1/15
ABC Comp     5          8/1/15
CDE Comp     10       1/1/15
CDE Comp     0          2/1/15
CDE Comp     0          3/1/15

In this example, 0 would be updated to 5 for ABC company for Feb and March, and 0 to 9 for June and July.  For CDE Company, 10 would be updated for Feb and Mar...etc..etc.
AliciaVeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Queries work on set theory and it is unlikely that you could get one to do this update since the update is dependent on record position.  If you can sort the data into the sequence you displayed, you can write an update loop to read each record and if users is not zero, save it.  If users is zero, fill it with the last saved value and update the record.  You will need to deal with first and last records separately.
1
Jeffrey CoachmanMIS LiasonCommented:
In a standard database you would not be doing this manually anyway.

If you know that a client will pay the same amount each month of a given quarter, then this could be a function, ...then the function could generate the dates and the amounts.
Unfortunately any function (or system)  to do this could grow to be quite complex (depending on your needs), ...and, will, most likely, tax your system if your datasets are large (function may have to calculate the "previous" month's amount for each row)

In other words, ...Access does not have an quick and easy "AutioFill" utility like Excel does.
But there may be an expert here willing to create (a simple) one for you...?

to me, ...It might be simpler (and faster) just to use the keyboard shortcut: Ctrl+'
or: Ctrl+Shift+"
This keyboard shortcut will copy the entry above
This shortcut works in Excel as well as in Access
1
Jeffrey CoachmanMIS LiasonCommented:
If you still need to see what a code solution might look like , ...here  the code and a sample db:
Dim lngCustID As Long
Dim currAmount As Currency
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tblBilling.InvoiceID, tblBilling.custID, tblBilling.[Invoice Date], tblBilling.InvoiceAmt FROM tblBilling ORDER BY tblBilling.custID, tblBilling.[Invoice Date];")
rst.MoveFirst
currAmount = rst!InvoiceAmt
lngCustID = rst!custID
rst.MoveNext


Do Until rst.EOF
    If rst!custID = lngCustID And rst!InvoiceAmt = 0 Then
        rst.Edit
        rst!InvoiceAmt = currAmount
        rst.Update
    ElseIf rst!custID = lngCustID And rst!InvoiceAmt > 0 Then
        currAmount = rst!InvoiceAmt
    ElseIf rst!custID <> lngCustID And rst!InvoiceAmt > 0 Then
        lngCustID = rst!custID
        currAmount = rst!InvoiceAmt
        rst.Edit
        rst!InvoiceAmt = currAmount
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

Open in new window



JeffCoachman
Access--EEQ28735749-AutoSum.accdb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
To get my code above to work in your db:
First, make a copy of your original table
Then you will have to change my object names to the corresponding names in your db.
0
AliciaVeeAuthor Commented:
Thanks all for your comments and feedback as well as knowlege sharing.

Jeffrey - I am going to try your solution and will respond in a bit.
0
Jeffrey CoachmanMIS LiasonCommented:
ok
0
AliciaVeeAuthor Commented:
Jeffrey,

I downloaded your example db, thanks.

Am not sure how to run the function or how it works?  there are two tables, tblBilling and tblBillingSAF.  Looks like one has all values in InvAmt, and the other has only Qtrly payments, which is how my data (somewhat) looks like.  The Invamt, is actually Users in my db, but I can fix that.  So, Invamt is always populated, on a monthly basis, but the total of users only shows up quarterly -- I need to pull that number into each month.  So, if blank, take the previous number?  Not sure how to use your tool.
0
Jeffrey CoachmanMIS LiasonCommented:
*Download the sample again
Open the database
*Close the opening Form, ...Do Not click the button*
First, examine the tables
Notice that both tables are exactly the same.
I only created the "SAF" version as a backup.
(You can delete the SAF table if you like...)

Note that tblBilling has populated data similar to what you posted.
(a starting value only, ...for each quarter)

Now reopen the form and click the button
When done, close the form and look at tblBilling again...
Note that now the first quarterly values are populated for the subsequent months for that quarter...
1
AliciaVeeAuthor Commented:
Ooooh....!  Yeah, I was thinking maybe that's what happened, that I updated the table before looking, but couldn't be sure, so I went into the table and deleted some data in the months between qtrs and ran the form again, but it kept throwing an error.

Will check it out again.

Question, does the code always check for a specific number of months, or does it simply look for the last value, then populate?  Meaning:

Jan, Feb, empty, Mar value goes into Jan Feb
Apr, May, June, maybe Jul, emply, Sept value, goes into "only" Apr, May, june, Jul.

Will your solution work for me?

Thank you!!  :)
0
AliciaVeeAuthor Commented:
Hi, just got a moment to check out your db and related code.  I don't have an invoice ID for this query (which I've dumped into a new table).

Trying to edit your code to work on a test db but got stuck on whether I need an invoice ID?  There are invoice IDs, however, the query that I need to update doesn't include it.  Guess I can make a new ID...just wondering here?
0
AliciaVeeAuthor Commented:
Jeffery,

I updated your code, attached below.  In addition to changing table name, and field name, I declared the field what would be updated to be LONG and not CURRENCY -- maybe this is where I messed up.  The user totals (of which I am trying to update) is simply a number, no more than 1,000.

the error I am getting is:
Run-time error '94':
Invalid use of Null

============
Private Sub Command0_Click()
Dim lngRevSFCRMID As Long
Dim lngUsers As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tRevBillTtls.NewInvId, tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate], tRevBillTtls.AfterTtlUsers FROM tRevBillTtls ORDER BY tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate];")
rst.MoveFirst
lngUsers = rst!AfterTtlUsers
lngRevSFCRMID = rst!RevSFCRMID
rst.MoveNext


Do Until rst.EOF
    If rst!RevSFCRMID = lngRevSFCRMID And rst!AfterTtlUsers = 0 Then
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    ElseIf rst!RevSFCRMID = lngRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngUsers = rst!AfterTtlUsers
    ElseIf rst!RevSFCRMID <> lngRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngRevSFCRMID = rst!RevSFCRMID
        lngUsers = rst!AfterTtlUsers
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

End Sub
0
AliciaVeeAuthor Commented:
Jeffery, I searched a solution for the error 94 and believe it has to do with null values. I replaced all blanks in the User field with zero (0) and reran your code.

I am now getting another error:
Run-time error '13':
Type mismatch

using the debugger, it appears that its this line:
lngRevSFCRMIN = rst!RevSFCRMID (this is my customer ID)

This field is set to "short text' and looking at your table, your custID is also short text -- so not sure what to do now.
0
Jeffrey CoachmanMIS LiasonCommented:
OK,
Give me a day or so, ...I had minor surgery yesterday and I am just now catching up on my emails...
0
Jeffrey CoachmanMIS LiasonCommented:
...or post a simplified version of your database for me to examine...

In any event, Type MisMatch error means the datatypes do not match.

In Access the datatypes must match *exactly*, to be syntactically correct.

So however "RevSFCRMID" is set as the *FieldSize* in the table, ...you must declare the variable with the same type.
0
AliciaVeeAuthor Commented:
Thanks Jeffrey -- hope you are doing better, feeling well.

I will relook at the datatype and also post a sample db if needed.

thanks,
Alicia
0
AliciaVeeAuthor Commented:
Jeffrey,

Okay, I changed my customerID to be declared as string, and it seemed to fix my errors. (Yay!!).  Your code is working beautifully, however, one issue and am hoping the code can be tweaked.  It appears that if there are blanks before the first value of a "user" (this is currency amount in your table, but is User counts in my db) the value stays null (or zero) and it only updates records from a go forward position.  So, if the record is blank, the value should be the next value, popluated until a new value is found.  In the ABC Company example attached, you will see that the first value didn't appear until May 2015, so all months before should also have the value of 1.  The code did populate the go forward months.  In the DEF Company, the code did a great job capturing the change of users -- which is perfect!!

Here is the code I've edited to work on my test db:

================================

Private Sub Command0_Click()
Dim strRevSFCRMID As String
Dim lngUsers As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT tRevBillTtls.NewInvId, tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate], tRevBillTtls.AfterTtlUsers FROM tRevBillTtls ORDER BY tRevBillTtls.RevSFCRMID, tRevBillTtls.[SortRevDate];")
rst.MoveFirst
lngUsers = rst!AfterTtlUsers
strRevSFCRMID = rst!RevSFCRMID
rst.MoveNext


Do Until rst.EOF
    If rst!RevSFCRMID = strRevSFCRMID And rst!AfterTtlUsers = 0 Then
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    ElseIf rst!RevSFCRMID = strRevSFCRMID And rst!AfterTtlUsers > 0 Then
        lngUsers = rst!AfterTtlUsers
    ElseIf rst!RevSFCRMID <> strRevSFCRMID And rst!AfterTtlUsers > 0 Then
        strRevSFCRMID = rst!RevSFCRMID
        lngUsers = rst!AfterTtlUsers
        rst.Edit
        rst!AfterTtlUsers = lngUsers
        rst.Update
    End If
    rst.MoveNext
Loop

MsgBox "Done"

rst.Close
Set rst = Nothing

End Sub
MissingValusPicture.PNG
0
Jeffrey CoachmanMIS LiasonCommented:
Your graphical example there, is not standard data entry procedure for a Database table.
In fact, with the highlighting, ...it looks more like an excel sheet...
So first can you explain exactly what your screenshot there is made from?

Based on what you have now posted, I am not sure the code can be modified to fit with this design...
In your image you are showing "Blank Records", ...a concept that has no meaning in a true database table structure...
(Note that the code I originally posted, addressed the original question you posted directly.)

In a true database, data entry is done once.
You should not have to use automation if "Fill in" missing data (in existing records), ...that should have already been entered manually(or with code).
As I stated, Access is not Excel.
In Access there is really no such thing as a "Blank Row" (as you are showing in your image)

In a true table/database, ...all the required info would be entered "once" ( in a "set up" form), then validated.
Then the code could insert the records.
For example,  ...image an interface where you are asked:
"Please enter the year, and starting values for each customer."
You would enter values like:
Year: 2015
Customer: XYZ
Starting Quarterly value: 450
Year: 2015
Customer: ABC
Starting Quarterly value: 277
...in a *single view form* (Not in a table)

After doing so, ...the function/system would simply generate the record data sequentially, directly into the table, ...like so:
Year, Cust, Quarter, Amount
2015, XYZ, Q1, 450
2015, XYZ, Q2, 450
2015, XYZ, Q3, 450
2015, XYZ, Q4, 450
2015, ABC, Q1, 277
2015, ABC, Q2, 277
2015, ABC, Q3, 277
2015, ABC, Q4, 277
In other words, ...The code would simply add the entire record(s).
Here there are no "existing records" that need to be "filled in", ...nor any "blank" records that need to be skipped.
The system would simply start with a new record, and create all of the records, based on the parameters you provided in the "Set Up " form.
But a system like this would go far beyond your original question...

In this case you should re-evaluate this system so that it aligns itself with the way data is entered/stored in a true Access table structure, ...else, every time you have data like this, ...you will need a custom (non-standard) way of dealing with it.

If, ...on the other hand, ...this is Excel data you are trying to insert into Access, ...then there is very simple code you can use to "Delete Blank Rows",
http://www.experts-exchange.com/searchResults.jsp?searchType=ALL&searchTerms=Excel+delete+blank+rows&searchSubmit=&asSubmit=true&asIgnored=true&asNoSuggestionNoResults=true
...then my code should work

Let me know

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AliciaVeeAuthor Commented:
Jeff,

Appologies for the delay as I was on vacation.

Thanks for your very detailed explanation and yes, it does make sense to me.  The image was in Excel as I exported the data into Excel from Access to highlight the missing values.

The db I am trying to create will only be used to link up two data sets from two different systems and then further extract required information from a monthly basis.  One side of the data breaks out revenue on a monthly basis, however, the other side of the data, (billing) bills only when needed, could be monthly, or quarterly or annual -- and that is the issue.  I need a data point in the billing system that isn't in the revenue system and thought linking up data sets might do the trick.

Your solution has gotten me a lot farther than trying to manually update data in Excel, so that you!  It is helpful, nonetheless.

Hope you are feeling better and fully recovered.
0
AliciaVeeAuthor Commented:
Great communication with added knowledge sharing.
0
Jeffrey CoachmanMIS LiasonCommented:
ok, thanks

After I had a fuller understanding of what you were looking for, I realized that the structure was not a good fit for Access.

You can take the code I posted and shop it around form a modification to work for your data...
Sorry I could not help more...
:-(

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.