This database is for a livestock show that I have been working on for quite some time now. This specific 'section' is for the Supreme Competitor award, in which points are given for the showman's placing in showmanship, ONE highest placing animal in market classes, ONE highest placing animal in breeding classes, and the showman's score on a quiz. I'm having a problem assigning 'points' for a single highest placing animal in market and breeding classes.
I would like to create a query/s that selects all animals shown by an exhibitor in a market class, then take the highest placing animal that the exhibitor had and award (update the livestock table-points field) points for a single animal. This is fine until one exhibitor has MORE than one animal that received 1st place. How do I go about telling the query to just pick one, lol... it doesn't matter which 1st place animal it selects to award the points... just as long as only ONE animal is updated and not all of the exhibitor's animals that received 1st... Then do this again to select one highest placing animal from the breeding classes.
Here's a little outline just for visual sake:
Market Classes
Name Tag No Class Placing <points field update>
Sally Johnson 100 1 1st 10
Sally Johnson 101 2 1st
Sally Johnson 102 3 1st
Kim Smith 200 1 2nd 5
Kim Smith 201 4 2nd
Kim Smith 202 5 3rd
See where Sally received 3 1st places, but only one of them is selected to be updated, and Kim received 2 2nd places (her highest placing) and only one is updated.
Thanks SO much in advance... this has really got me stumped.
I am looking for a query to select the second highest value (in this case a date) from a table.
For example, if I had the following dates: 9/1/06 9/2/06 9/3/06 9/4/06
I would want the results of my query to be 9/3/06.
My table is called tblEquityFunds and the field I'm looking to select is called Added. I have the following query, which I think works, but it seems like an awfully goofy way to do it:
SELECT DISTINCT TOP 2 Max(tblEquityFunds.Added) AS MaxOfAdded FROM tblEquityFunds WHERE tblEquityFunds.Added Not In (Select Max(Added) FROM tblEquityFunds))
I'm trying to identify the 2nd highest number in a field in Access. While searching, I ran across the =large(a1:a3,2) formula, which works perfectly in Excel. I'm trying to duplicate this functionality in Access 2003, but it looks like this formula isn't in there. Any ideas on how to accomplish this?
I have been tasked to determine the actual cost to build a product by summing the cost of the most recent required components to come off of our assembly line. For example, say I'm trying to determine the cost of a car with multiple sub-components (1 engine block, 4 doors, 4 wheels, 2 headlights, etc.). We make parts for all kinds of products (cars, boats, televisions, etc.). All of the data for ALL of these parts are contained in a table called PART_DATA (i.e. part ID, date started, date finished, hours spent, etc.).
Then I have a list of parts required to build a certain model car in a table called CAR_PARTS_LIST. This table contains data like (part ID, quantity needed, etc.).
If I want to know how much it would cost to build a car using the most recent car-part data, I need to pull data for the most recent required car parts to come off the assembly line that are required for a single car. That is, my car needs 4 wheels, so I need data for the most recent 4 wheels to come off the line.
How can I write a query for the "4 most recent wheel records" given that "4" is specified in the CAR_PARTS_LIST?
(I am a notive in database calculations) I have a human resources database with a report that calculates how long a person has been employed and calculates vacation and sick time based on the number of years employed. Is there a way to take the single highest value in vacation time text box and multiply it by a predetermined number? (i.e., if 5 is the highest number in the list for the person, multiply it by 24)
I have a query which is currently using a prompt box to ask the user to input a machine number. This is run from a button on a form. Press the button and the prompt appears .I would like to now do one of two things either make a form with every machine number on it or use a combo box to select the machine number. My problem is that I don't know how to get the combo box or the control button to link to the individual machine number.
right i have been searching this forum looking for answers to my problem with no luck - i have been searching other forums and i now know how to do this using oracle database which isn't much help because i am using access!
what i want to do is a simple update setting the value of one field but i want to do it on only the records which are returned by a select statement
apparantly oracle has a construct
SELECT <select query here> FOR UPDATE <update query here>
can you do this in access? or is there a workaround?
specifically what i want to do is to read in an excel file which contains all the fields for some records which are already in the database
the table they are in has a valid field (boolean) which when false effectivly means they are deleted from db (we use this instead of actually deleteing so we can duplicate search against previously held records)
i want to find all the records which are in said excel file and set valid to false
so the two parts of this are pretty simple the update is simple, the select is simple but i need to put them together
I have a KidShop table that shows the Diagnosis for the person,there are 5 diagnosis fields because one person could have up to 5 Diagnosis and each field is a lookup from the diagnosis table.in the report they show up as the ID number not and not the text.
I inserted Diagnosis from the Diagnosis Table and a window popped up asking what Diagnosis from the Diagnosis table matched in the KidShop Table and gave the selection of Diagnosis 1 or 3 or 3 or 4 or 5. I could only select one and I selected Diagnosis1 in the KidShop table and that showed Diagnosis as text for the Diagnosis1 field but the other 4 diagnosis fields still show a number. I understand what that did but it does not give me the option to match Diagnosis 2,3,4,5 to diagnosis Table. If I changed the Data source for the other 4 to Diagnosis then only Diagnosis1 shows up it the other 4 fields which makes since.
i have two tables in a one to many relationship, TBL_submissions is a table containing global information about claims submitted by contractors for work done (invoice number, total claim value, contractor etc..), each submission being a batch of claims for specific jobs (measures)
TBL_Submissions is in a one to many relationship with TBL_Measures because one submmsion contains information about many measures. Each record in the Measures table contains only one field IDSubmission relating to this global information, that is IDSubmission which is an autonumber primary key of the submissions table, i.e it is foreign key in TBL_Measures
Each submission comes in to us as an excel file and there is a form where users fill in the global information into text boxes and combo boxes then click an import button browse to the excel file and it gets pulled into a temporary table TBL_TMPSubmission
a query adds an IDSubmission field to this temporary table and then what i want to do is fill it with the autonumber of the record i have just added to TBL_Submissions by pulling all the global information from the form - i can then use a simple append query to load all the new data into TBL_Measures
The buit to add the IDSubmission filed to the temp table works fine and the append query is easy enough i had that going without the ID filed before i added relationships to my database i am trying to fill in the last entered ID with the following statement
UPDATE [TBL_TmpSubmission] SET IDSubmission = (SELECT MAX(IDSubmission) FROM TBL_Submissions)
but i get the error "operation must use an updatable query"
is this a simple syntax error or am i going about this the wrong way?
Hey All!! This is what I wanna get done. I have a big table I am querying into and extracting data and populating it into the new tables that I have constructed.
For eg: SELECT dbo_analyst.anls_id, [fst_name] & [lst_name] AS Analyst FROM dbo_analyst;
This query selects the name and the ID of the Analyst out of the big table. I want to store these values in the Analyst table that I have made. Do i need to combine an Insert or an update query with the select query to simultaneously put the values in the Analyst table. Please help me on how to go about with this.
I'm currently working on fixing an older 97 database that I've updated to 2010. I have just populated the Drop down box with about three fields. Ideally what I want to do is after having selected the item number from the drop down box I then hit the button that creates a report with the information about that item number.
At the moment...If the box is blank it reports all the item numbers, however if i fill the box(select an item) it returns nothing i.e the report is blank.
I've looked at the query that builds the report, there are only three fields that populate the. location, part number and description. from a tbl called MainDetails
the only other thing: If([Forms]![frmReports]![FLoc] Is Null,[FUNCTIONAL LOCATION] Is Not Null,[FUNCTIONAL LOCATION] Like [Forms]![frmReports]![FLoc]) it has a criteria of <>False *FLoc is the drop down box *functional location is the location field. I believe all the above does is populate the report if Floc is empty.
What can I put to make the report generate what ever I pick in the drop down box 'FLoc' source the three fields from the 'MainDetails table'.
using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
I'm having a small problem converting a select query I wrote into an update query. Below is my original select statement:
SELECT Mid([address3],1,InStrRev([address3]," ")-4) AS CITYx, Mid([address3],InStrRev([address3]," ")-2,2) AS STATEx, Mid([address3],InStrRev([address3]," ")+1,10) AS ZIPx, [address3] AS Expr1 FROM Exercise2 Where right([address3],1) <> "E" and address3 is not null;
The above statement basically parses the address field. Now what I need is an update statement that will use the above code. I'm needing to update the empty fields for city, state, and zip from the field address3 which contains all 3 combined.
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work: SELECT [tlkp.Language].[LangID], [tblRawData].[LangName], [tblApplication].[AppID] FROM (tlkpLanguage INNER JOIN tblRawData ON [tlkpLanguage].[LangName]=[tblRawData].[LangName]) INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE? I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication. I have tried everything and I keep getting syntax errors.
Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(
Anybody please help me out? ------------------------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then MsgBox "Please select an Absence reason" Cancel = True End If
I have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
