How To Select Records Based On Summation Of Another Field
Jan 14, 2007
Hello,
I have a dataset that consists of Stands within a forest inventory. There are two Fields: the first field is StandValue (dollars), the other field is the StandArea( hectares).
I want to select the highest valued stands BUT the sum of the individual areas found in "standArea" cant exceed 10,000 hectares. Can anyone help me figure out an SQL query for this, or any other suggestions on how to proceed would be greatly appreciated, thanks
Greg
I've looked through the thread and was unable to find exactly what I'm looking for ?
Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.
What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.
So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
I have atable with fields time in and time out (short time type), in aquery i did a diffrence between that to fields, so I can know to evry employee every day how many hours work.
but, my problem is how do summation to the work hours to the month (it's a short time type).
example, an employee work from 8:30 to 15:00 in the first day, second day work from 9:10 to 14:20 ....etc at the end of the month i want to have report from query which give me sum. of hours the employee workes.
Is it possible to have a specific tab in a tab control selected automatically based on the value of a combo box? Not by what is selected, but by what is already stored in the field.
For example, if have a combo box with a value "shirts," the "shirt types" tab is automatically displayed in the tab control box.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.
I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).
I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c) and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
I am trying to add various values based on Select Case to the value of field. The problem I face is that each time when I get different Case in select statement, the value of the field rather changing adds the value on top.
Code: Private Sub ProductID_AfterUpdate() Dim qflPrice As Variant Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlQry As String Dim instID As Integer
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
How do I calculate a filtered total from a field in a subform. It needs to be filtered according to [agreementID] and I would like to total the records in another field.
eg: I would like the total assessment for all 01-pmg18's and the total for all 02-pmg18 etc. Can I automate this so it recognizes and totals all assessments according to each unique agreement id group. Customers can have many 'batches" of agreementID's belonging to him. The 18 also refers to his ID number it is typed in. Thanking you in advance!
I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example: For today, we would use 4085-0001, 4085-0002, 4085-0003, etc. For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
I have a huge database but this is only a sample. Basically, I want to combine the monthly_qty, annual_qty_used and months_of_use for the similar Part-Numbers (ie. with or without the 'W' suffix). Example, part numbers 0010-20524 and 0010-20524W should be treated as alike.
The Table 'wrong_outcome' generated from the saved query would not be able to handle this kind of situation. The correct Table should be exactly like the one I created: 'should-be_outcome' but I have no least idea how to query this. Can someone help me with a query or SQL.
I've found this board very helpful in the past and was hoping someone would be kind enough to help me out again. I have a problem similar to the one below:
I have a table which tells the supplier we ordered from and number of each of the 5 types of fruits my store orders from the particular supplier(apples,oranges,pears,etc). Those are my 5 columns.
I need to create a sixth column which I will call 'Multiple Types' in order to tell if we ordered more than one type of fruit from that particular supplier(and how many total fruits). Therefore, the following condition has to be met: If two or more of the five columns is greater than 0, I need this sixth column to sum the values of all the 5 columns(thereby giving me the sum of all the fruits ordered from that supplier). If this condition is not met, I need the column to be equal to zero.
Do you have any ideas how I might accomplish this task?
I have a table with a field that shows the number of pieces that a parcel contains. It looks like this: ParcelID, Pieces, Description
Data example: 1001, 5, Jackets 1002, 10, shoes etc
I need to print labels for each piece that shows to which parcel it belongs. The report will look like: 1001, Total pieces = 5, 1 of 5 pieces, Jackets 1001, Total pieces = 5, 2 of 5 pieces, Jackets 1001, Total pieces = 5, 3 of 5 pieces, Jackets 1001, Total pieces = 5, 4 of 5 pieces, Jackets 1001, Total pieces = 5, 5 of 5 pieces, Jackets
and 1002, Total pieces = 10, 1 of 10 pieces, shoes 1002, Total pieces = 10, 2 of 10 pieces, shoes 1002, Total pieces = 10, 3 of 10 pieces, shoes 1002, Total pieces = 10, 4 of 10 pieces, shoes 1002, Total pieces = 10, 5 of 10 pieces, shoes
If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it 2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
i stumbled upon this site via google while i was trying to find a solution to my problems! i'm a pretty proficient pl/sql writer and this is my first time trying to learn jet sql so a lot of the things i'm used to in pl/sql can't be done in jet sql!
1) i'm trying to create a summary record from RR_FT_COMPONENT to RR_FT_TOT_PAYOUT. this is basically summing some payout fields by grouping by payee_id and period_id. from my research i've seen that jet sql doesn't like sum or group by's for updates. is it the same for doing select statements for insert into?
when i run the query i get the following error: "ms access can't append all the records in the append query. MS access set 0 fields to null due to a type conversion failure"
all of the fields on both tables have the same type. is this a group by and sum problem? i ran the select statement separately and it worked fine.
SELECT max(rc.market), max(rc.region), max(rc.financial_center), rc.payee_id, max(rc.employee_name), max(rc.manager_flg), max(rc.lic_code), max(rc.job_code), rc.period_id, sum(rc.volume), sum(rc.cp_spread), sum(rc.ytd_spread), sum(cp_comm), sum(ytd_comm), max(prior_yr_flag) FROM RR_FT_COMPONENT AS RC GROUP BY rc.payee_id, rc.period_id;
2) in this next code i'm trying to sum spread information in the ft_txn_summary table for a given payee_id, market and period_id. i'm using dsum and have the appropriate joins to keys on the tables, yet this update sums ALL records in the ft_txn_summary table rather than summing the specific payees in the join. any idea on what's wrong or how to debug dsums?
code: UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary.payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] AND "ft_txn_summary.period_id= " & [rc.period_id]) WHERE rc.component_name='Total Revenue';
