Compare records in a query then write equation??

Hi all, i have a problem and i need help, the case is as follows: the
default rule is that i have 4 fields, (Date, Team, Score). each team
is allowed to have one score per day but sometimes it can have 2
scores per day, when this is the case i wanna multiply each score by a
certain number and have one score instead of 2 scores (similar to
average but not average). So, i need to have a condition which
compares records, and if this is the case, formulates this equation
and gives me one score instead of 2 scores. Please help

0
Samer
9/4/2007 6:52:03 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
680 Views

Similar Articles

[PageSpeed] 54

SELECT Team, [Date],
Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
FROM SomeTable
GROUP BY Team, [Date]

Interesting fact is that you only listed 3 of your 4 fields.

IF you don't know how to do this in the SQL window, then post back and I 
will try to describe how to build the query in the query view.


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Samer" <samoora44@gmail.com> wrote in message 
news:1188888723.880304.124000@k79g2000hse.googlegroups.com...
> Hi all, i have a problem and i need help, the case is as follows: the
> default rule is that i have 4 fields, (Date, Team, Score). each team
> is allowed to have one score per day but sometimes it can have 2
> scores per day, when this is the case i wanna multiply each score by a
> certain number and have one score instead of 2 scores (similar to
> average but not average). So, i need to have a condition which
> compares records, and if this is the case, formulates this equation
> and gives me one score instead of 2 scores. Please help
> 


0
John
9/4/2007 11:29:41 AM
On Sep 4, 2:29 pm, "John Spencer" <spen...@chpdm.edu> wrote:
> SELECT Team, [Date],
> Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
> FROM SomeTable
> GROUP BY Team, [Date]
>
> Interesting fact is that you only listed 3 of your 4 fields.
>
> IF you don't know how to do this in the SQL window, then post back and I
> will try to describe how to build the query in the query view.
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Samer" <samoor...@gmail.com> wrote in message
>
> news:1188888723.880304.124000@k79g2000hse.googlegroups.com...
>
>
>
> > Hi all, i have a problem and i need help, the case is as follows: the
> > default rule is that i have 4 fields, (Date, Team, Score). each team
> > is allowed to have one score per day but sometimes it can have 2
> > scores per day, when this is the case i wanna multiply each score by a
> > certain number and have one score instead of 2 scores (similar to
> > average but not average). So, i need to have a condition which
> > compares records, and if this is the case, formulates this equation
> > and gives me one score instead of 2 scores. Please help- Hide quoted text -
>
> - Show quoted text -

Sorry for this mistake, the 4 fields are (Date,Team,Score,Volume),
basically if a team have more than one score per day i want to
multiply each score by its corresponding volume, then divide all by
the total volume of this team. This is specifically my problem, i hope
it is becoming clear now, thanks for the assistance. i am aware with
sql and i can write sql code, so go ahead in sql. it is urgent, so
please help

0
Samer
9/4/2007 1:15:43 PM
If I have understood you correctly, then perhaps the following will work for 
you.

SELECT Team, [Date],
    IIF(Count(Score) = 1, Sum(Score), Sum(Score * Volume)/Sum(Volume)) as 
CalcScore
FROM SomeTable
GROUP BY Team, [Date]

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Samer" <samoora44@gmail.com> wrote in message 
news:1188911743.143716.42950@19g2000hsx.googlegroups.com...
> On Sep 4, 2:29 pm, "John Spencer" <spen...@chpdm.edu> wrote:
>> SELECT Team, [Date],
>> Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
>> FROM SomeTable
>> GROUP BY Team, [Date]
>>
>> Interesting fact is that you only listed 3 of your 4 fields.
>>
>> IF you don't know how to do this in the SQL window, then post back and I
>> will try to describe how to build the query in the query view.
>>
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> .
>>
>> "Samer" <samoor...@gmail.com> wrote in message
>>
>> news:1188888723.880304.124000@k79g2000hse.googlegroups.com...
>>
>>
>>
>> > Hi all, i have a problem and i need help, the case is as follows: the
>> > default rule is that i have 4 fields, (Date, Team, Score). each team
>> > is allowed to have one score per day but sometimes it can have 2
>> > scores per day, when this is the case i wanna multiply each score by a
>> > certain number and have one score instead of 2 scores (similar to
>> > average but not average). So, i need to have a condition which
>> > compares records, and if this is the case, formulates this equation
>> > and gives me one score instead of 2 scores. Please help- Hide quoted 
>> > text -
>>
>> - Show quoted text -
>
> Sorry for this mistake, the 4 fields are (Date,Team,Score,Volume),
> basically if a team have more than one score per day i want to
> multiply each score by its corresponding volume, then divide all by
> the total volume of this team. This is specifically my problem, i hope
> it is becoming clear now, thanks for the assistance. i am aware with
> sql and i can write sql code, so go ahead in sql. it is urgent, so
> please help
> 


