Solved

MS Access Table auto populate recored

Posted on 2013-02-03
9
367 Views
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
0
Comment
Question by:Reyesrj
9 Comments
 
LVL 5

Expert Comment

by:indrajitmahajan
ID: 38850075
NO.. It wont be possible in Table..
0
 
LVL 39

Expert Comment

by:als315
ID: 38850128
You can use calculated columns in table:
http://msdn.microsoft.com/en-us/library/office/ff945943(v=office.14).aspx
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:
=[txtDateDue]
and in format you can manually enter:
format
0
 

Author Comment

by:Reyesrj
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.
Thanks
0
 
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:
tblBills
BillID
BillName
BillDueDate

tblPayments:
PaymentID
BillID
PaymentDate
PaymentAmt

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.

JeffCoachman
Access-EEQ28018780-GeneratePayme.mdb
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Reyesrj
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.
Thanks
0
 
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.

JeffCoachman
0
 

Author Comment

by:Reyesrj
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?

Thanks
rich
vlookup.xlsx
0
 
LVL 74

Accepted Solution

by:
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...
:-O
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)
;-)

JeffCoachman
Access-EEQ28018780-GeneratePayme.mdb
0
 

Author Closing Comment

by:Reyesrj
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.
rich
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now