I am creating a query based on two tables: 1) tblClient (only one primary key "ClientID") and 2) tblContactDate (with two primary keys "ClientID" and "ContactDate").
Each client may have more than one contact date.
In the query, I only want the records from the tblContactDate showing for the MOST RECENT contact date of the client only; however, right now the query is producing more than one record for those clients with more than one contact date.
Not sure if I need add some special code to the "criteria" area in the Design View, or somewhere else. Your help is appreciated. :confused:
i've designed a rental database (which seems to do what i want) - it's not perfect....
But i can't figure out how to accomplish this.
If i have a tenant, who has previously had a lease agreement on one of my properties, but then moves out, and then moves into one of my new properties, i can't seem to see how to get access to display just the new lease details.
I've attached a blank copy of the DB with some sample data to illustrate this.
you will see that test tenant has two leases, one expired lease, and one active lease, but on the main form it just shows the expired (original lease)....
I have managed to link a report to a form where you can double click on the surname and the report opens automatically BUT I want the report to only show the data from the record that I have double clicked - at teh moment the report is coming up with thousands of pages - one for each record.
Can anyone help me with this - I think it's probably something simple but I can't work it out on my own :eek:
I was creating a program using ms access. consist of 2 table of database called employee and userlevel.. in employee table have ID, name, username, password,userlevel, and others related employee profile.. and in userlevel table, it consist of admin level and normal user level.. Userlevel table allow to differenciate admin and normal user(employee) .. in my program each employee having their own loginID and pass to access their own data.. the problem with my program now is displaying the data that belong to the logged in employee. its mean that it only show the data that owned by currently user that logged in..
Hello everyone, I have a query that runs from the main form when the command button is clicked. It displays the results in datasheet view. I want the user to double click a specific record and jump to that specific record in the main form. How do I do this? Sorry, if this is a dumb question. Please help, my boss is breathing down my neck. :eek: The main form is called Contracts. Each record has a unique ID.
I have a form that when a user fills out the information and select a submit button. It brings up that record in reports in print preview for them to print. The issue i am having when the user enter the information, the report does not show any data until i refresh it. I tried include me.refresh,etc. No luck. I attached the code to make things simpler.
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty Then If MsgBox("Do you want to Submit this Contract Form? Clicking No will DELETE ALL ENTERIES,and Log You Off", vbYesNo, "CONTRACT FORM") = vbNo Then Me.Undo Cancel = True Else If MsgBox("PRINT FORM", vbOKOnly, "CONTRACT PRINT") = vbOK Then DoCmd.OpenReport "rpt_Contracts_Main", acViewReport, , "[CONTRACT_ID]=" & Me!CONTRACT_ID End If End If End If End Sub
The reports comes up, but i have to manually refresh it to show the data that was enter.
I have a table, with a related value in another table. E.g. A Items table with a batch value from another table.
I have a form to enter how many of these items has been used and from which batch number they belong.
The batch number is from a dropdown, and batches can be finished(exhausted) and marked such in the table so they no more show in the dropdown.
All this works fine, until, I go back to a entry which was from a batch that has been finished. The combobox is empty although the (Already finished) batch number is mentioned in the table. This is perfectly normal as my query for the combobox is :
Code:
SELECT ItemBatch.ItemId, ItemBatch.ItemBatchNumber, ItemBatch.Finished, ItemBatch.ItemName FROM ItemBatch WHERE (((ItemBatch.Finished)=False) AND ((ItemBatch.ItemName)=[Forms]![ItemMasterForm]![ItemDataSheet].[Form]![ItemName]));
What I want is to show the current batch number as well. I tried to make this query get the current value, but wasn't successful. I tried to make a calculated field based on the dropdown and show its value.
Is there any way I can show the batch number in the datasheet? I have to use a datasheet and not a form, because there will be many sub records for the main form, and having a form will be very uneasy.
I have created a databse with three tables that are linked (customers, quotes and invoices). I have my main form (customers) with two sub forms (quotes and invoices).
Is it possible to create a query to show the data just from the current form that i am viewing?
Just wondering if anyone could help me with my little problem :)
I am making a register (as in a class register of students) I have a table with the lessons on and one with students on. I have made a relationship between them etc.
I have a form where the parent allows you to choose which lesson you want to view, and the subform displays the students for that lesson. This all works fine.
My problem is that I also want a picture to be displayed for the student that is currently selected, I have managed to update the picture by using
Private Sub Form_Current() [student_picture].Picture = [student_image] End Sub
student_picture is the name of the image on the subform, the student_image is a row in my student database that contains the location of the image e.g. c:abc.jpg
and this works at updating the image that is show. The only problem is that the image is shown on all records/rows. Is there anyway of making it so that the image is only displayed on the current row?
I have a report that shows all visit dates and the rep that did the visit. How do I only show the most current data. I use an append query to add records to a specific table. The report pulls from that table.
I am extremely new to Access. I have my database up and running ok(ish) and would like to know if in my data entry form, I can have the "Date Entered in Database" box display the current date that does not change from day to day. I know you can use the "Now()" function but won't this just change everyday?
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database Option Explicit Private Sub AttachPaymentDetails() Call PerformInsert("tblFinancialBudget", "frmFinancialBudget") End Sub
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
