IIf-summation Problem
Mar 20, 2007
Hi,
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?
Thank you all for your time.
View Replies
Jun 7, 2007
Hello Mdb Gurus,
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.
Any help is very much appreciated. Many thanks.
View 8 Replies
View Related
Jan 17, 2007
hi,
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.
thanks to help me
suhad
View 2 Replies
View Related
Aug 29, 2007
hello all,
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.
code:
INSERT INTO RR_FT_TOT_PAYOUT ( market, region, financial_center, payee_id, employee_name, manager_flg, lic_code, job_code, period_id, volume, cp_spread, ytd_spread, cp_comm, ytd_comm, prior_yr_flag )
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';
any help would be appreciated! thank you!
View 1 Replies
View Related
Dec 23, 2013
How can I bind a textbox to summation query's value? I tried the formula builder and this did not work:
=[qryAutoMatchSum]![SumField]
View 1 Replies
View Related
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
View 2 Replies
View Related
May 24, 2013
Quick summary (See Attached)
Data is stored in 11 tables each with matching primary key. Each table has in the range of 20 fields of which only numbered data type are being used in calculations.
Each Data table has 3 augmenting queries. All these queries work individually.
Summarizing query "QStationCountList_Sum" works fine.
Calculating a summation score by each "Master_PS_ID" works fine if I only try to summarize data by 3 of the calculating queries. As you can see in the attached database "QStationTotalScore1_6" query based on 6 gives "Too many fields defined". (I have tried so many fixes I can not remember and received messages such as "To Complex") While a similar query on 3 works fine.
At this juncture I plan to build 4 new partial sum queries each from 3 collecting queries and then build a 5 Summation query adding the results from the Sum queries. Surely there is a better way.
View 3 Replies
View Related
Nov 5, 2013
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.
View 13 Replies
View Related
