Can MS access handle this many entries

[leftpeg]

Hi,

I am very new to Access but i seem to be getting to grips with it.

My business has just taken on a large contract for the UK main electric provider and we have 6,000 addresses to visit to carry out electric meter checks.

I am about to recieve a data spread sheet with the 6,000 address, will i need any special PC requirements for it to be able to handle all 6,000 entries in the database?

I also will be posting the required data i need in my database, and will be looking for any advice on tables/reports etc that i should make.

thanks,

Mark

 

[Beady]

Access should be able to hold 2 billion records. Unless your computer is very, very old then it should be OK

 

[maxmangion]

you shouldn't have any problems with just 6000 records. However, make sure how you are going to create your tables (normalization).

For example consider using multiple fields to store the addresses rather than just 1 field.

 

[leftpeg]

thanks for quick responses guys.

yeah i had thought about having multiple fields for the address i.e. house number, road etc.

below is the information i need to create tables for. to give you more info, i have five engineers who will be visiting properties, sometimes prebooked and sometimes not, to perform safety checks on meters. i need access to be able to monitor which houses are to be done, have been done, when, by who, etc.

Customer Name
Telephone No
Address
Job booked Y/N
Date of booking (date)
Complete Y/N
Date of completion (date)
Completed by (engineer name)
Additional notes

From this data i would want to print reports that tell me:

jobs to be done
jobs booked for a particular date/week
jobs completed in a particular day/week

i also want to be able to access what each engineer has done, as they are paid on price. i.e. i need a report where i can select the engineer and a week, and then it will report on the total of houses/jobs he has done in that week etc.

any advice on normalisation and table structure would be greatly appreciated.

thanks,

mark

 

[Swillsy]

I would use a combo box for the engineers name and then have a subform showing work done.

tblcustomers
Customer Name
Telephone No
Address

tbljobs
Job booked Y/N
Date of booking (date)
Complete Y/N
Date of completion (date)
Completed by (engineer name)
Additional notes

should be alrite

 

[leftpeg]

with job number in both tables?

 

[Swillsy]

sorry yeah you'll need to link them

tblcustomers
customerID
Customer Name
Telephone No
Address

tbljobs
JobID
Job booked Y/N
Date of booking (date)
Complete Y/N
Date of completion (date)
Completed by (engineer name)
Additional notes

with a one to many relationship on customerid - jobid

 

[leftpeg]

i have just ofund out there is more information needed for the database.

for each job i have to input meter serial number and about 10 other yes or no questions for each job eg. all seals in place? Y/N bare copper? Y/N

So should i now have 3 tables

tbl1 customer info with job number
tbl 2 job status with job number
tbl 3 job information with job number

any advice will be greatly appreciated. i have a few days to get this all set up, so speed is of the essence here

 

[maxmangion]

The first thing you should do is to right down on a piece of paper all the requirements that you need for your database and see which pieces of data should your relationships be based on.

You should also ask yourself some questions such as "can a job be assigned to multiple engineers" and "can an engineer have multiples jobs assigned to him".

If you answer both questions yes then you need to have a many-to-many relationship database, thus creating the need for yet another table (junction table).

You should also consider if you are going to store any details about engineers because if that is the case, make sure that you don't put them in the tblJobs but having a separate table for engineers.

This should get you started at least ...

 

[leftpeg]

perfect, thanks.

best get my text book out and learn about junction tables then. but most i think, if not all, will be one to many.

already made a table for engineer details.

 

[maxmangion]

if you search this forum there are a lot of thread covering many-to-many relationships. You should also find a sample database in the sample databases section.

Goodluck and post back if you have any difficulties.

 

[leftpeg]

okay here is all the information i will need, now i have looked into it.

JobID
House Number
Address
Post Code
Town
Complete Y/N
Date completed (date)
By who (combo drop down list of engineers)
Meter Serial Number
Meter reading
Are the tails loose? Y/N
Isolator fitted in correct orientation? Y/N
All seals in place? Y/N
Cross polarity? Y/N
Bare copper? Y/N
Inner sheath visible? Y/N
Untidy tails? Y/N
Tails marked correctly? Y/N
Unused cable entried blanked off?
Other info

EngineerID
Engineers first name
Second Name

Am i right in thinking that i only need two tables, basically setup like the ones above? or should i have a seperate job address table? i dont think i should as the customer/address will only ever be used once.

Also, is it possible to set up a form where i can select the post code and then fill it out once engineers have completed the jobs, by ticking boxes and using drop downs etc, instead of directly inputting the info into the table?

thanks,

Mark

 

[maxmangion]

As regards all those yes/no fields are these standard for all jobs? I think you would be better creating a table for example tblTasks and you will populate it as necessary

eg.

job1 requires All seals in place
job1 requires Cross polarity
job2 requires Bare copper
job2 requires Inner sheath visible
etc.

because in your scenario if you think of more questions to ask, you are going to end up with an endless number of columns. This will not only goes against normalization, but you are also going to be redesigning your structure all the time.

 

[leftpeg]

that info will be standard for every single job, and each has to be answered yes or no by the engineer.

i am pretty new to access so you will have to bear with me. i am playing around with the database to try and make it work.

another problem i have is that the customer supplied me with the excel spreadsheet, that has the address etc on it with the Y/N questions. i have imported it to Access but it is not in the format i would like i.e. order of columns etc. that would be no prbem but i have to weekly email updated excel spreadsheet, can access export the info back into the same format that they want? i.e. rearrange columns?

mark

 

[Swillsy]

yes, however I'm a bit better at excel so in that case I would export the info into a 2nd excel sheet to format the data either manually or you can create a macro and then move it into their spreadsheet

 

[leftpeg]

perfect, cant believe i didnt think of that

 

[Pat Hartman]

leftpeg, the order of columns in a table is irrelevant since you never work directly with the table so I would leave them as imported. In your application, you always work with queries, reports, and forms and you can rearrange the columns to suit you.

The scheduled y/n and complete y/n fields are not necessary and should be removed from the table. The associated dates serve the same purpose. If there is a date in the scheduled date field, then the job is scheduled and the same for the complete date.

Rename your columns to remove the spaces and slashes and other punctuation characters. Names should be formatted in CamelCase for readibility such as IsolatorCorrectlyOriented. Make the names as short as possible so that they stay below 30 characters if at all possible. In the case of y/n fields try to phrase them so that yes is the desired answer for consistancy. Do NOT use the caption field at the table level to rename the fields. Change the labels on the forms or reports if you want longer, people friendly questions that end with a question mark.

 

[twoplustwo]

Which company is the check for? I only ask as I work at British Energy