Queries :: Insurance Database - Get Only One Record Per Client
Apr 4, 2013
I am working on an Insurance DB.. I have two tables tbl_Clients and tbl_PolicyDetails with a One-Many relationship.. tbl_Clients have the Clients basic info, Name, Tel, Address etc. The tbl_PolicyDetails have the details of the policy for the customer like Start date, policy term, amount they pay, policy status etc. Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..
One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client. My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
Code:
SELECT tbl_Clients.cli_ID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover
FROM tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.cli_ID = tbl_PolicyDetails.cliID_FK
WHERE tbl_PolicyDetails.policyStatus = "Active" Or tbl_PolicyDetails.policyStatus = "Suspended" Or tbl_PolicyDetails.policyStatus = "Lapsed" Or tbl_PolicyDetails.policyStatus = "Cancelled";
Code:
cli_ID firstName address
1 Paul Bournemouth
2 Mark Bristol
3 Lauren London
4 Caryn Devon
[code]...
When a Client wishes to Cancel a policy it is not Terminated, the policy status is just Cancelled.. Similar to Lapsed policy, if the policy hits 30 days of no pay then it is passed on to a Debt collection agency.. If in future the Client comes back a new policy is generated for both cases.. So in this case Client Lauren and Caryn will have been repeated twice, in the report I am trying to generate (based on the above query).. I do not wish to display the Cancelled policy if the customer has another policy which again can be Active, Suspended, Lapsed, Cancelled..
Code:
cli_ID firstName address policyCover policyStatus
1 Paul Bournemouth PDB Active
2 Mark Bristol PDB Active
3 Lauren London PDBCH Suspended
4 Caryn Devon PDE Active
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details 2/ "Department" storing the departments of the unis we work for 3/ "Client" Name of the client(s) in that department.
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
I am creating an employee database for a client. The employee table has a performance column for each employee, and my client wants to be able to choose from four performance codes - Excellent, Good, Average, and Poor. They want to choose these descriptions instead of typing them in each time. How can I make this happen?
I have developed .net application ....at back end I am using ms access database. When I deploy my application at client end , I want only my ms access database should accessible to application only. Client should not be even to see which type of database i am using or its business logic .
The Setup: There is a clients table, a property table and a jobs table. Each client can have multiple properties and there can be multiple jobs done on each property.
The form: I created a form that uses a drop down to select the client, once the client is selected the subform shows the properties for that client. I can navigate through the properties to select any of the required properties and on this same subform, there is a button to open the jobs related to the property selected.
The problem: The form and subform work fine. The last form (the button that opens up the jobs) works ONLY IF there are records available. If there are no jobs for that property, it doesn't allow me to enter a new record. I'm pretty sure that the issue is because the jobs form has a query record source that uses all three tables linked (I'm pulling the clients name from the clients table and the property name from property table).
What I need is some vba coding to check if the recordset is empty and if it is then add this entry. Also, I'm not sure if this should add this code to the button or on the on_load of the jobs form.
1. Client Info table which contains the following fields: Clientid LastName FirstName
2.DatesDistribution table Clientid DateAttended
The relationship is one to many for these tables.I have been trying to create a query that will show the following:
Clientid LastName FirstName DateAttended (where this field only shows the most current date that the client visited)
Example: ClientInfo has 2 records:
Clientid=jsmith LastName=Smith FirstName=Jim
Clientid=tjones LastName=Jones FirstName=Tom
[code]...
I have tried grouping the records in Client Info table to get distinct names and using a Last function to get the most current date with little success.
I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid
Here is the sql DELETE tblinclude.ClientID FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID) WHERE (((tblinclude.ClientID)=1));
I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start 1st Obs End 2nd Obs Start 2nd Obs End 3rd Obs Start 3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
i have been trying to create an employee database to record:
1. Name , Address 2. Telephone 3. Date of birth 4. employee ID# 5. Emergency contact -name, address, telephone 6. Photograph 7. Other pertinent information related to employees
So far I have incorporated the contacts database forms into my new database but I am unable to link the forms into one form that will incorporate all the information needed. Is there an existing employee database that I may edit to suit my purposes?
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef" Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
I have split my database, the data is in a DB on the server and the forms, reports, etc is on the client desktop. My question is "Is there an advantage to having all of my combo box queries (Lookups) on the server side (defined in the table as a combo lookup) or should I put the all on the form so that they reside in client side DB.
Hi, we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to "Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off? what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
Hello, I am new to this forum and hope someone will be able to help me.
I understand that when querying using a split MS Access database it retrieves all the data to the client machine and then applies the criteria (hope this is correct) at the client side.
If I were to link to Oracle XE tables via ODBC, does a query using these linked tables still bring across all the records to the client or will it run the query on the server and just bring down the records matching that criteria?
I'm making a pop-up form for a certain of clients who require advocacy. I'm including a screenshot.
The thing is I don't necessarily want the form to go to a particular record, but rather be null until lastname/firstname field is filled in. I've written code to look up if there's already a client on the file and thus use that file, but am stuck when if there's none, and it's a new client, how do I make it so a new file is created?
Currently, the lastname and firstname's record source is a query looking up the corresponding fields in the related table.
Also, should I do something special for form's load property to ensure that no new record are created until lastname/firstname has been filled in?
Private Sub LastName_AfterUpdate()
If IsNull(DLookup("advocacyid", "advocacy", [me.lastname=advocacy.advocacyid])) Then DoCmd.GoToRecord , , acNewRec Exit Sub Else Let Me.AdvocacyID = DLookup("advocacyid", "advocacy", [me.lastname=advocacy.lastname]) Let Me.FirstName = DLookup("firstname", "advocacy", [me.advocacyid=advocacy.advocacyid]) Let Me.LastUpdate.DefaultValue = DLookup("lastupdate", "advocacy", [me.advocacyid=advocacy.advocacyid]) End If
I have a feeling that my problem is easy, but I have been in a panic over this project, that I cant think too straight anymore (I think I need a vacation!)
I have a simple form for tracking employee activity, such as rehire, death, orig hire, exc. This works find, except they now want to be able to make changes to staff details like new job title and such. On the same form. They don't want to change forms.
So I added a subform that connects to the Staff Detail table, using the Clock# that exists in both tables.
When the form is open, user must choose employee from drop down, once choice is made the clockno on the main form fills in, however, the subform clockno which it connected to the main doesnot update- unless, is you go into design view and then back to form view, the subform fills in with the right info, you can even use it in quierys -- However, if you change the employee in the main form, its clockno will change, but not the subform, you have to go the design view again. I have no clue. HELP
Consultants need to be able to log-in when they are available and be put in rotation. Agents need to be able to select the next available Consultant. I also need there to be a waiting list. I would like to be able to run reporting on Time on Waiting List, Time with Consultant, etc.
Is there a simple way to do this? I would appreciate if someone could point me in the right direction.
I want to create tables with clients and another table to populate multiple orders for each client. How these tables will look like and how to create one to multiple relationship.
I need any code or way that whenever any field of a record according to unique ID changed the code must save the changed field name and the current date in a specific field in another table (first field store the ID and the second one detail about changes) with add record mechanism. Suppose I have a table about the information of students with the name std_info and another info_report and when any changes make to the any field of std_info the field number and the unique ID to the table info_report. I want to use this system to record which user make changes to which records.
Hi guys. I am trying to help a client regards an un-supported access database. They have a DB that is shared across a network (only have experiance on database on single client, never set up security etc....). The DB is installed on the Server. The DB is access via a mapped network drive. The Database folder contains the following files: MKC Clients_Quesries.mdb MKC Clients.mde MKC Clients_.des
On one machine the MKC Clients.mde is launched and 1086 records are visible. On a second machine the same MKC Clients.mde is opened but displays only 977 records?? An I missing the obvious?
I have created a db and am having serious problems, the size of the complete db is 3mb, and when split its 500k, the trouble I am having is that when putting the db on the network server and you try and open it based from home it takes so long for both the full db and the split.
The main db has one page that all staff need to see and have a quick response when opening on VPN client. The form I want to open has a combo box that lets the employee select the company and all the information updates on the form.
I tried making a page in acces to do the same thing but having problems
Is this possible what I am trying to do or is there an easy faster option.
Right now i using client server application using - Frond End : Ms.Access + Winsock with UDP protocol. ( This program like Yahoo Messenger ) - Back End : Sql Server
I made this program for Remote Trading.
every second server or client will be send data. Sometime Server send data only to specific client, so when client logon to application, I save the IP number and after logout i clear the IP
the problem will be happen when the computer client Hang, server cannt send a data to that computer, and waiting for ever until server hang too after that all client will be hang.
and the question is How to check status computer already ON or Off i mean before server send data, server will be check status computer with that IP, If status computer On then True else False, so if computer client off, server don't need to send data.