0
John
9/4/2007 3:41:33 PM
Reply:

Similar Artilces:

Query
I have a transaction # for each record in my main form with a subform "Approvals" and "Checkouts". I created a query to show the sum of all Approvals and all Checkouts by Transaction # for each record. (one for Accruals and one for Checkouts). All approvals and checkouts come up in these queries. I want to create a report showing, per Cost Center, the $ amt of Approvals, the $ amt of Checkouts, and a calculated field to show the remaining value. The report comes up with all approvals and checkouts per cost center, however, if there is an approval that does not hav...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

MSCRM Opportunity's to allow price override and write in products
The Opportunity functionalirty should allow users to over-ride the price list values as per the quote functions. Additionally the option to have write-in products, again as per the Quotes functionality, should exist as part of the standard Opportunity functionality. This therefore gives the sales person flexibility is pricing the products to suit the client and also include products that are not standard price list items ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I...

How to compare "varchar" values?
I need to compare a student's score (that s/he got it after a test) with the score requirement. The problem is the scores are either a "character" only, i.e., "2" OR combined a "character" AND the '+'/ '-' character, i.e., 2+. For example, if the require score is 2+ and the student's score is 2 then that student is not qualified. The datatype of scores is varchar. Can you please help in programming how to compare these values? Thanks a lot in advance. I appreciate it. I see two approaches. The first one is a trick, ...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

Migrating notes records
Using Data Migration Manager I have uploaded 68000+ notes to CRM 4. However all notes show a creation date of 5/10/2008. Even more strangely, I have found that the AnnotationBase table contains the correct dates from the import file in the CreatedOn field, but has not set the OverriddenCreatedOn field. The server was built a few months ago, but certainly not in October 2008 - anyone have ideas as to how I can persuade CRM to recognise the values in CreatedOn? ...

pre-select new record in combo box
Hi, I have a form (frmActivity) with a combo box (cboStore) that gets its data from a table (Store). If there is a new store which dose not show in the combo box I have a command button on the same form that open a popup window for entering the name of the new store. In the 'on close' event of the popup I requeary the combo box (cboStore) on the main form so that the new store will appear. What I would really like is to have the new store already selected in the combo box when the popup is closed. Is this possible and if so how? Thanks, Phil Try after the requery of the ...

Crosstab query totals
Need some help, I have a crosstab query that returns the following data see sql below, what I need is to total all gearbox types that are R and 37 and R and 27 and R and 47 etc. an example R and RF 27 total = 492 <> 1 2 4 SubTotals R 27 20 26 303 12 361 R 37 6 66 307 8 387 R 47 8 87 424 2 521 R47R 37 2 2 R57R 37 3 2 5 R67R 37 1 1 2 R77R 37 19 19 RF 27 4 17 109 1 131 RF 37 1 11 76 88 RF 47 1 25 39 65 TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType ...

location of web query
I have a file that refreshed 2006 data that I am now changing to 2007 but I cannot recall where a web query is on a certain worksheet, is there an easy way to find in which cell the query resides? ...

Access 2007 SQL Pass Through Queries
I have an Access 2003 mdb that I use to connect to a SQL 2005 database. When I open the application in Access 2007 it errors on using SQL pass through queries. I see that pass through queries (amongst other options) have been disabled under Access 2007. However it seems that they are enabled under certain circumstances so I've been trying to get them working by changing the security settings. The queries are still failing after setting the following: - Sandbox Mode to 0 (turn off sandbox mode for all apps) - Applied a recognised Digital Signature - Trusted the Publisher - Trusted the loc...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Attachments to window notes vs record notes
Got an odd situation. On record notes we have the paperclip and can attach files with no problems. On window notes there is no paperclip. Never tried to attach a document to a window note before but always assumed that both record level and window level notes would act the same way. Has anyone else seen this? Should we be able to attach documents to window level notes? GP10, SP3. Thx. No - the behaviour you are seeing is by design. Window notes have never allowed for OLE attachments. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC h...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

DataSet write xml
Hi, I have observed that DataSet.WriteXML() method does not store "Allow Zero Length", "Indexed", and "Required" properties of the fields of a table in the table schema. dataset.WriteXml(stream, XmlWriteMode.WriteSchema) How do I instruct WriteXML() method to include these information along with field name, type and size? <xs:element name="field1"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:maxLength value="64" /> </xs:restriction> &...

