Question New Access Database

[ps2328h]

Hi There,

I have been asked to design and develope a nww access database to enter all the nw projects the company receives so we can report on it and tract the costs and fees. The information they require is client information with their address details and site details and also the county the site is under, etc..

Can anyone please help or guide me in how i can achieve this??
Currently i have just setup an excel spreadsheet which has the following information i log on an excel spreadsheet:

Date, Project No, Project Ref, Project status, Surveyor Name, Client Name, Address, Contact H, Contact M, Email, Recommended Contact, Site Address, Project Description, Fees, Site Engineer fee, other cost, Final fees, BC fee, Site Eng Name, 1st Payment, 1st Payment date, 2nd Payment, 2nd Payment date, 3rd Payment, 3rd Payment date, comments, Thames Water,

I think i'lll need the follwoing tables but I'm not to sure now and wondered if anyone would be kind to assist me in setting this up so we can tract this information and are able to report on the projects we gave completed in progress, and cost occured.

Tables:
Clients: Id, name, address, contact, email, authorised county, site (if different to client address)
Projects:id, ref, date, name, project status
Engineers: if, name, address, county
Cost: Eng Cost, Final Cost, VAT, Project Fees

Much appreciate any help, thanking you in advance.

 

[avcastner]

I would split the addresses and contact names into the actual address and name parts. Your database will morph eventually, and the more the data is split into it's own fields the better. You can always concatenate in reports and such, if necessary.

You could create a drop-down for the county that has the list of all counties, so there would not be misspellings. If have a list of towns/cities/villages you work in, you could create a separate table of towns/cities/villages and counties that you could link to, provided there are no duplicate towns/cities/villages.

The project should have it's own address fields, as they will usually be different from the postal addresses of your contacts.

Fees seem very generic. Will you need to log each fee and then total them? You may need to create another table that is just for fees: FeeID (primary key), ProjectID, Description, DateofFee, AmountofFee, Type of fee (VAT or other types you incur). You can then total in your reports, queries, and forms. You may want to be using subqueries and subreports/subforms for your totals.

You can have your final cost be totaled by Access, but it may take some fiddling to get it to work.

 

[ps2328h]

Hi,

Thank you for the promp response and suggestion. I'm not sure i understand what you mean by splitting the clinet and address? As client would always have to have a address and the site could be client address or another address?

Much appreaciate your help and guidance on how to set the tables to start me off.

Thanks