Totals Query Help!

Hi,  I have a table with the following fields:

Manufacturer
Model
Value
SoldMonth

What I'm trying to do is write a query that will give me an average Value 
for each month, but I want the average to be based only on records where the 
Model is consistent across all months.  So if Model x is missing from one or 
more months all records for that model would be excluded from all the 
overall Monthly averages. So the query will only average records where the 
Model is present in each month.

Any help with this would be greatly appreciated.

Regards.....Jason 


0
Jay
8/1/2007 1:03:05 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
498 Views

Similar Articles

[PageSpeed] 35

It'll help us if you can post some sample data, and show us what type of 
result you want to see.

-- 

        Ken Snell
<MS ACCESS MVP>


"Jay" <dummy@dummy.dummy> wrote in message 
news:OkS9NxD1HHA.1212@TK2MSFTNGP05.phx.gbl...
> Hi,  I have a table with the following fields:
>
> Manufacturer
> Model
> Value
> SoldMonth
>
> What I'm trying to do is write a query that will give me an average Value 
> for each month, but I want the average to be based only on records where 
> the Model is consistent across all months.  So if Model x is missing from 
> one or more months all records for that model would be excluded from all 
> the overall Monthly averages. So the query will only average records where 
> the Model is present in each month.
>
> Any help with this would be greatly appreciated.
>
> Regards.....Jason
> 


0
Ken
8/1/2007 2:41:35 PM
First of all what are your field types?  Is month a text field or a date 
field?  How many months are you talking about?  What is stored in Month?

Generically the idea would be something like the following.
Query1:
SELECT DISTINCT Manufacturer, Model, Month
FROM TableA

Query2:
SELECT Manufacturer, Model
FROM Query1
WHERE Month Between 1 and 12
GROUP BY Manufacturer, Model
HAVING Count(Month) = 12

Query3:
SELECT TableA.Manufacturer
, TableA.Model
, TableA.Month
, Avg(Value) as Average
FROM TableA INNER JOIN Query2
ON TableA.Manufacturer = Query2.Manufacturer
AND TableA.Model = Query2.Model
GROUP BY TableA.Manufacturer, TableA.Model

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

"Jay" <dummy@dummy.dummy> wrote in message 
news:OkS9NxD1HHA.1212@TK2MSFTNGP05.phx.gbl...
> Hi,  I have a table with the following fields:
>
> Manufacturer
> Model
> Value
> SoldMonth
>
> What I'm trying to do is write a query that will give me an average Value 
> for each month, but I want the average to be based only on records where 
> the Model is consistent across all months.  So if Model x is missing from 
> one or more months all records for that model would be excluded from all 
> the overall Monthly averages. So the query will only average records where 
> the Model is present in each month.
>
> Any help with this would be greatly appreciated.
>
> Regards.....Jason
> 


0
John
8/1/2007 3:02:25 PM
Hi, My field types are all text, other than Value, which is a number.  Some 
of my data may look like:

Ford    Mondeo    5750    Jun-06
Ford    Fiesta        6000    Jul-07

I'm after just a list of averages per month (with the condition that the 
averages are based on only records where the model is in each month at least 
once).

Many thanks,

Jason

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:%23lUqpzE1HHA.4880@TK2MSFTNGP03.phx.gbl...
> First of all what are your field types?  Is month a text field or a date 
> field?  How many months are you talking about?  What is stored in Month?
>
> Generically the idea would be something like the following.
> Query1:
> SELECT DISTINCT Manufacturer, Model, Month
> FROM TableA
>
> Query2:
> SELECT Manufacturer, Model
> FROM Query1
> WHERE Month Between 1 and 12
> GROUP BY Manufacturer, Model
> HAVING Count(Month) = 12
>
> Query3:
> SELECT TableA.Manufacturer
> , TableA.Model
> , TableA.Month
> , Avg(Value) as Average
> FROM TableA INNER JOIN Query2
> ON TableA.Manufacturer = Query2.Manufacturer
> AND TableA.Model = Query2.Model
> GROUP BY TableA.Manufacturer, TableA.Model
>
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Jay" <dummy@dummy.dummy> wrote in message 
> news:OkS9NxD1HHA.1212@TK2MSFTNGP05.phx.gbl...
>> Hi,  I have a table with the following fields:
>>
>> Manufacturer
>> Model
>> Value
>> SoldMonth
>>
>> What I'm trying to do is write a query that will give me an average Value 
>> for each month, but I want the average to be based only on records where 
>> the Model is consistent across all months.  So if Model x is missing from 
>> one or more months all records for that model would be excluded from all 
>> the overall Monthly averages. So the query will only average records 
>> where the Model is present in each month.
>>
>> Any help with this would be greatly appreciated.
>>
>> Regards.....Jason
>>
>
> 


