Access 2000 databases

Im a beginner learning vb 6 on my own, Im trying to write a program to figure electric bills. My problem is that one person may have several  "map numbers"  seperate bills, and  I'm needing a way to associate a "map number" ,which  contains hyphens, with a persons information , ie: name, address, so on.  Then from a list box select the "map number" and display last recorded reading, for the specified number.  My main problem is if  I need to programmatically add a seperate table for each number, with fields , or if one common table would be enough to handle several different "map numbers" with accompaning fields, ie: amnt of bill, reading, etc.
   The bill is figured by using the present meter reading of the current month subtracted from the present reading from the past month. So the records will need to be cross referenced. ( current month, past month)  

If anyone could help I would greatly appreciate it , Ive been racking my brain on this for some time now
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Presumably you have a customer table in your Access database, then all you need is a "CustomerMapping" table to hold your customer ID and their associated map numbers, i.e. with one row for each map number.

Then selecting your customer will allow you to restrict your list of map numbers down to just the ones that apply for that customer. Then selecting tha map number will populate the necessary fields.

Just a more general note, given the nature of elcric meters, they rarely start at zero for a customer and therefore a customers first bill could be unintentionally large, and also meters are replaed from time to time meaning that the meters will suddenly go back to zero.

Hope this helps
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You can do either way:
* have 1 table for all the map numbers
* have 1 table for each map

in the first case, you need the map number info in the table (as column), in the second one you need the tablename indicate the map number.
However, if you want to lookup all the map numbers ie bills for 1 customer/person, you would have to loop all the map tables to look into it, that's why the second option normally is not chosen. However, way 1 has the disadvantage that the table grows all the time, and you need some way of purging it from time to time using history tables.

in the case you use 1 table (still recommended), you will have 2 solutions: cross reference the table to generate the bill, or when saving a new map number, get the previous one and save the previous reading together with the current reading value. That means that you denormalize your design, but the solution will be able to work much faster...

personally I would use 2 tables, one containg the persons details and one containing the map numbers, you can then relate each table, e.g if you use an autonumber field in the person table, then all you need to do to relate any number of map numbers to any client, is make sure the client number appears in the map table.

With this in mind we can create a recordset to display the required details, the following example uses a programmed recordset and simple statement  query to open records, based on a selection from a listbox, to give you an idea, the middle portion of the code is optional, but gives an idea of further uses to the recordset.

Dim db As Connection
Dim ADOdetailsRS As Recordset
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Your acccess database path.mdb;"
Set ADOdetailsRS = New Recordset

ADOdetailsRS.Open "select * from tblname where  [field] = ' & val.[list1] order by mapnumber DESC", db, adOpenStatic, adLockOptimistic

' this then opens the access table [tblname] and requests all details where [field] is the same as what is selected in list1

'Middle Bit.....You can place code for events or further criterion, such as IF...THEN staements

Set ADOdetailsRS = Nothing


Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
PAQed - no points refunded (of 125)

EE Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.