MS Access Table auto populate recored

Posted on 2013-02-03
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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...
...in 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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