Corrupt record in Money 2004
I have one record in my .mny file that I can't clear when I try to accept it after it has downloaded from my credit card site. I keep getting the message that the transaction cannot be entered and it remains outstanding - bolded. I tried the salvage processes - msmoney.exe -s and salv.exe. Neither worked. I just want to delete this one record and put it back manually. Is there a way to do this with jet database functionality? Not that is exposed to any of us users. The database is actually "msisam"--a buttoned up derivative of Jet. "Bill Boffi" <anonymo...

How to create new record
How can i use code to create a new record into a table. Something like this? CreateNewRecord("John","Smith") I tried to Google it but i can't seem to find the answer. help! thanks On Wed, 4 Jul 2007 19:34:03 -0500, "Tim McGavin" <nws@gsw-inc.com> wrote: >How can i use code to create a new record into a table. > >Something like this? > >CreateNewRecord("John","Smith") > >I tried to Google it but i can't seem to find the answer. > >help! >thanks > Open a Recordset based on the table and use ...

wrapping text in a query field
I set up the field in table to memo and tried entering a lot of information but when I open the report that field does not expand to show all of the entries. Can this be done in query and reports both. Thanking you in advance. Mary Lou On Dec 12, 12:06 pm, MaryLou <Mary...@discussions.microsoft.com> wrote: > I set up the field in table to memo and tried entering a lot of information > but when I open the report that field does not expand to show all of the > entries. Can this be done in query and reports both. > > Thanking you in advance. > Mary Lou Go to the p...

View equations created in Word 2007
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Greetings! I've successfully installed Office 2008 update 12.0.1 on my MacBook Pro running 10.5.2. One of the fixes that was listed in the Microsoft Knowledgebase article associated with the update was the ability to edit equations created in Word 2007 for Windows. After applying the update, I still get a warning message when opening a file created in Word 2007: "This file contains Word 2007 for Windows equations"; the document opens with placeholders (blank squares) instead of the equations. Does anyone el...

Can you help me with a troublesome query?
I'd like some help formulating a query for the following situation. The problem I actually have to solve is somewhat complicated, so I've extracted the essence of the problem into the simplified situation described below. While I know I could do this with an ugly cursor solution, I'm pretty sure that performance would be terrible as the table grows to a large size, which it will in the real problem I have to solve. So I'd like to hear your best ideas on how to solve this in a way that will scale to as high as a million records or more. Consider this table: ...

Query plan isn't doing an index seek
If you have the following: SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = ws.VendorID) FROM Work ws What I get is a Hash Match from 2 index scans. |--Compute Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName])) |--Hash Match(Right Outer Join, HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as [ws].[VendorId])) |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR])) |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS ...

Graphing ellipse equation
How do you graph an ellipse euation in the excel? The easiest way is to calculate X and Y parametrically. I used the angle theta, which I varied from 0 to 360 degrees. I can use cos(theta) and sin(theta) to get the coordinates of a circle of radius 1. If my ellipse formula is (x/a)^2 + (y/b)^2 = 1 then my points are simply related to the above by factors of a and b. So put angles in column A (0 to 360 in increments of 15 worked adequately well in my example: this filled A3:A27). Put the values for a and b into cells B1 and C1. Use this formula in B3 for X: =B$1*COS(A3*PI()/180) and ...

Adding records
Is there any way to add a record into the middle of a database without having to add it at the end and then sort. I need to be able to manually add records into the database, as though you would in excell, exactly where I want it without having to use sort since not all of the data can/should be sorted. Any thoughts? Tables are unordered. If you need to Order records then you use a query. You then need a way of defining that order. A more specific description of your problem including descriptions of the fields involved will get you a more specific answer. Cheers, Jason Lepack On M...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

Data/Values change when Query Analyzed in Excel
I have a query with 3 tables that represent master item list, count qty and as qty. When I look at the results in Query, all is well and accurate yet when I select Analyze with Excel, values change. For example, if a record shows zero qty in the as of field and zero quantity in the count field, I get a value in one or both of the fields that origianlly were zero. Other than linking the tables and grouping by master item list (to show all parts regardless of qty's), there are no formulas or expressions in this query. One of the tables is linked to a FoxPro table via ODBC driver. ...

Need help to structure simple (I think) query...
I think this is a simple query but I am not well-versed in SQL so I cannot figure this out. I have the following tables (I show only the relevant stuff): Documents PK_DocumentID Reviews PK_ReviewID FK_DocumentID FK_ReviewStatusID The relationship is one-to-many: One Document can have many Reviews. A Review has a Status: 1 = Not Started 2 = Open 3 = Closed For ALL the Reviews associated with a Document, there can be only ONE Review that has a non-closed status. Sometimes this condition does exist, which results in a data integrity problem, so I want to identify th...