Redesign Access form and related tables to be efficient with SQL Server 2008
$100-460 USD
Completed
Posted over 12 years ago
$100-460 USD
Paid on delivery
Hi, I'm after someone to redesign an access form, create a new search form and move some access tables to SQL Server 2008.
The form will need to be redesigned using stored procedures to make it more efficient.
This is one module of a much larger system so we are looking at some one who can do this component first then move on to an hourly rate with the other modules.
When bidding could you please let us know your standard hourly rate as we need to take this into consideration for ongoing work with this project.
Thanks
## Deliverables
I'm after someone to make changes to convert a module of our access database tables to sql server and make a form optimised for SQL Server 2008.
You will need to convert the tables in the attached database to sql Server 2008 (the tables that start with tblFE can stay in the access db)
**Table Changes**
**Contact**
Rename table to tblContact
Add new field fldFranchiseCode - text 5 -Already done
Rename ContactID to fldContactID - we will need to account for existing franchises data, as there will be records which have the same id for different contacts.
Rename Input Date to fldInputDate
Rename Type to Type Ref
Rename Name to fldName
Rename Company to fldCompany
Rename ABN to fldABN
Rename Address1 to fldAddress1
Rename Address2 to fldAddress2
Rename Suburb to fldSuburb
Rename State to fldState
Rename Suburb to fldSuburb
Rename Postcode to fldPostcode
Rename Telephone to fldTelephone
Rename Telephone 2 to fldTelephone2
Rename TelephoneBusiness to fldTelephoneBusiness
Rename fax to fldFax
Rename Sort By to fldSortBy
Rename Comments to fldComments
**Employee**
Rename table to tblEmployee
Rename any field that doesn't start with "fld" to start with it.
**Service**
Rename table to tblService
Rename Service Code to fldServiceCode
Rename Service to fldService
**frmClient:**
Needs to become an unbound form (using a stored procedure to retrieve the data).
At the moment access returns all records to the form so you can scroll through different clients, I would like these to be removed and only one record returned at a time to improve performance.
The search/find and select franchise comboboxes at the top will be replaced with a "Search" popup form (Explained later).
The navigation buttons at the bottom of the form can be removed. I would like this form to be based on a stored procedure to return only one record at a time (to increase performance). It needs to show the contact history (this tab is not editable), notes and customer relations need to remain editable.
(Customer relations uses dao to populate the lists so its functionality will need to be rewritten work in sql server)
I'm not sure how you populate the contact history subform (maybe a stored procedure to return only the records for the client with a pass through query)
The show amount owing button will need to use a stored procedure to calculate the amount owing for the client as it is currently slow.
The "update all inquiry details" and "update inquiry customer details only" can be ignored as I will complete these when I tie it into the rest of the database
The Contact history report and view duplicates will also need to remain. The contact history report will need to be filtered by franchise. If admin user they will have the ability to filter by franchise, (need a new drop down box for each franchise, with all at the top, if normal franchise user it will be hidden).
The view duplicates form will show all duplicates only for contacts assigned to their franchise.
The 'add new client' button needs to function
This form is filtered by a franchise code, when the user logs into the database they will be either a normal or admin user. An admin will have full access to the database, where as a normal user will be restricted to what franchise they are assigned to (they will only be able to view records for their own franchise), you don't need to worry about the login etc what I have done is put 2 module variables for "Admin"/"Normal" (strNormalAdmin) and franchise code "Mel"/"ADEL" (strFranchiseCode) you just need to make sure normal users can only Search for records based on strFranchiseCode when they are normal users and show all when they are admin.
**New Search Popup Form (frmSearch):**
The popup will have some plain textboxes at the top to allow users to search by client, phone and company contact. There will be a combobox at the top of the top the popup where a user can select "Starts With", "Like", "Exact Match". What ever is selected here will determine the type of search for records conducted in the three textboxes.
It will need to be filtered by the franchise code, but allow admin users to view all contacts (have a filter by Franchise combobox available to admin users on this form)
We also need a way of showing any customer without an assigned franchise code, that way we can have customers who can be used by all franchises, they wont be editable by normal users though (only admin users will be able to edit them in frmClient)
There is a field fldDuplicateClientName in the client table I need the view Non Duplicates/All option group to work off this (will be non duplicates by default)
The user will select their criteria to search for then click on the Filter button this will show all records that meet the criteria (via a stored procedure), when a user double clicks on the record they want, the form will close and the record will be displayed in frmClient.
I have attached an image of what I would like it to look like: