Forms :: Returning Values From Tables Based On Criteria
Jun 27, 2013
I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.
Table 1
EmpID Name
1 EMP1
2 EMP2
Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B
Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18
I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:
Example 1:
Dim ActualManHours as Long ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400 txtActualManHours.Value = ActualManHours
Example 2:
Dim ActualManHours as Long ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")
Example 3:
Dim StartTime as Double Dim EndTime as Double Dim ActualManHours as Long StartTime = CDbl(txtStartTime.Value) EndTime = CDbl(txtEndTime.Value) ActualManHours = EndTime - StartTime txtActualManHours.Value = ActualManHours
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
If I want to sum the percentages from April, May and June only if a column is Not Null, how would I do that?
example
Tbl 1 PK, Month Percent
Tbl 2 FK, Month Enrolled Qty of Rx in the 1st month enrolled Qty of Rx in 2nd qtr Base (if Qty of Rx in 2nd Qtr is null then Qty of Rx in 1st month enrolled)
If Qty of Rx in 2nd QTR is NOT NULL then QTY of Rx in 2nd Qtr * Sum of April Percent+May Percent+June Percent, otherwise Qty of Rx in 1st month enrolled * Month Percent
Im getting stuck on how to sum the percents of April, May and June and then multiplying the result times the Qty ONLY IF the field is not null.
I only know how to create Query's using the design mode. I dont know how to write SQL statements.
I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.
I have two tables say Daily and Master, and now I want to update the Daily table and add two additional fields in this table based on the data from the Master table.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Ok. Here's the situation. I have query setup. This query has 2 columns: 1. Agent Name 2. Sale Value
I have 2 tables. Table1: Includes Sales for this pay period only Table2: Includes all Sales From past to present.
We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need: I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above. Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex). So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report. Probably going about this wrong.....but any help would be appreciated.
The Table1 holds data that is a request for a task.
ID, Request, Task_tms (the number of times the task is required to be done)
1, Make a cup of coffee,15
Table2 is my allocation of people to the tasks.
ID, Person, Task_done (the number of times this person has done the task)
1,Sally,10 1,Eddie,5
What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.
Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)
The two table are in datasheet format linked on a form.
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product. Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.
Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?
Hi anyone! I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.
I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.
Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].
I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?
What I have is:
IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)
Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?
I have a problem with opening forms.I have situation with 8 forms (small ones!) which must be open based on values in ComboBox1 and ComboBox2. Can I do it with Select case (DoubleClick event), and how?
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
I've good a very very strange problem... I do an insert of a new row into the table TRA. That insert creates an id for the new row. Right after that I do a select of the new row, trying to get the new id created. Well, the insert is working fine, but the select sometimes gives me values, sometimes not. How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...
Any idea??
My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)
sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); " DoCmd.RunSQL (sql) db.Close Set db = OpenDatabase(database) sql = "" Set rcset = Nothing sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "
Hi. Can someone help me? I am trying to make a query return all values (please see attached jpg) It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?) In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
I have a form with a regular save record command button, however I also want to add in an action to open a second form based on information stored within the form.
So users may be directed to Form.alpha or Form.beta depending on their data.
I have a form in which I want to capture today's date when another field on that form is changed from active to Closed. In excel I would write an IF statement but can't get my head round what I would do in Access to achieve the same result. basically it's if (field name 'status')=(criteria 'closed') then populate field (date closed with 'today's date').
