Display Query Results in MS Access using Mutiple Fields and 1 Text box with DCOUNT()

In Continuation of my recent post : Civil Registry App in Microsoft Access – Finally!

Encountered an issue in access where I need to get the values of Gender and Age.

Display Query Results in MS Access using Mutiple Fields and 1 Text box with DCOUNT().

Now What I need to do is to count the total number of male or female residents based on the age using the query, and I need to display those calculated values in a textbox. After sorting and scouring info in the web.

I decided to use DCOUNT().

I translated the problem with an SQL statement.

Select Count (AGE) and (Gender) From TotalPOP as totalpopbyage Where AGE = 24 and gender = female

using Dcount(name of field, queryname, criteria)

I tried =DCount(“AGE”,”totalpop”,” AGE = 24 AND gender=’female'”)

because of the dcount format I was following, errors kept showing if I added both criterias gender and age but it works fine with just one criteria

until I came up with this by accident.

=DCount(“gender” And “AGE”,”totalpop”,” AGE = 24 AND gender=’female'”)

… sometimes the solution is in front of our noses, it’s how you look at it.

Basically reviewed these articles for the solution.

I’ll be back with more misadventures and my quick fixes.

Enhanced by Zemanta
Continue Reading

Civil Registry App in Microsoft Access – Finally!

Alright so it took  me longer than I expected. Of course I did encountered some bumps that basically made me stop developing it and sorts.

civil registry application
Developed in Microsoft Access 2007, A Civil Registry Application (data entry module)

Developing an app in MS Access may seem simple but without proper planning you may end up like me. Recreating the whole application from scratch.

First hurdle : the naming conventions, I got so mixed up with text1 and label99 that I had to restart the whole app and basically do what I’ve learned in school. It’s good to practice putting prefix for easy identification even on smaller projects, like putting txt for text boxes, lbl for labels, etc. Well, you get the idea.

Second Hurdle : 1 table for the whole project without any lookup tables. It’s clean doesn’t clutter much, easy to remember, but the problem is it takes a long time to load. It’s good at small projects but with a big project with a data entry, record viewer, reports, queries all accessing 1 table. it will soon grow big and will load up so slow. My Database loaded for 25mins total with 15,ooo plus entries.

Third Hurdle : Hubris. I thought that since this is MS Access it should be easy I can make that app in like 2 hours. I was wrong. Really wrong, I had to remake from scratch didn’t even make backups, no standardization, no naming conventions.. a shitload of mess.

Thank you for the Internet, I was able to make research on how to do this and that, like using dcount, dlookup, making queries and all that headache.

Thanks to a couple of sites, that been a good source of info, like Access World Forums, Jegworks.com, oooh and I even tried and tested Opengate Software’s UI Builder for MS Access – you should try this too, It can save you a lot of time instead of focusing on the UI you can immerse yourself in the back end of your application.What I’ve learned. Access is easy to use, but without proper planning, naming conventions, flowcharts, db relationship, minimal VBA knowledge. You’ll easily get lost with the unbound textboxes, wizards, reports that makes no sense. and button that don’t do anything. You won’t go far you’ll only get confused.


Enhanced by Zemanta
Continue Reading

Civil Registry App Project

Day 2

just stared at the data, I work with. Looked at it for a minute and then played Marvel Avengers Alliance the whole day, and clocked in as “research”

Day 3

After the naming convention, creating the tables to used on the project. Queries, reporting has to be made with the data such as :

by :
1. total population
total male
total female

total population ages 18-25
total male ages 18-25
total female ages 18-25

total population below 18
total male below 18
total female below 18

total population ages 26-40
total male ages 26-40
total female ages 26-40

total population ages 41-55
total male ages 41-55
total female ages 41-55

total population ages 56 above
total male ages 56 above
total female ages 56 above

2. house number
Street name

**user has an option to pick query by certain categories

**reporting via total pop (via certain criteria – gender, age)

**reporting via location of demographics


and a very useful source of information : Office Support  for playing with the data and automatically calculating the age when Date of Birth is entered. Since this is a working Civil Registry application, reporting has to also include :

household head > lot owner
household head > related (son/daughter)

no. of house hold (calculated by the household head – lot owner)

So that this can be used for collecting data, especially for voter registration, unemployment and other helpful data.

Continued from the previous post : Playing with Microsoft Access in A Civil Registry Application 

Had to read all about the Civil Registry Process as I clearly have no idea on the subject.

References : Good Reads and useful sources of Information

On WikiPedia – http://en.wikipedia.org/wiki/Civil_registry

Unicef – http://www.unicef.org/pacificislands/index.html

WHO –  http://www.who.int/en/



Enhanced by Zemanta
Continue Reading

Playing with Microsoft Access in A Civil Registry Application approach

I recently got a gig for developing an application in Microsoft Access. I said to myself this is going to be easy. I was wrong, need to think this through with coffee and Katy Perry.

Day 1

A Civil Registry application to be developed in 2 weeks time for beta testing. It would incorporate the Excel spreadsheets that the clients use. As with working with the clients data, I would definitely need to work on naming conventions to standardize the data on the spreadsheets that was given to me.

Microsoft Office Access 2007
Microsoft Office Access 2007 (Photo credit: Wikipedia)

Working with a list of Voter’s Information on an Excel spreadsheet. First issue I encountered : Street names are not standardized, as the data was added by multiple persons and since the Excel spreadsheet would allow almost anyone to enter any data as he/she interprets it on looking on the form that was given to them to input on the system. The result is a large volume of data full of inconsistencies and headaches.

Solution :

Make tables for contact details, voters info and other juicy information.

Contact Detail Form

ContactID – PK for other tables I will add sometime if I get super heroic.
> last name / first name / middle name **added three separate fields, from 1 field – name
> house number/street / barangay – **added three separate fields, from 1 field – address
> tenure – owned / rented / living free (dropdown) **added new field
> sex (dropdown) **renamed to Gender
> birthdate **renamed to Date Of Birth
> Place Of Birth **added new field
> age (calculated by DOB) **will add code
> marital status – single / married / with a live-in partner / separated / widowed / divorced  **added new field (dropdown)
> Citizenship **added new field


Enhanced by Zemanta
Continue Reading

Onward to Sales Dept.


WORK… daily ramblings.

I’ve been a HelpDesk/Tech Support Guy half of my worklife, I recently applied for a 2nd Tier Technical Support position for an ISP, guess what?!, I was trained for SALES and BILLING, 2 of my most hated dept. of working for an ISP. Well, the pay is good I signed a contract for a 2nd Tier Technical Support position, but take note, I have limited scope of support so I mostly do Tier 1 stuff which is great considering the work to pay ratio, it’s a win-win situation

Technical Support Explained

So I said to myself, WTH let’s go sell some packages, earn commissions and be rich LOL… 1st day phone work, I learned the hard way. ANYONE can make the sale, but it takes special kind of person (plus dedication+hardwork) to be a great salesman.




Enhanced by Zemanta
Continue Reading

Starting with Ruby on Rails

English: Ruby on Rails logo
English: Ruby on Rails logo (Photo credit: Wikipedia)

Since I started with Ruby first because it was my first challenge, I went through the RubyInstaller.org way. After digesting the valuable information on LCODETHW‘s Learn Ruby the Hard Way, I’m now ready for the main challenge which learning Ruby on Rails.

Installing on Windows

I went to RailsInstaller downloaded the Kit, (thankfully) it proceeded to install Git, RubyGems, then Rails.

Once its done with its installation, Git would ask for a name and email address as it needs to set your user name and e-mail address. You can change it later on by using on the your terminal :

$ git config –global user.name “Your Name/User Name/Whatever”
$ git config –global user.email “emailaddres@emailsomething.com”

Since the Rails Installer already did the bulk of the installation, try checking if Ruby is installed properly

$ ruby -v

and also checked my RubyGems installation by updating it

$ gem update

and lastly Rails

$ rails -v

Now I’m ready! It’s time for a coffee break and some D3 farming. See ya! Hopefully I can do something productive with this new addiciton I’m stuck with.



Enhanced by Zemanta
Continue Reading

Work, Work (Learning Ruby)

Alright, here I am busy with my Ruby Challenge, I’m enjoying it, with the occasional coffee breaks and D3 inferno farming runs… I’m getting there.

I’ve been checking out some interesting sites and been reading them a lot so my game time is now roughly 2-3 hours from the usual “sleep is for weak” motto after work upon arriving at home, I fire up my computer and log in to D3, I’m more into the learning Ruby on Rails stuff than before.

  1. Ruby on Rails Tutorial : Learn Web Development with Rails
  2. The Best Way to Learn Ruby on Rails
  3. Rails For Zombies

break is over, now get back to work!!

Enhanced by Zemanta
Continue Reading