Hello all. I only started this design this morning, I am out of touch with Access so please bear with me. I should be able to pick most things up as I did use Access for my A level IT years ago but have not used it since. I am looking for some critique on my proposed method and also some help as I have no idea how to implement some features.
I am a self employed IT technician. I consult home/corporate clients on how IT hardware can help them become more efficient, I repair, upgrade and build PCs, setup networks, reduce cost of utility bills etc.
Aim:
Initial Design:
Table1: Client data
User type (drop down box to select home user or corporate)
Company/Client name
Address Line 1
Address Line 2
Town/City
County
Postcode
Country
No. of Employees
Notes (This field will contain misc. information about the client)
Table2: Contact data
Company/Client Name
Contact Name
Position
Contact Email
Telephone Number
Fax
Table3: Job data
Company/Client Name
Date of Job
Status (Completed/Ongoing)
Job Details
I plan to use the Company/Client name field to link all three tables together IIRC this is the Primary Key.
How would be best to display client details? What I would like is to be able to open a record and display all fields in Table1 and Table2. I would like Contact Name and Position to each have a list in a drop down box containing all records from Table2 for that Company/Client. If I select 'Mr. J Bloggs' from the Contact list I will have his relevant position and contact details displayed, If I select 'Secretary' from the Position list I will have the name and relevant contact details displayed. Can a report offer such an interactive function? I cannot remember if macros could help
At the bottom of the above record I could have a button linked to a macro which is labelled 'Jobs'. When clicked it could show me all past and future jobs for that client and I could filter them down if necessary and search for keywords.
This post is getting a little long so if anyone could express their views on the above and help me to create the database I would be most grateful
Thanks in advance!
I am a self employed IT technician. I consult home/corporate clients on how IT hardware can help them become more efficient, I repair, upgrade and build PCs, setup networks, reduce cost of utility bills etc.
Aim:
- To store client information such as address (for on-site work) and contact details.
- To store multiple contacts for each company or home client along with their relevant positions and contact details.
- To store past and future job information for each client for reference.
- Perhaps to be able to update job records with additional date stamps e.g. if a single job is carried out over more than one date the details can be linked or consolidated into a single record with details of what work was carried out on what date. This could be done in the Job Notes section but I am unsure if a separate field would be useful for each date work is carried out.
- To link or attach past job records to invoices (in PDF format).
- To be able to mail merge all clients, just home clients, just corporate clients or corporate clients of a specific size (by number of employees).
- To be able to lookup clients by use of any field.
- Ability to search for keywords in details fields such as Job Details and filter by company/client name.
- To be able to open a record and view client details. If multiple contacts stored for a client to have a drop down list, when any contact name is selected the appropriate position (e.g. Owner, secretary etc.), contact email and phone number be displayed. The ability to select details by position (e.g. if I needed to contact the head of a particular department and I was unsure of their name).
Initial Design:
Table1: Client data
User type (drop down box to select home user or corporate)
Company/Client name
Address Line 1
Address Line 2
Town/City
County
Postcode
Country
No. of Employees
Notes (This field will contain misc. information about the client)
Table2: Contact data
Company/Client Name
Contact Name
Position
Contact Email
Telephone Number
Fax
Table3: Job data
Company/Client Name
Date of Job
Status (Completed/Ongoing)
Job Details
I plan to use the Company/Client name field to link all three tables together IIRC this is the Primary Key.
How would be best to display client details? What I would like is to be able to open a record and display all fields in Table1 and Table2. I would like Contact Name and Position to each have a list in a drop down box containing all records from Table2 for that Company/Client. If I select 'Mr. J Bloggs' from the Contact list I will have his relevant position and contact details displayed, If I select 'Secretary' from the Position list I will have the name and relevant contact details displayed. Can a report offer such an interactive function? I cannot remember if macros could help
At the bottom of the above record I could have a button linked to a macro which is labelled 'Jobs'. When clicked it could show me all past and future jobs for that client and I could filter them down if necessary and search for keywords.
This post is getting a little long so if anyone could express their views on the above and help me to create the database I would be most grateful
Thanks in advance!