Grouping data

I am trying to group data for a specific event and have not been successful, 
so I am hoping someone can help.

ID           Date                   Value
174         12/3/2007             102
174         12/3/2007             909
174         12/11/2007           405
174         12/11/2007           701
206         12/6/2007           1001
173         12/3/2007             500

I want to group by ID, the MAX Date for that ID and the Max Value for the ID 
& Max Date

The desired result would be:

ID           Date                   Value
174         12/11/2007           701
206         12/6/2007           1001
173         12/3/2007             500

When I use GROUP and MAX the Value does not stay specific to an ID, I get 
the MAX Value for ID, but not the Date and ID.

Not this

ID           Date                   Value
174         12/11/2007           909
206         12/6/2007           1001
173         12/3/2007             500

I would appreciate any help.

Thanks

0
Utf
12/4/2007 12:40:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1303 Views

Similar Articles

[PageSpeed] 47

See:
    Getting a related field from a GroupBy (total) query
at:
    http://www.mvps.org/access/queries/qry0020.htm

If you have never worked with subqueries before, this may help also:
    Subquery basics
at:
    http://allenbrowne.com/subquery-01.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:B9D30F52-D0E9-42F3-B274-6A442BAFFF1B@microsoft.com...
>I am trying to group data for a specific event and have not been 
>successful,
> so I am hoping someone can help.
>
> ID           Date                   Value
> 174         12/3/2007             102
> 174         12/3/2007             909
> 174         12/11/2007           405
> 174         12/11/2007           701
> 206         12/6/2007           1001
> 173         12/3/2007             500
>
> I want to group by ID, the MAX Date for that ID and the Max Value for the 
> ID
> & Max Date
>
> The desired result would be:
>
> ID           Date                   Value
> 174         12/11/2007           701
> 206         12/6/2007           1001
> 173         12/3/2007             500
>
> When I use GROUP and MAX the Value does not stay specific to an ID, I get
> the MAX Value for ID, but not the Date and ID.
>
> Not this
>
> ID           Date                   Value
> 174         12/11/2007           909
> 206         12/6/2007           1001
> 173         12/3/2007             500
>
> I would appreciate any help.
>
> Thanks
> 

