MS Access Table auto populate recored

Posted on 2013-02-03
Last Modified: 2013-02-06
Hi All,

I’m creating an MS 2007 Access database for my bills.  Some bills are due on the 5th of every month and some are due on the 25th of every month.   In a table, is it possible to have a column that will be populated via a form such as entering the due date as 10-25-12?  And another column in the same table auto display “October 2012” (Month Due)?  Same thing if I entered 10-5-12 the other column will display “October 2012”.

Thanks in advance
Question by:Reyesrj

Expert Comment

ID: 38850075
NO.. It wont be possible in Table..
LVL 40

Expert Comment

ID: 38850128
You can use calculated columns in table:
but usually you don't need it.
On your form you can show same data with different formats.
For example, if field txtDateDue is 10-25-12 (I hope it id date field, not text), other field could be:
and in format you can manually enter:

Author Comment

ID: 38853238
Would it be possible to run a query that will display the data for all bills due on 10-05-12 and 10-25-12 only.  Some type of criteria range?  If so, please show me.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38853830
<And another column in the same table auto display “October 2012” (Month Due)?  Same thing if I entered 10-5-12 the other column will display “October 2012”.>
You may wish to rethink this design...
Your requests here really does not fit with many database design "best practices".
What is your skill level with Access database design?

1. Adding Columns dynamically is a tip-off of an "un-normalized" structure.

It is far easier to populate "Records" with the recurring dates
So I am not quite sure of why you want new columns...

Besides, your new requirement of:
<a query that will display the data for all bills due on 10-05-12 and 10-25-12 only.>
...would be much easier if the dates were in records...
Think about it, ...after 5 years you will have 60 columns.
Access has a finite number of fields you can add, (255) then what...?

2. You also did not post any details on this system.
I see at least two tables:


This is not counting tables for your dropdown boxes for: categories, Late/Ontime, Fees, Acct Type, Institution, CheckNumber, confirmation code, ...etc

3. It is also not clear how far in advance to want the dates populated for...
Typically you may want to do this yearly.

There are many more things you need to also consider here...
For all of these reasons, ...and more, ...I would just by a Personal finance program to do this all for you.
Even for "simple" Personal finance needs, this may evolve into something more complex than you bargained for.
(Split deposits, Partial payments, reminders, alerts, ..etc)
An off-the-shelf product will have worked out all the bugs and will have Support, upgrades, web utilities (upload/download bank data, tax help, reconciliations, overdraws, ..etc)
Finally in an off the shelf product, the dates are simply "displayed" in a calendar, thus there is no need for any code to "generate" dates.
...or have to worry about the recurrence (and leap years, Holidays...ect),
...or if a user clicks the "Generate Dates" button twice by accident, or if a date is deleted by accident.
...Or remembering to click the Generate date button every time the recurrence end date is reached...
....or updating all the dates if the start date changes... other words, ...there are too many potential unforeseen contingencies to worry about here.

I have been working with Access since Access 97 and I just did not see any advantage to creating something like this in Access.
Quicken deluxe is $60 USD and your done.
So if you value your time at $20/hr, ...this project will take much longer than the 3 hr break-even time (3hrs*$20/hr=$60)
Again, I see no real reason to re-invent the wheel here.

But you can try the attached sample DB, if you are still interested.
Generate the dates for both "Bills , then run all three queries...

If you still want to go your original "Columns" way, then please continue on with als315.


Author Comment

ID: 38853906
Thank you Jeff,

I’m fairly new to Ms Access. The purpose of me creating this database is purely for training.  Just something I chose to do to learn MS Access.  I think I’m above average in MS Excel.  I was in payroll and it forced me to learn excel to make the job easier.  And it did!

As for Month Due column reflecting the Date Due column, I can do this in excel with a vlookup formula.  I was inquiring if a lookup function, like vlookup, is available in Access.  The Date Due column is entered into the table via the form.  I was thinking if the Date Due column for a record showed 10-5-12 is there a lookup function that will auto populate the Month Due column, for that record, with October 2012.  The purpose is to compare bills paid in October last year to October this year.  This is just self training.

I agree there are better applications than Access for this purpose.

Any and all advise is welcomed.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38854048
A basic "Lookup" in Access has a syntax like this:
Dlookup("YourField", "YourTable", YourCriteria")

So to lookup a payment in a certain month (March, 2012, for example) you would do something like this:
YourOldPayment=Dlookup("PaymentAmount","tblPayments", "Month(PaymentDate)=4 And Year(PaymentDate)=2012" )

Obviously here you could use values from another place to specify the Month and year.

But know that this is still rather "odd"
I know this is just Training,...but why practice something that is nonstandard and does not follow standard DB design rules?

For example, why waste time "Practicing" how to insert a car engine backwards?

Perhaps this would all be clearer if you posted a sample of the Excel file you are trying to replicate with Access.


Author Comment

ID: 38854633
Thank you for being understanding.
Okay, I know excel is not access so maybe I'm trying to apply excel into access.  Such as, inserting a car engine backwards (LOL).  I attached an excel spreadsheet with a vlookup formula.  But if it can't be done that’s fine.  Is the example you gave VBA?

LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 38855678
What you can do is create a "table" to enter the sample dates, then create a query from this table to "lookup" the corresponding date in the other table.
But here again I am confused by your design.
Why bother looking up the Month/Year, when you can look up the date, then format it as Month/year.
In other words, the Month/Year is just a formatted version of the date, so I don't see a real need to look up a formatted date..
So if you wanted to do this you would have to create yet another query to create the formatted date, then use the first query to lookup the formatted date in the second query...
In my sample I simply looked up the date, then formatted this looked up date as Month/year

The other kicker here is that you are not stating what you want displayed if you enter a date with no corresponding date.
In your XL file, #N/A is returned for invalid dates, so again, in my sample, #Error is returned.
Obviously, you can avoid this with an even more complex formula, ...but now you can see how complex this design may need to become...

But again, you can do anything you want.
If your goal here is to simply learn how a lookup works in Access you can try something like this (See: qryLookUpPayments)


Author Closing Comment

ID: 38861975
Thanks Jeff,

I will work on the queries and just mess around with it.  This has been very helpful.  I’ll be posting more questions later.

Thanks again.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question