Too many records for Access?

[Dave177]

I am considering designing a database that will hold information about mobile phone calls made by a team of workers. Every month it is expected that there will be 3,000 new calls made.

I have read that the limits of a table are 2 gigabytes minus the space needed for the system objects, which is apparently, roughly 100,000 records. Therefore, it would not be long (3 years) before this maximum was reached. Although records over a year old could be archived once into another table, would this project be considered unsuitable for Access? If so, what database programme would you recommend using?

Thanks for any advice

 

[PHV]

space needed for the system objects, which is apparently, roughly 100,000 records
???

What is the database schema you want to implement ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

3,000 new calls made." So 3,000 new people making those calls, 3000 new and different numbers, 3000 new cell phone numbers, etc.

Surely you have data that can be broken down through NORMALIZATION. Have you structured any tables yet? If so, normalize them and then post them.

Remeber Access is not Excel.

 

[Dave177]

I was thinking:

tblUsers:
UserID (PK - autonumber)
First Name
Surname

tblUserNames: (Usernames supplied are related to the location of the user. A user might have two usernames, most likely one some time after the other due to a change in location)
UserID
UserName (joint unique index)

tblMobiles:
MobileNumber (unique tel number)

tblUserNameMobiles: This table would link UserNames to MobileNumbers. However, I am not sure it is necessary (see below).
UserName
MobileNumber

tblCharges: (all of these fields, except for the PK, will be imported from a spreadsheet on a month by month basis)
ChargeID (PK - autonumber)
DeptCostCentreName
UserName
MobileNumber
CallDuration
CallDate (dd/mm/yyyy)
CallTime
NumberDialled
Band
PlaceCalled

tblUsersNumbersInUse: (a list of numbers used/dialled by each user)
NumberUsedID (PK autonumber)
UserID
TelNumber
BusinessOrPersonal
Description

The monthly spreadsheet will contain the data for call charges, which will always list DeptCostCentreName, UserName &
MobileNumber and I am therefore unsure if tblUserNameMobiles is required.

The idea would be to be able to breakdown the monthly information, send bills to Users (using mailmerge in word and excel) listing their calls, with usinessOrPersonal calls highlighted, and then ask them to pay their Personal calls.

Any payments could then be reconciled with Usernames.

tblPayments:
PaymentID (PK)
Username
Amount
DatePaid
Cheque Number


Excel is currently being used to hold this information but I believe that Access might be a better option.

Any advice would be appreciated.

David

 

[Dave177]

p.s.
It is tblCharges that will increase by about 3000 records every month. It is an itemised phone bill in excel that will be imported into tblCharges. Rougly 3000 individual calls are made every month by a number of different Users.

David

 

[KenReay]

If the information can be stored in Excel, then in volume terms it can be stored in Access.

Even if after (say) three years you reached the 2 GB limit, you could archive data to a seperate mdb file and use linked tables to access it.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Dave177]

Thanks Ken.

 

[Ed2020]

The number of records isn't really the issue anyway. It's data volumes (and therefore a function of both number of records and table structures) that you need to consider.

Based on the details you have posted I don't believe it will be a problem. I have a number of databases that are *much* larger than this (some containing tables of nearly 3 million records, albeit with only half a dozen fields) that function perfectly well. They receive over 18000 new records in the largest table every month.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Dave177]

Thanks, Ed - that's very encouraging!

 

[SeeThru]

I've got a call log database with (count(*)....) 3.4 million records, each record with 19 fields in various types.



SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk