how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.
I have a query which I want to based on a group selected on a form using a selector button. EG if a person is a member of the accounts team that button would be selected. I could then just run the query to get members of the accounts team.
I am using the following SQL, but find when I run it "every" record is selected....
Hi I have 2 tables(tblA and tblB) and I want a query result(tblC) where ‘group by’ only one record per individual, with the total added together and the type starts with ‘01’. Below(tblA and tblB) are examples of the two table. TblC is the query result that I want. tblAName---Type---Amount---StatusJoe---01FAE---$10---NewJoe---01FBE---$10---NewLee---01FBE---$10---OldBob---30FBE---$30---NewtblBName---Type----Amount---StatusJoe----01FBE---$5------NewLee----01NAE---$20-------OldLee----30FBE--$30------NewtblCName---Type---AmountJoe---01FAE---$25---NewLee---01FBE--$30---OldI have tried linking/joining, adding SUM function, First, Group By, etc. but all the results came out to be all different. Also, am I support to add a ‘join’ of tblA to tblB on BOTH the NAME and TYPE? I am so confused! Please help.Thank you very much. Joe
I have a user who can't operate a mouse and I want to make it possible for him to select from an option group using only the keyboard.This is possible if a default button is stipulated, but can it be done when there is no default?
There is no event associated with the keyboard in an option group, only with the controls within it. I tried using the Got Focus event on an option Button, but entering the control doesn't cause the options to receive the focus, seemingly.
Any ideas, (they don't have to be fully formed) would be gratefully accepted.
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
I need to create a simple database where I have a list of people, a list of groups and all I want to do is select which people belong to specific groups.
All I need is to create a form where I have a list of my people and a tick box next to the groups to show who belongs to which group.
I have a form with an option group to select subforms to be displayed .To save space on the main form I have added a subform (frmSubService) to the option group to display some of the data that was originally on the main form. The subforms data source is the same as that of the main form but only displays five fields.
After editing data in the subform frmSubService, if I then click on a field on the main form no problem, however if I click on an option button to view another subform I get a messagebox pop up with the following message;
The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-Edit the record.
If I click OK,and without re-editing the record, I can open another subform.
I have checked and the data changes made on frmSubService, in both cases, has been saved.
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code: Set objDialog = Application.FileDialog(4) With objDialog .AllowMultiSelect = False .Title = "Please select a File" .InitialFilename = "C:" .Show If .SelectedItems.Count = 0 Then MsgBox ("Action Cancelled") Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I need to output an HTML table with the following headings:
============================================== Event Title | Event Price | No. Delegates Booked | Total Price ==============================================
How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.
We're developing a database to manage tournament registrants as well as event results.
The key tables we have ar: ContactsRegistrationsRegistration DetailsTournamentsAgeGroupsBreakingAgeGroupsExperienceGroupsEventsPayments
There are queries for using information in the tables to determine age, competition divisions etc.
At the moment the RegistrationExtended query, which uses the ContactsExtended and TournamentsExtended queries and the Registrations table to determine the number of events for each registrant as well as what division and age group they are assigned. This is done with some IIF expressions.
For the 2012 event we had 4 division classes based on experience and 6 age groups. For 2013 there might be more division classes and age groups. So now I have the tables AgeGroups, BreakingAgeGroups, and ExperienceGroups. The two age group tables have a query that calculates the age group name based on the values in the MinAge and MaxAge fields. For example 8 & Under, 9-11, 12-14 etc. The ExperienceGroups also have a minimum and a maximum field but only one field will be completed for each experience level. If the value is a maximum then that would be for those with less than the maximum experience and if the value was a minimum then that would be for those with minimum of that many years of experience. For example Grasshopper for less than 1 yearWarrior for those with 1 year to less than 2.5 yearsSamurai for those with 2.5 years and less than 4 yearsShogun for those with 4 or more years.
We want to maintain the history so the age groups and experience groups are now tied to the tournament.
I'm think that I'll need some VBA code that will compare the registrant's tournament age to the ranges assigned to that tournament and will assign them to the correct age groups. Then there will be code to compare the registrant's experience and assign them to the correct division class. The key here is that each year might have a different number of age groups and experience groups as well as different experience group names. For instance in 2013 we may change the 8& Under group to 7-8 and add a 6 & Under group and we may add another level called Emperor for those with 20 years or more experience.
is there a way to count - in one query - the number of records that fall between specific times:
Time CountOfRecords 0000-0100 7 0100-0200 2 etc 2300-2400 4or do i have to do separate grouping queries then separate counting queries for every hour of the day? a crosstab? something else perhaps? feels like i'm missing something obvious (i hope...).
I have a table which holds parts sales data for 6 individual dealers, and I basically want to be able to produce a report which details the top 10 selling products by volume by group. I can produce a query which gives me the top 10 results of ALL of my groups accumulated, but really need this to be split by group, and would prefer not to create 6 different queries to obtain the data.
My table (tbl_Part Sales by Dealer) has the following fields:
Dealer Code Part Description Volume Sales
So I'd like the results to show the top Part sales by Volume, for each of the (6) Dealer Codes within the table.
my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message Which Reads: Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0
What I have noticed, I maybe wrong, the record is a available for the "group_header" section closest to the detai section, in my case, "Client_GroupHeader". However, I need information in the record to format goup header information in the "Customer_Groupheader" and "State_Groupheader. Can anyone help me with this.
I've got a table, where each record is a group of students. So the fields include Student1, Student2, Student3, etc. I know this is bad database design, but I've done this because I want the group number (primary key) to be automatically generated.
Now, I want to add more information for each of these students as if they're seperate records but I also want an autonumber for the group.
I am trying to email a group of records in a table and with the code I have written, it just loops to the first record in the recordset and will not move down to the next record. Below is my code.
Code: Private Sub TestOutlook() Dim db As Database Dim rstMail As Recordset Dim appOutlook As Outlook.Application Dim MailOutlook As Outlook.MailItem
I have a database with numerous nutrient lab values per food item and zero to 20 tests per food item; some 600 food items
I want to select the last 5 tests per food item which should be no problem using the "TOP " type statement.
After I have the "TOP 5" record I would like to create another field to number each record automatically with in the query so I can run a cross tab query to display these records 1 thru 5.
I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...
At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter
Code:
SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator FROM MatterContactsMade GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator])) ORDER BY MatterContactsMade.MatterId;