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
711 Views

Similar Articles

[PageSpeed] 16

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:

Insert query using date as where condition
I have an insert query that runs every day at midnight that records our client's current environmental status (it's for a mental health facility). Here's the code: ----------------------------------------------------------------------------------------------- INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName, FirstName, FileNumber, ClientNumber, StaffID, Environment, Location, Residence, FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp) SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' + dbo.tblClient...

Counting Number Records per Unique ID, saving it to another table:
I have a form frmVendors with a subform frmPayments. The main form shows information on vendors, and the subform is a continuous form which lists the checks paid out to each vendor. Users are typically entering new payments into the subform on the main form. The forms are linked to tables: Linked by VendorID (one to many) tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor City tblPayments: Key=CheckID, VendorID, PaymentAmount There is a field in tblVendor called NumberChecks to show the total amount of checks paid out to that vendor. There is a text box on th...

Writing to web-based database
I am trying to build VBA code that will write to a database locatedona web server. Database is MS Access now, but could use SQL Server orsomething else SQL based. How can I automate the process of opening,appending, and closing a database when the Office application is onthe local computer but the database is on a web server?...

Update Query question 06-26-07
Hello, I am trying to write what I assume would be an update query. I have 2 fields, acct_num and brnch_num in my table. I need to combine these 2 fields into a new field called cust_num. So, if: brnch_num acct_num 123 45678 123 12 12 1 Then I would need the cust_num to read: 12345678, 12300012, 01200001 I'm not sure how to get the zeroes into the cust number so that that number is always 8 diguts. Any help would be greatly appreciated! I wouldn't use an update query for this. Keep the data separat...

Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually, these queries return the correct amount of records in the tables. Which is 80 records in all tables involved. Then I built a query that collects data from the 6 queries and this query returns 75 records. What am I doing wrong? Thanks Don As a guess, your query is not correctly structured for the data you have. For instance, if one of the 6 tables does not have a matching record that could cause you to "lose" a record or two in the query if you have set up a join to that table and have n...

Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces of equipment so we can safely do maintenance. I have an 'edit existing lockout' form, based on a query, that lists in a subform the various points required for a certain piece of equipment. My system used to work, but now I can edit the main, but the subform has locked me out. All I get is a doorbell tone when I attempt to enter info. I can enter the req'd info in the tables, but but my form went snafu. Any ideas? Thanks in advance Does your query allow editing? Check the asterisk in the record...

how do I record times from track meets and find averages
I need toot been able keep track of athletes time and find averages. I have not been able to figure it out. go over to debra's site: http://www.contextures.com/excelfiles.html some files have been provided for download. check what fits you requirement -- Message posted from http://www.ExcelForum.com ...

Need help with SQL Query
Our database consists of over 10,000 items. I'd like to re-organize and format alot of the items we have in our database. For instance, I'd like to do something simple like search all items in the database with a description of "7 1/8" and replace it with "718" and so on. Is there a site or a point of reference to refer to for using basic SQL commands for use with RMS? Another example of where I could use SQL would be for assigning select dept's/cats/items for a certain sale promotion instead of having to goto the properties of each item and manually selecti...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

textbox query
I am using a userform to have data transfered to a sheet. I currently use the code below to send it to a worksheet but what I would like to have happen is for it to add the total of the textbox to the total already in the cell. R = 3 'or whatever desired row variable Sheets(2).Cells(R, 5).Value = TextBox4.Text Any help would be great Thanks in advance Greg With Sheets(2).Cells(R, 5) .Value = .Value + CDbl(TextBox4.Text) End With -- HTH Bob Phillips "Greg B" <laptopgb@ihug.com.au> wrote in message news:d7pjmh$ch3$1@lust.ihug.co.nz... > I am using a userfo...

export query with formulas to excel
Is there a way to export my Access query to Excel 2003 while keeping the formulas in Excel. For example, in my Access query I have a field called "Other" which is defined as the following: Other: [DiffTotal]-[BudTo] [DiffTotal] and [BudTo] are "real" fields in the query and become columns in Excel. Instead of having the value of "Other" appear in Excel, I'd like the "Other" column to have the formula where, for example in row 1, it could look like "=A3-A2." Thanks, ~Mark When Access exports to Excel, it exports the...

#Deleted# records in Form
I've struggled with this before. It's been quite a while since I programmed in Access. When you delete a record from the source of a form, how do you get it to stop showing up as #Deleted#? I've tried Requery.... ?? TIA Ok, Crazy, Here is a function that will delete the current record and make that annoying #Deleted# disappear. If you have a Delete command button on the form, call it like this: DelCurrentRec(Me) I have this function is a Standard Module so I can call it from any form. Public Function DelCurrentRec(ByRef frmSomeForm As Form) Dim rst As Recordset O...

