Queries :: Getting Most Current Date For Each Client
Aug 3, 2013
I have two tables:
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.
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.
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?
All using 2010. I have a query that the date is based on the Saturday after that current day and is entered in manually. Is it possible to automate this with code that says whatever the current date is; to adjust to the next Saturday date? I know its a long shot but I just wanted to see if I can eliminate user input.
I have a problem with my query to calculate a most current date. I have try everything to explain all current data at each TubeNumber, but there are no calculated answers at TubeNumber 7, IC2, IC3 and IC4. Why not these TubeNumbers? See below my formula to find a current date:
Code: FindACurrentDate: IIf([Time]=DMax("Time";"qlkp_FindACurrentDate";"[Date]=#" & DMax("Date";"qlkp_FindACurrentDate";"[TubeNumber]='" & [TubeNumber] & "'") & "# And [TubeNumber]='" & [TubeNumber] & "'");"This is most current date!")
I have send my database as an attachment with my question.
I have this table that records sales events for properties, with multiple sales records for some parcels. Each parcel has unique field: MapTaxlot. I want to create a select query or make table query that shows only the most recent sale event for each parcel. Instrument_Date is the date field for the sales records.
Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?
How do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
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
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.
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 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?
I have a report that displays simple date fields. One of the fields is "lease execution". On the report, I want "lease execution" to display differently depending on the date the report was run.
So:
- If the "lease execution" date is more than 120 days away from today, I want it to display as Q YYYY. - If the "lease execution" date is between 120 and 90 days away from today, I want it to display as MM/YYYY - If the "lease execution" date is 90 days or less away, I want it to display the normal date MM/DD/YYYY
I was thinking I would need to do DateDiff() to figure out an amount of days that's between Now() and [Lease_Execution]. Then based on that amount make the report show it differently. Pseudocode would be: if DateDiff() = 40, then display [lease_execution] as MM/DD/YYYY
I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key) Employee ID (Foreign key to link to the employee table) Start_Date End_Date Status (Either valid or expired) Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
i have just started to use access and i know how to insert the current date into a field using date() but i am not sure will this change the date everytime i open the form ? i want to create a form for invoices that shows the date the invoice was created and doesnt change if i re open for editing,
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.
