[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

MS Access Table auto populate recored

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
Reyesrj
Asked:
Reyesrj
1 Solution
 
indrajitmahajanCommented:
NO.. It wont be possible in Table..
0
 
als315Commented:
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
 
ReyesrjAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
<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
 
ReyesrjAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
ReyesrjAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
ReyesrjAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now