Sum Values From Multiple Related Records On A Form
Jan 26, 2013
I am fairly new to Access and have no formal training on this program, just a lot of trial and error. I have a database with 4 tables. They are tblEmployees, tblCourses, tblDates, and tblTraining. The key for each was an autonumber that is EMPLOYEEID, COURSEID, DATEID, and TRAININGID.
tblEmployee lists pertinent information regarding an employee (name, serial #, shift, etc). tblCourse lists all courses that are available for an employee (course name, #hours, required attendees, type of training, etc).
tblDates lists all available class dates and times for the courses in tblCourses and has a lookup field for COURSEID and COURSETITLE from tblCourses.
tblTraining lists all the training scheduled for and completed by an employee and has a lookup/relationship with tblEmployees for EMPLOYEEID and EMPLOYEENAME.
It also has a lookup/relationship field with tblCourses for COURSEID and COURSETITLE. Finally, it has a lookup/relationship with tblDates with lookup field/relationship with DATEID and CLASSDATE. I have successfully created a form where the training can be added to an employee (frmTrainingUpdated) and it has a subform (frmTraining) with all classes for that employee in a multilist at the bottom (from a query of tblTraining). The subform also has a field for whether the training was completed and then the hours are credited to the employee (txtCredit).
The problem comes when I try to add all of the credited hours for a single employee in a separate field. I am trying to create a field somewhere on the form that will total all of the hours for all of the completed classes for the one employee on the form. I have tried to list the data for the textbox as =DSUM([txtCredit], tblTraining, WHERE (EMPLOYEE=Me.Employee)) and only get an error message displayed in the textbox. I tried to do a totals SUM on the query and it only gives me the individual hours for each class on the same line for that record.
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
I have a Datasheet view of a form and it displays a "+" symbol. When expanded it returns a list of related records. How do I get rid of the "+" in my form?
I have a main form with multpile tabs - each tab containing a different subform.
Link Master Field: ClientID (field in Master Form) Link Child Field: Client ID (field in all subforms)
There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......
Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.
When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.
If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I hope the title actually conveys what I'd like to do.
I want to assign records on a subform to a group header on a form. For example, I have groups A, B, C, and D and I want the records on a subform to be assigned to groups A, B, and D. (The number of groups and their names will change so I can't simply use an "A," "B," "C" option box.) My idea is to have a Multi-Select List Box on the main form and choose all the groups to which the records on the subform need to belong. So I'd select the groups, enter the records on the subform, then create a another record on the master form and assign records to another group.
Is it possible to make this happen without a lot of programming?
Hi! I have a tables. One is called: products => prdouctID,productName, ProductPrice
I created a dropdown list. To read in the values of productName.
I wanted to have a textbox / label which will update the productPrice. If i select productName as "Pirates", the textbox/label will show $50.00 If i select productName as "Who let them out?", the textbox/label will show $80.00
I have managed to do the dropdown list but cant seem to figure out the label / textbox.
tblAcq and tblAcqDetail In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity. tblAcq [AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
In the attached Database, I have four tables. The purpose of the Database is to track training for employees. A quick description of each table:
Employees: List of employees requiring training Course List: List of Courses offered Course Schedule: When said courses are offered (one to many relationship with Course List). this has a Composite Primary key consisting of the Course Number and Section Number Course Attendance: This is to track which employees attended which class.
Question 1: In the Course Attendance table, the first field (SOS Course Number) looks to the Course Schedule table. This field uses a lookup to select the course and section number, but only displays the course number. How do I get it to also display both the course and section number (don't care if it is displayed in one or two columns)?
Question 2: Similar problem, except the second field is Employee Last Name which is a lookup from the Employee table. I want to display both last and first name in two separate columns.
Note, I realize there are spaces in table and field names. Please ignore this for now. It will be fixed later.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
how could i get multiple values shown in 1 form only? i currently have a code to look through 6 fields and if the fields iar blank, you get a a message box. you press ok and the next message is shown for the next field if it is blank but it would be so much better to have 1 message box showing all blank fields required to be filled in.
all it does is check the fields as these are required before continuing. if anyof the fields are blank, then it tells you and you cannot continue untl they are all filled. it seems easy in my head!!
im sure this can be done but im not quite sure how.
I am making a database and I am currently doing my forms. I have been using wizards and so far everything I have wanted to be able to do has been achievable, except for where I am now! I am making a 'purchases' form. What I want to be able to do is make multiple purchases in the one form. Let me try and give you an example to further explain myself.
Lets say Mr John Smith wants to purchase 1 black pen, 1 30cm ruler, 2 rubbers and a compass.
I want it so that rather than have to add each one of the purchases one by one, I want to be able to add them all in the same time and be able to go back to my purchases table and see:
Mr John Smith - 1 black pen Mr John Smith - 1 30cm Ruler Mr John Smith - 3 rubbers etc
Hi there. What I'm attempting to do is pass multiple values from a multiple selection list box as criteria for an Append Query. Is there any way to do this? The DB keeps track of Real Estate boards and the forms that they use, I would like to be able to select all the boards in a given state/province, but have the ability to deselect some within that province if I don't need them in the query (this is the criteria i'm trying to pass). I don't need to use a list box, any control that would allow me to pass multiple values would be great. Anyone able to help? Thanks very much.
I have a database and on the main table one of the columns is "Status". The values in that column can be either "Worked, Unworked, or Overdue".
I would like to be able to create a form so that the user can run a query and select which Status' they want to see. For example maybe they only want to see Unworked and Overdue so they can select those and run the query.
I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Function cmdOpenQuery_Click() On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String
I've been using an access d/b for ages, it's one of the template (Orders) databases included with access but modded to suit my own use. copy attached with customer details removed.
Last time I used it it worked fine but yesterday, when I tried entering order information, I get an error saying cant add or change a record because a related record is required in table orders.
Can anyone shed some light on the potential problem? Bear in mind I'm not an advanced user and have very limited knowledge on relationships in access.
The "last" total in an Access query is unpredictiable. I have a Journal, with the main form/dataset being linked to the entries of that day. I would expect the 'last' total word to give me the last entry of that day, ie. what I ate for supper. But in the two qrys shown, it does the opposite. What is the most relable way to get the last entry of that day (ie. the one with the highest journalID)? :confused:
i need a query to return the first orderdetailsID and related productsID, so that all the other products that are the same as oldest order can be manufactured together. In access help menu it shows it would be something like using the criteria under productname1 field: (SELECT [productname1] FROM [the next one to cut query] WHERE min([orderdetailsID])) But this returns an error cannot have aggregate function in where clause min([oderdetailsID])).
I've also tried with 2 queries, 1 with the min function and the using inner join, but can't get it to work.
Before I spend an age trying to get this to work I thought I'd consult some experts.
I have a main data table for recording all possible insurance claims against a company. What I want to do is create say 2 incidents but link them in some way. i.e. One of our drivers had a car accident (1 claim against our motor policy), but then the driver of the other car sues us(1 claim against our public liability).
I can create 2 seperate records and maybe have a field to highlight parent/child reference numbers, but is there anyway I could allow the user to click a button and see the related records simultaneously?
I am trying to improve a work process using an existing Access DB.We have a form with multiple texts boxes on it. I need to search through these boxes to determine the total number of occurrences of a specific value. This is not tied to a table.
The text boxes I'd be searching through all have related names: "Element0" to "Element40". And I'd most likely be looking for a "/" within the value in the boxes (value could be 12345/01, for example).Would then be using the result in VBA to apply some conditions, so I would prefer if I could do the count in VBA (the count/sum is the part that is hanging me up.)
I've found multiple ways to accomplish this from a table, but nothing for what I have to work with.I am unclear in my description of what I am trying to do, let me know and I will try to provide more information.
I have a subformClientOffers on my frmAllProperties. On the subformClientOffers is a field called Outside Agents. This field allows for multiple values to be selected.
The subfrmClientOffers is in datasheet view.
While the field Outside Agents DOES allow me to select more than one, they post in the field one after another:
Ken Shaw, John Doe, Jane Doe, Harlan Bestlyn
Therefore, the datasheet view of the subformClientOffers causes the field Outside Agents to get very long, much too long to view without scrolling. My client wishes for everything on the frmAllProperties to be viewable without scrolling.
Is there anyway, to cause the field Outside Agents to place a return between each selected name (sort of a wrap text kind of thing)?
One thing I might mention is that the field is a drop down field from tblOutsideAgents. The table itself has First Name and Last Name, but I concatenated that into Fullname on the form, so that when the user opens the drop down they see the entire name. I don't know if that would have any effect on making the 'return' thing work or not.
I can't use this field as a subdatasheet because I already have one subdatasheet attached to the subform to show multiple notes on each Client Offer.
I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).
An Example of Textboxes values on the search criteria form: Textbox - name: bob Textbox - address: Left blank Textbox - category: car,boat,truck
I Have tried creating a query with the following
field: name criteria: like “*” & name & “*” field: address criteria: like “*” & address & “*” field: category criteria: like “*” & category & “*”
SQL code: SELECT customerName ,address,category FROM prospectiveCustomers WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;
That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.
I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.
SQL CODE: SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category] WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;
Hi I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre? Thanks Aden