0
Jay
8/1/2007 3:34:14 PM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

IWAN & IUSR bei Crystal Reports? *help*
Hi NG, my problem is that i can see the crystal reports, but when i open one i get "more information is needed". But till yesterday i could open reports!! Now after some search i see that i have no IUSR_servane and no IWAM_servername. how could this happen? My system is AD, SQL, CRM and each of them is one a seperate 2003 server. Please let me know if you have any suggestions. Regards Nicolas F�hrs sound strange with the IWAN and IUSR. I offten have this problem. There are a techknowledge article with 13 resoluti...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Mortgage Payment Split Doesnt Equal Total
Money Plus Premium Hello, I used the loan wizard to set up my mortgage when I bought my house four years ago. For a long time, everything worked as it was supposed to. Now, when I go to pay the bill each month, although the amount is correct, when I try to enter the transaction, I receive the following message: "The sum of the splits does not match the total transaction amount. Click OK to adjust the splits." When I go into the split box, the interest portion is correct, but the principal is way too large. Though at this stage of my mortgage, the interest should complet...

Help With Strange Error When Trying to Save Any Record
I am getting astrane error happens when try to create any new record (Customer, Item, Vendor or Account), the system gives me a message that "Save Operation Failed" and when i click "More Info" the error message is "Could not find stored procedure 'DYNAMICS.dbo.aagGetCompanyStatus'", although i have created alot of items, customers, accounts and vendors before but suddenly this error appeared. Any quick help will be highly appreciated. It would appear that the AAG has something to do with a product produced by American Association of Geographers. I...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

help with preview pane and "read receipts"
Hi, I'm new to Outlook administration. We run Outlook on a server wit Exchange. Is it possible to setup Outlook on client PC's so that th preview pane cannot be activated by individual users? Also, can the blocking of "read receipts" by individual PC users b prohibited within Outlook, at the server level? If not, is there an other way to do it? My reasoning for wanting to do the above two things is to make th "read receipts" function work more effectively. Thanks for your suggestions. : ----------------------------------------------- ~~ Message posted from h...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help with Do...Loop
Hi I need a check to be done to see if column a has a number in it then to check if column b has a number. If column B doesn't have a number then I need it to stop and give a msgbox, When column A doesn't have data then I need the loop to stop as we dont have to check column b The code below is what I have but when I try to run it it keeps saying LOOP WITHOUT DO. I hope someone can help me as I am not very good with loops. mykeycode = Range("B32") mysell = Range("N32") Do mykeycode = mykeycode + 1 mysell = ...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Crosstab Query 04-06-07
I have a crosstab query that shows the products i sale with the number i have sold for each day. instead of showing the sales for each day i would like to show sales dor each months. How do i show it my month Thanks In query design view, enter this into a fresh column in the Field row: TheYear: Year([SaleDate]) Replace SaleDate with your actual field name. In the next column: TheMonth: Month([SaleDate]) You can now group on these fields instead of on each date. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html ...

How to display Total Item Quantity on receipt and Status bar
I would like to print a total Quantity of items purchased on receipt and the status bar. I found a way of displaying them but the problem is that we use tag along items for alcohol tax . This tax differs based on alcohol content and size, example a 6 pk of beer has $0.20 tax plus sales tax, a 750 Ml a bottle of wine has $0.116 tax . If i ring up a 6pk or beer this would show up as 2 lines(line one name and price for beer , line two alcohol tax tag along item) on POS screen and the Total items and total QTY count shows as 2 when in reality only 1 item was sold. This gets even more conf...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Help Please - A bit of a challenge
Hello everyone~ I had had some trouble on a project I have been working on for some time. I have tried numerous approaches, each with undesired results. (VAB Code) The project I am working on has become quite complex, so rather than bother you with my spaghetti code, I have made a simple example of what I am trying to do. (Attached to this Message) The sheet tracks People and how many fruits they had each day. Day1 is where the information is Inputted, Day2 will double the DAY1 numbers, and Day three will triple the DAY2 Numbers. The challenge I am faced with: On DAY1 ...

Help- messages stuck in Outbox
Hello there. Outlook Xp client with all service packs connecting to an exchange 2003 server. This person in my network has rights to another mailbox (which he accesses through her folder list)and when she attempts to forward 2 particular emails with pdf attachments to another user (who also has rights to the mailbox....not that it should matter) the emails get stuck in her Outbox. She can forward those same emails to me without any problems. Also, when she sends emails without attachments to that person they go through. i haven't tried anything else at this point. The person wouldn&...