473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Auto populate empty field

2
Beginner here - I need to create a report that shows two fields, [TranDate] and [LoanAmount]. I have a table that a user inputs the [LoanAmount] into every business day. Because we do numerous calculations using the [LoanAmount] field, I need the [LoanAmount] for EVERY day, including weekends. I have created a query that displays all dates in the current year along with the [LoanAmount] for dates that it was entered.

My Problem: I would like the empty [LoanAmount] records to auto populate with the [LoanAmount] from the previous day. So if the loan amount on 3/4/16 was $100 and the 5th and 6th were weekend days, they would populate with $100 as well.

I have tried to come up with an If statement, but have failed. Any help is greatly appreciated!
Mar 16 '16 #1
3 952
NeoPa
32,556 Expert Mod 16PB
You may be a beginner, but your requirement is certainly not basic.

Bearing in mind that databases work fundamentally to Set Theory, this is a long way from being trivial. First you need a record source of some kind that will include all the required dates. Once you have that you can link it into your loan data - but! The but is that you need to link it in such a way that the date required is greater than or equal to the date from the loan data. It must also match only the greatest matching date from there.

This requires the loan data to have an identifying PK value.

Why don't you include in your next post the layout of the data you have with names etc of tables and fields then we can look at showing how it may be done. This is all info that's part of the original question so please take note for any other questions you may have. it's really quite important to include the whole question, with all the relevant details, in the first post.
Mar 16 '16 #2
bkfb
2
NeoPa-

This was an inherited database so please bear with me.

I have a table called tblLoanAmounts with the following fields: TranDate, LoanAmount, FundingAccount, PurchaseAccount, Notes. I also created qryDates. This query is based off of tblDates which contains the 366 days of the year in date format (the field is TranDate). From tblLoanAmounts and qryDates I created qryQuarterly that contains the fields: TranDate and LoanAmount. I have set the criteria to only show dates within the current quarter.

In qryQuarterly there is a left join between tblLoanAmounts and qryDates on the TranDate. This produces all dates in the current quarter and all Loan amounts entered on those specific dates. (leaving the weekend dates blank of course) Hopefully this is the information you were looking for....
Mar 17 '16 #3
NeoPa
32,556 Expert Mod 16PB
OK. Well I'm going to start by reminding you of the requirement for an ID field for your [tblLoanAmounts] table. I'm going to assume you've added one called [TranID] which is defined as AutoNumber.

Table = [tblLoanAmounts]
Expand|Select|Wrap|Line Numbers
  1. FieldName   Type
  2. TranID      AutoNumber
  3. TranDate    Date
  4. LoanAmount  Number
  5. ...
Table = [tblDates]
Expand|Select|Wrap|Line Numbers
  1. FieldName   Type
  2. TranDate    Date
I still don't know if there is any other of the fields you want to GROUP BY, so I've ignored that part and assumed no other groupings. If that's the case then [TranDate] would probably work as a unique reference, but in case it's not I've added [TranID] for the extra flexibility.

The SQL for the query should be of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [TranDate]
  2.        , (SELECT [LoanAmount]
  3.           FROM   [tblLoanAmounts] AS [tLA]
  4.           WHERE  ([TranID]=(SELECT [TranID]
  5.                             FROM   [tblLoanAmounts]
  6.                             WHERE  ([TranDate]=(SELECT Max([TranDate]) AS [MaxTD]
  7.                                                 FROM   [tblLoanAmounts]
  8.                                                 WHERE  ([TranDate]<=[tD].[TranDate])))))) AS [LoanAmount]
  9. FROM     [tblDates] AS [tD]
  10. ORDER BY [TranDate]
Starting from the innermost SELECT query :
  1. Lines #6- #8 Return the furthest forward [TranDate] in [tblLoanAmounts] that is either on or before the [tblDates].[TranDate] from the outermost query.
  2. Lines #4- #8 Return the [TranID] for the [tblLoanAmounts] record that matches A above.
  3. Lines #2- #8 Return the [LoanAmount] from the record identified by B above.
  4. Lines #1-#10 Return the [tranDate] from [tblDates] along with the value from C above and sort all records by [tblDates].[TranDate]
With records in [tblDates] running from 1st March 2016 to 10th March 2016 and the following data in [tblLoanAmounts] I got the results shown at the bottom.
Expand|Select|Wrap|Line Numbers
  1. TranID  TranDate   LoanAmount
  2.    1   03/03/2016  20
  3.    2   05/03/2016  50
  4.    3   08/03/2016  100
Expand|Select|Wrap|Line Numbers
  1.  TranDate   LoanAmount
  2. 01/03/2016  
  3. 02/03/2016  
  4. 03/03/2016   £20.00
  5. 04/03/2016   £20.00
  6. 05/03/2016   £50.00
  7. 06/03/2016   £50.00
  8. 07/03/2016   £50.00
  9. 08/03/2016  £100.00
  10. 09/03/2016  £100.00
  11. 10/03/2016  £100.00
Mar 17 '16 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Alistair | last post by:
this has been driving me nuts for over an hour now. I have a DB with a date field that is empty Because of this the records sometimes get included in searches because their contents are less...
1
by: Angela Byars | last post by:
I imported several .dbf's from another application to use as the basis for my form. In addition to displaying the records that were created with the original application my users will be adding...
2
by: keri | last post by:
Hi, I'm still struggling with my tables - frustrating. If a field in table one is completed I want the following field to auto populate (dependng on the answer to the first field). I have...
3
by: jacklindsay | last post by:
Hello smarter people than me I am creating a database for college, and have requested some help, but they are unable to help me. ( im obviously too eager) anyway, im creating a database on...
4
by: whamo | last post by:
I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt...
1
by: Paul | last post by:
I have a table with a list of departments & Dept IDs (table a) and also a master table (table b). When staff are completing an input (this is saved in the master table b) they fill in multiple...
2
by: paulyXvpf | last post by:
Hi All, FACTS: > I've created a VB.NET 2005 form, with a SQL Server 2000 backend > The form has about 30 fields that populate 30 columns in the SQL database > The form has mostly text feilds,...
5
tsubasa
by: tsubasa | last post by:
I have a form in ASP.net that has a checkbox option to auto populate the form with the user infomation that is held in an table. When I select the checkbox the auto populate works well with texboxes...
9
by: yappy77 | last post by:
I want my "requalify date" to auto populate to the beginning of the month in the following year when my "liability date" gets entered/changed. Example: Liablity date = 11/15/2010; 7/31/2011 ...
1
by: blue310 | last post by:
Hi I am need of some help with my database design. I am no expert to Access/VBA by any means. I have a form (see attached) which has a field called PropertyID. Originally I had Indexed set to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.