DNS issues, reverse lookup, MX records
Hello, I've done some searching in the groups, but I'd like to pose a specific scenario. We are getting these errors: #4.7.1 smtp;450 4.7.1 Client host rejected: cannot find your reverse hostname, This is the situation. Our domain name is registered with network solutions. Our website is hosted with Yahoo on a business account. Yahoo used to host our email as well. Recently we put in a local SBS 2003 server to host our Exchange locally. Originally our Network solutions account had all DNS pointing to Yahoo's name servers, as it should have. We wanted to switch the MX reco...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

can't record a new macro or insert module in editor
When i try to record a new macro it tells me it is unalbe to record. When i go into the VB editor and go to the insert menu all the items are greyed out so I can't select module and so create a new macor that way. I have changed the security off from high (tried on both medium and low). Does anybody know why I can't make new macros? Douglas Allen Douglas Unfortunately the "Unable to record" message has several potential causes. Suggest you wander through these google search results dealing with this issue. Perhaps something will do the trick. The Personal.xls file ...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

Conditional formatting query #2
Hi, I thought I knew how to do this, but it turns out I don't. If I have names in column A (eg Alan, Bob, Cathy), and values in column B (see below), is there any way I can use conditional formatting to change a cell in A, based on a value in B? A B 1 Alan 4 2 Bob 1 3 Cathy 3 Essentially what I'd like is to highlight the name, if the value is 3 or over. I know how to change the cells in B using conditional formatting, but can't figure out how to change A cells. Thanks, Fiona Select all the cells you want Conditionally formatted. Try this with Formul...

Subquery select field from previous record
I have the following table setup Table Name - tblInventoryCounts Field Names - Store Number, Count Date, Item name, Count Amount Sample Data Store Number Count date Item Name Count Amount 9 1/4/2010 Gear 10 9 1/11/2010 Gear 18 9 1/18/2010 Gear 18 I have a parameter query where the user is asked for the store number and Count Date. I need for the query to return the information for the date entered and the Count Amount from 7 ...

able to record & run macro but not edit !
All Excel97 . When I open a file with an existing macro (or create a new spreadshee and simply record an entry in a cell ) and go to Tools/Macro/Macros th macro list is visible and I can run the macro but if I try to edit it get a Microsoft Visual Basic pop-up 'Unexpected error ; quitting' o if I simply try to open the VBE i get a similar pop-up with ' Out o Memory'. Same files work fine at work (Excel 97) where I edited macros n problem. I've tried uninstalling/reinstalling Excel from the office 97 dis along with data access & Office tools (and their elements) ti...

Double Results in Query
New to Access I am trying to create a query that returns data from 2 tables. one table can have multiple entries on one day per person and the other table will only have one entry per person. 2 tables are DataEntry - will have multiple entries per day per person CallLog - one entry per person per day SELECT DataEntry.OrderTakenBy, DataEntry.Company, DataEntry.Date, DataEntry. Category, DataEntry.ShortCode, DataEntry.ContactName, DataEntry.PositionTitle, DataEntry.PostDateExt, DataEntry.FirmPreview, DataEntry.AllorProgramCodes, DataEntry.Units, DataEntry.value, DataEntry.Mark...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

querying similar databases plan
I'm working with hideously unnormalized databases, most of which have similar structures. (Of course, if they had the same structures, it would be too easy.) I work at a company that does cancer research, so they collect information about their patients over a series of visits. The questions they ask (queries) for parts of the final report are standard across all databases, and then some are specific to a the type of study they're doing. What is the best way to approach this, given that I have to summarize like 20 databases in maybe two weeks, and the column names are not necessari...

Query Needed 01-04-10
A sample of my data table looks as so: Underlying ID StrikePrice AAPL APVA10C155.00 155 AAPL APVM10P155.00 155 AAPL APVA10C160.00 160 AAPL APVM10P160.00 160 AAPL APVA10C165.00 165 AAPL APVM10P165.00 165 AAPL APVD10C160.00 160 AAPL APVP10P160.00 160 AAPL APVD10C165.00 165 AAPL APVP10P165.00 165 AAPL APVD10C155.00 155 AAPL APVP10P155.00 155 AAPL APVB10C155.00 155 AAPL APVN10P155.00 155 AAPL APVB10C160.00 160 AAPL APVN10P160.00 160 AAPL APVB10C165.00 165 AAPL APVN10P165.00 165 GS GPYA10C155.00 155 GS GPYM10P155.00 155 GS GPYA10C160.00 160 GS GPYM10P160.00 160 ...

Query in Excell
I would like to create bolean statements in one or more of the columns that result from a query. Basically, I want to test a column for a factor and return a simple 'true' or 'false'. Using this I, hopefully, will eliminate the need to pass the data to Excel and perform the function there. ...

MS Query
I want to replicate an ODBC report for a number of different countrie on different databases. Currently I have to create a new file and query for each differen database. Is it possible to change which database a query points to withou re-writing it completely, as this would be much faster. The databases appear to have identical attributes (tables, fields etc.) Many thanks -- Message posted from http://www.ExcelForum.com usually all you have to do is change the file path. what method are you using. how is the query pointing now. >-----Original Message----- >I want to replicate an...