Queries :: Selecting Maximum Value For Each Primary Key
Mar 9, 2014
I am doing an outline design of a database system to handle the results from an archery competition in a couple of weeks.
Currently I have a table with a primary key to hold the information on the competitors (name, age etc.) and this is linked to a table via a foreign key (the archers name) to enable multiple scores to be recorded against each archer.
For the purposes of the competition only the first score shot each day is valid and so I am using a yes/no box to filter out these results.
What I would like to do is then filter the results such that only the largest of these first session scores is displayed for each archer. (i.e if archer A shoot 578 on day 1 and 585 on day 2 i would like the database to only report the 585, and similarly for the other entries).
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
A query TaxType_Query produces the following results
TaxType; FromDate; ContactID, FullName W2; 7/1/15; 1, Tom 1099; 4/1/15; 1,Tom W2; 1/1/15; 1, Tom W2; 1/1/10; 2, Dick 1099; 1/1/09; 3, Harry
I need the query to show only the rows with the maximum FromDate for each ContactID (result would be the 1st, 4th and 5th rows only). I assume there is a quick SQL statement that will work better than Totals / Max criteria method. If I set the FromDate criteria to Max the TaxType field is a problem as they have different values I cannot group by that field.
I am needing help with how to set up the code to select different queries based on different conditions.
The database is to be set up so that I can pull studies at different time points based on if they are under long term, accelerated or stressed conditions. When this condition is selected there are only certain time points which are to be run, example..... Long term: 3,6,9,12,24,36,48 and 60 months. Accelerated: 3,6,9 and 12 months.
As you can see I have the dates calculated for all of the weeks and months that the sample could be pulled at, Inventory Transactions Table. And I am able to generate throught the Pull Dates Form a list of only the records to be pulled under Long Term conditions. I would like to have the form set up so that when I choose the Accelerated condition that it would pull from another Query like the Pull Dates Long Term, but only have the Accelerated time points listed.
I am not sure if I need an If/Then statement and if so how do I set this up.
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):
-------------------------------- Name Result Date -------------------------------- Bob 5 1/1/2014 Bob 6 2/2/2014* Bob 7 3/3/2015 Bob 8 5/4/2015* Len 5 1/1/2014 Len 6 2/2/2014* Len 7 3/3/2015 Len 5 7/4/2015* ---------------------------------
how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?
Bob 6 2/2/2014 Bob 8 5/4/2015 Len 6 2/2/2014 Len 5 7/4/2015
(The database is normalised and this table has an ID field).
I have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code: CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
I am trying to combine fields (different columns) into one field. However I am trying to have a criteria for the fieldname. For example I wish only to combine the columns that contain the text 2003 (ie [Value 2003],[Price 2003]). I have tried to use wildcards in SQl ie select * from [query] where * = %2003. I must be missing something because I can't seem to work it out. It must be simple. I have also tried several ways through the design grid with wildcards in expressions. I also don't want to union the columns by using the exact names ie [Value 2003]& [Price 2003] because additional columns may be added.
Any help, extremely appreciated, I have wasted a whole day with this.
I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.
I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.
Table: t_AmortPeriods Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate
Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.
My sql looks like this:
SELECT t_AllMonths.MonthStartDate, Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate, t_Assets.AssetID FROM t_AllMonths, t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);
I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot FROM EmpTable S1 WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
Table1 has fields docno (text) title (text) progress (number)
sample data is like below:
doc-001 test1 90% doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number) icon(attachment type)
1 image1 2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
I have a table (Quarter) which acts as a source for another table. When I create a query based on the latter in the Datasheet view the entries of the second column of table Quarter are shown. The PivotTable field however displays the primary key.Where do I need to make the change to only have the second column entries (Q1, Q2, ...) shown in the PivotTable view as well?
I bounced into a big problem with Access 2010 where I cannot seem to be able to remove duplicates from a table containing millions of entries midway through a series of queries.
As you can see, there are some rows which contain exact duplicates and some are a bit different. I wish to remove those fields with exact duplicates and only the duplicated fields. Running "Find duplicates" in Access gives me about 250,000 rows with such data.
I've tried a few options already: - I cannot use the date as primary key as there are several serial numbers. I cannot use serial numbers as primary key, because there are several dates. Using reading value as primary isn't an option either. - Microsoft says I should mark all duplicate values with an x and then make a delete query to get rid of all the x-marked rows. For 250,000 duplicates, that's a bit too much manual 'x-ing'. - If I do a delete query using the Find Duplicates query as a base, it removes all 250,000 entries from that table, instead of just the 125,000 which had a duplicate elsewhere in the table. - If I make a query which identifies duplicate data and gives me just one row for each duplicate, the delete query still deletes both entries from the original table. - I could make a new query which would have only unique values using Totals as criteria (for instance, using First for the Date-column). However, this still leaves the duplicate values in the original table. Note that this database is already 800 MB large and new data is imported once a week, for the next decade or so. I cannot have a table get duplicates every week and leave it there. - If I make a macro which would create the unique values table first and then deletes the old table, what happens next week when I try to import new readings? I would need to make a new macro each time I try to import new data as the table names change. Or is there a way to first run the unique numbers out, then replace the original table with the new one? With a 800MB database, this would put me dangerously close to the 2GB size limit. I wouldn't be able to use this as a part of a macro, as the database would have to do Compact & Repair each time it deletes the original table, midway through a longer series of queries. - Having duplicates removed from the original import isn't an option either, as it comes in as a overriding excel sheet for the past 3 months, once a week.
As you can see, it's quite a pickle getting the duplicates out of the original table. This is just a small part of a very long macro, which takes about 15 minutes to complete, but due to duplicates the database is getting way too large.
I have an access table which has a key using letters (A,B,C,D...etc). Against each letter there is multiple rows against each letter. However for testing purposes I only need to get a single row against each key and I was wondering if there is a function I could use which would return only the first record of data for each key?
I have a table which has a primary ID and then date/time then an empid field relating to an empid table,
Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whichever is easiest?
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
OK, I've tried everything I can think of with this query/subform/records problem. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...
I am trying to create a database which I can use to store client’s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each “order” of an advertising product, including the client’s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).
I have a main form called frmListings, which displays the client’s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.
Now the tricky part…
The subform displays a blank row for a new record, and I would like to be able to enter a new advertising expense in that blank record (date, type of ad, and cost), and have that data added as a new record in the tblAdvertisingExpenses table with a new auto-numbered OrderNumber. I don’t know whether it’s because the subform is actually a dynaset from a query, or because I’ve constructed the query incorrectly (highly likely, I’m still pretty new to queries), but when I add in the details for the new record, I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship (etc, etc).
I suspect that Access is attempting to write the record (along with the ListingID) back to the tblListings table, where the same ListingID already exists (uniquely) as the primary key. But I don’t know for sure, because I don’t really know what the heck I’m doing. What I actually want to do is have the new record I’ve entered in the subform added to the tblAdvertisingExpenses table, which is the record source for the qryListings query which displays records in the subform. It shouldn’t be a problem, should it? So why can’t I get it to work? Perhaps there’s a fundamental flaw in the way I’ve broken up the database into tables, or my assigning of primary keys and foreign keys. Or perhaps there’s a flaw with the way I’ve constructed my query. Or am I forced to have a completely separate form solely for entering data into the tblAdvertisingExpenses table? Or will I need to use a macro or VBA to accomplish what I want?...
Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven’t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I have a make table query that gets its info from another table that is updated weekly. I would like that when we run the Query Type (Make Table), we would like to add a primary key to a field that exists already starting at 1 every time the query is run.