0
Allen
12/4/2007 1:40:06 PM
Thanks for the quick response.  I have used the queires from the example page 
(http://www.mvps.org/access/queries/qry0020.htm) and substituted the following

BookID = PopID
BookDate = Date
BorrowerID = Value

I get multiple BookIDs listed in the result where I am looking for a single 
BookID each time.

Make sense?

Dave

"Allen Browne" wrote:

> See:
>     Getting a related field from a GroupBy (total) query
> at:
>     http://www.mvps.org/access/queries/qry0020.htm
> 
> If you have never worked with subqueries before, this may help also:
>     Subquery basics
> at:
>     http://allenbrowne.com/subquery-01.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:B9D30F52-D0E9-42F3-B274-6A442BAFFF1B@microsoft.com...
> >I am trying to group data for a specific event and have not been 
> >successful,
> > so I am hoping someone can help.
> >
> > ID           Date                   Value
> > 174         12/3/2007             102
> > 174         12/3/2007             909
> > 174         12/11/2007           405
> > 174         12/11/2007           701
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > I want to group by ID, the MAX Date for that ID and the Max Value for the 
> > ID
> > & Max Date
> >
> > The desired result would be:
> >
> > ID           Date                   Value
> > 174         12/11/2007           701
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > When I use GROUP and MAX the Value does not stay specific to an ID, I get
> > the MAX Value for ID, but not the Date and ID.
> >
> > Not this
> >
> > ID           Date                   Value
> > 174         12/11/2007           909
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > I would appreciate any help.
> >
> > Thanks
> > 
> 
> 
0
Utf
12/4/2007 1:54:02 PM
Got it!

I used a variation of an example on that page.

Thanks

Dave

"Allen Browne" wrote:

> See:
>     Getting a related field from a GroupBy (total) query
> at:
>     http://www.mvps.org/access/queries/qry0020.htm
> 
> If you have never worked with subqueries before, this may help also:
>     Subquery basics
> at:
>     http://allenbrowne.com/subquery-01.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:B9D30F52-D0E9-42F3-B274-6A442BAFFF1B@microsoft.com...
> >I am trying to group data for a specific event and have not been 
> >successful,
> > so I am hoping someone can help.
> >
> > ID           Date                   Value
> > 174         12/3/2007             102
> > 174         12/3/2007             909
> > 174         12/11/2007           405
> > 174         12/11/2007           701
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > I want to group by ID, the MAX Date for that ID and the Max Value for the 
> > ID
> > & Max Date
> >
> > The desired result would be:
> >
> > ID           Date                   Value
> > 174         12/11/2007           701
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > When I use GROUP and MAX the Value does not stay specific to an ID, I get
> > the MAX Value for ID, but not the Date and ID.
> >
> > Not this
> >
> > ID           Date                   Value
> > 174         12/11/2007           909
> > 206         12/6/2007           1001
> > 173         12/3/2007             500
> >
> > I would appreciate any help.
> >
> > Thanks
> > 
> 
> 
0
Utf
12/4/2007 2:11:06 PM
Reply:

Similar Artilces:

Grouping data
I am trying to group data for a specific event and have not been successful, so I am hoping someone can help. ID Date Value 174 12/3/2007 102 174 12/3/2007 909 174 12/11/2007 405 174 12/11/2007 701 206 12/6/2007 1001 173 12/3/2007 500 I want to group by ID, the MAX Date for that ID and the Max Value for the ID & Max Date The desired result would be: ID Date Value 174 12/11/2007 701 206 12/6/...

Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page of a group -- either hide the page footer on pages if it is not the last page of the group or have the group footer print at the bottom of the page. I have used the instructions to reset the page number and the total page count for each group. (http://support.microsoft.com/kb/841779/en-us) I was hoping to be able to set it where if the page equals the total pages of the group, it will make the page footer visible. For some reason, if there is more than one page, it doesn't work properly. I have also used the i...

Excel Truncating Leading Spaces when pasting data
Is there a way for Excel to not truncate leading spaces when data from outside of Excel has them. For example, I have a text file where some data has 1 leading zero, and some has 2, etc. I want to preserve that format when pasting to Excel, but they get truncated off. Is there a setting or something I can use to keep them. Excel XP, Office XP, Windows XP Thanks Hi Wayne Format the cells as text before you copy the data -- Regards Ron de Bruin http://www.rondebruin.nl "Wayne" <anonymous@discussions.microsoft.com> wrote in message news:260001c427dc$0c643a40$a10128...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

Creating Universal Groups with Distribution Group members?
Hello all. We have a Windows 2003 Active Directory with Exchange 2003, and I don't really want to duplicate work, but I think I may have to (ugh!). Here is the deal. I need to create an All Managers Universal Group with every group that has managers in it. So, what I tried doing, was I created a Universal Group and added the _All Managers DL into it -- but that didn't seem to work, according to my boss who asked me to create this UG for him. So, my question is, am I not allowed to add DL members to a Universal Security Group? And if now, is there any way that I can take...

How to number rows after data has been filtered?
I want to know continously give a serial number skipping in between some rows that are filtered. In excel if we try Fill series, it is not working on filtered rows. Is there any way out? Please help. I think I'd sort the data to group those visible cells. Then fill those empty cells and resort the data to its original order. I'd have a column that was essentially a row number column. show all the rows first. Insert a new column A. put 1 in A2 and 2 in A3 select a2:a3 and drag down your data. Filter your data to see the blanks you want to fill. select that range of visible cell...

Reinstalling CRM 4.0 but preserving data
We are having terrible trouble having upgraded from CRM 3.0 to 4.0. We are trying to use the Workflows to manage tasks etc., but without fail the Workflow goes into a "Waiting for Resources" state and never does anything. We've looked at all the obvious candidates (the Async Service, for example) and have tried the various fixes (more information here; http://www.themssforum.com/Crm/Workflows-Waiting/) We're now at the stage of considering reinstalling CRM 4.0. However, we have migrated a large amount of data from our CRM 3.0 installation. Can we safely backup the existi...

Distribution Group list
I have a problem with the distribution group list. Here it goes: I removed and deleted the account from this group list but whenever I send a task, it always tries to reach this deleted account and give me a undeliverable error message. Please help. Thanks Restart Exchange services. Linh wrote: > I have a problem with the distribution group list. Here > it goes: I removed and deleted the account from this > group list but whenever I send a task, it always tries to > reach this deleted account and give me a undeliverable > error message. Please help. Thanks ...

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

Live incoming data
Hi,Could someone please tell me how to "Capture Live Incoming Data ",so that I can make charts from this data. Walkenbach's books dont get into it.Thanks a lot.I'm stuck. You really need to provide more information: Where is the data comming from Where are you pitting it how often is it updated where do you want it displayed "peter z" wrote: > Hi,Could someone please tell me how to "Capture Live Incoming Data ",so > that I can make charts from this data. > Walkenbach's books dont get into it.Thanks a lot.I'm stuck. > > ...

Local Data Groups
Hi, Does anyone know if its possible to create local data groups for offline clients and somehow push the groups out to all users, rather than going to each outlook laptop client? Jo Savidge You can deploy the SFO client using group policies. When the user next logs onto the domain the SFO client will be installed ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm http://www.crowechizek.com/microsoft "Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...

graphing by group
I have 10 individuals in 5 groups. I would like to graph the individuals but keeping them within their groups. When I try to graph it ends up as 10 groups. I would also like to make each group of individuals a different colour. Thanks, Tim Hi Tim In Step 2 of Chart Wizard make sure you have Series by Rows or Series by Columns correctly selected. If you groups are in different columns us Series by Columns other wise use Series by Rows -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Tim Pasma" <anonymous@discussions.microsoft.com> wrote in m...

Passing option group value to vba function
I'm sure I've done this before but can't for the life of me figure it out now, and hours of research online haven't helped. I'm simply trying to pass an option group value from a form to a vba function. I'd like the user to choose option1 or option2 and when they click a button, the function will run an if statement like "if option1.value=true then...." I've got 2 options named option1 and option2 and a frame called frame20, what else do I need? I've also tried bounding the selection to a textbox, but keep getting errors. Any help on the process? Tha...

Show Percent on Data Labels
In Excel 2000, I've created a non-stacked vertical bar chart. I would like to show percent of total on the data labels of each bar while retaining the actual data value on the Y-axis scale. Can this be done? Under the Format Data Series/Data Labels tab, the "Show percent" option is grayed out. Thanks for any assistance you can provide. "Paul Scheer" <paul.scheer@warnerbros.com> schrieb im Newsbeitrag news:3c5d01c3762c$f6aa9980$a301280a@phx.gbl... > In Excel 2000, I've created a non-stacked vertical bar > chart. I would like to show percent of...

managing distribution groups
Hello all, I am running exchange 2003 and I would like to know is there a way and I can have a regular user maintain my distribtion groups. If so how would I go about that? Thanks so much Mike You can grant permissions at the disty group level to allow ordinary members to add and remove users. Be careful to only do disty groups as security groups changed by users in outlook is a touch insecure. "Mike B" <MikeB@discussions.microsoft.com> wrote in message news:0F2F3709-0900-46B1-B87B-A2449FD44B75@microsoft.com... > Hello all, > > I am running exchange 2003 and...

cancel event if data change
Hi there, I have a main form with a sub-form and code at the before update event on the date field under the main form, I would like to bypass/cancel the before update event if the user change the date after entry. I mean that when the user enter the date (new record), the before update event wil be executed. If the user change the date (immediately or after record saved), I would lite to bypass/cancel the before update event. Hope this clear for you. Appreciate for your expert comments. Thanks in advance! On Nov 29, 8:06 am, Andy <A...@discussions.microsoft.com> wrote: > Hi th...

DSum by group
I have a textbox with a DSum function, but would like it to query only within each group. It seems that most other functions (e.g Avg) automatically calculate by group, but DSum doesn't seem to be. =DSum([MKTCAP],"QryFILTER",[MKTCAP]>100) The text box is within the Group Footer, and I'd like it to return a different value below each group. Thanks! "RussG" <RussG@discussions.microsoft.com> wrote in message news:697878DC-521A-4423-BB7F-97089D536EF2@microsoft.com... >I have a textbox with a DSum function, but would like it to query only >within...

Re: Protecting worksheet with grouped cells
I have a worksheet which has rows as well as cloumns grouped. After protecting the worksheet I am unable to expand and collaspe my groups. error:"You cannot use this command on a protected sheet..." Is there a work-around/solution to this problem? You can only enable outlines and have worksheet protection through a macro: With Worksheets("Sheet1") .EnableOutlining = True .Protect UserInterfaceOnly:=True End With The EnableOutlining property (and UserInterfaceOnly) is not saved with the workbook unfortunately, so this code would have to be run ...

Inputing Data
Is there an easier way to input data into cells. i.e. instead o actually typing in the numbers 100, 150, 150, 55, 40, in cells A2:A6 is there a voice program or can I scan a document with the numbers an copy the data into the cells? Trying to save time if I can. Thank -- Message posted from http://www.ExcelForum.com Some (most nowadays???) scanner software can create xl files on the fly. (If the scanner creates a text file, it wouldn't be too difficult to open in excel.) There are voice recognition programs. (I've never used them). You can google for "voice recognitio...

Data\Validation
I have a cell in excel that has a Validation list, and the list is a sum of other cells so the values change based on what is entered in other cells. my question is, is it possible to have the cell update by itself. Example I have completed entering data in the cells and then I go and select an option from the validation list then I go back and change some data that I entered originally, at this point is it possible that the validation option that I had selected can be updated automatically instead of me going back and selecting it again. Thanks. You may be able to update the selections with ...

how to prevent Excel from reformatting a chart wen updating data?
I have data over time for different categories. when i change de reporting time frame i want the chart colors to stay the same. Or when i change the scope in categories i still want the categorie chart to keep its color. How to stop excel from reformatting the colors? Hi, I am assuming you are referring to Pivot charts in which case this is a know problem. If it's not a pivot chart then post back. Changing a PivotChart removes series formatting in Excel http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 Cheers Andy Cmd_Marco wrote: > I have data over time for differ...

Grouping and grouping within groups
I really need help, and help fast, so thank you ahead of time! I nee to know how to group and group within a group. I have been able to d a group, but once I group within the group and try to group anothe group withing that group it combines them. I know I can put a spac between the information so it doesn't combine them but that doesn' seem to be the right way to do things. For instance I have thre different area codes and I want to group them, then within those group I have five different colors of houses in that area code. I want t group the different colors within their area ...

Show Groups as Page Header
I used the report wizard to create a report based on a table. The table has 252 records. When I preview the report, it only shows 242 records. The records are sorted / grouped by district. In the table, the fields are District Business Name DBA Name Amount The wizard placed the District and field names into the District Name Header. To make the report more readable, how can I get the group headers to be the page headers? That is, if a group prints over more than one page, how can I get the page header to show the group name? You can set the "Repeat Section" property of the Gro...

Filtering Data #2
Hi guys, I have a problem that I was hoping you could help me with. I hav excell files that are 100 rows down with floating point number (ranging from -500 to 2000) across (see below for an example). What I need to do is to pull out all the values 0<x<500 keeping th format of the rows intact. Any help would be greaty appreciated Lodgey ------------------------------------------------------------------------------------ Example. 0 -383.5 -81.1 96.5 135.5 1381 1 -472.8 126.9 155.8 1135.2 2 -129.2 69.9 119.8 224.9 319.4 874.8 3 -56.8 61.1 102.1 234.3 292.1 867.6 4 -469.1 -442.6 5...

E2K3 Storage Groups
What are advantages, and more importantly, disadvantages to using multiple Storage Groups. I have heard that having more then 2 SG's creates extra CPU load, is this true? The only benefit I see so far is logical separation for use in exchange manager, and that you can point translogs and system path to different locations, if you had different physical drives, I could see this as a benefit. I have a situation where I'd like to do the following -Storage Group for local users - Multiple stores with different store policies -Storage Group for remote users - Multiple stores with diff...