Group By Query - find which rows are being grouped

Hello. I need a little help. Is there a way to find out which records were 
grouped in a group by query, and maybe flag that record with a specific tag

for example in this scenario:

ID       Amount     Name
1         123          First
2         123          First
3         123          F-irst
4         345          Second
5         345          Second
6         345          Secon-d
.......
.......



If I put this table in a group by query and Sum the amounts, I take the 
LastOfName and LastOfID then I get something like this:


ID       Amount     Name
3         369          F-irst
6         1035        Secon-d

I would like to be able to know which records were grouped maybe "mark" them 
for the record? Something like:

ID       Amount     Name     Flag
1         123          First       G1
2         123          First       G1
3         123          F-irst      G1
4         345          Second   G2
5         345          Second   G2
6         345          Secon-d  G2

Where G1 is Group 1, and G2 is group 2. I would then know based on G1 which 
records "can" be grouped

I am not sure if this makes sense, but I need to know what records can be 
grouped for a specific table, and just mark them somehow. In the end I need 
to show those records in a report. For each set of groups, there will be a 
matching record already grouped (this is output by a system) and they will be 
compared to make sure they match.

So when the report shows let's say G1 like this:

ID       Amount     Name     Flag
1         123          First       G1
2         123          First       G1
3         123          F-irst      G1

a user will already have a record that looks like this:

ID            Amount     Name
123AC         369           F-ir-st

They will then look at similiarities based on partial Name, Amount and 
partial ID to decide wheather the Amount is the same and the record is the 
same
So they'll say, oh OK, my ID of 123AC contains 1, 2 and 3, already showing 
above, the amount also matches, and the name is not exaclty the same, but 
similiar, so they can be looked at being the same. This is the business rule. 
I just have to provide the flagging


0
Utf
8/1/2007 9:30:03 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
734 Views

Similar Articles

[PageSpeed] 12

You can not do it the way you expressed it as it will not group on two 
different spellings -  First & F-irst    OR   Second &  Secon-d

-- 
KARL DEWEY
Build a little - Test a little


"Leo" wrote:

> Hello. I need a little help. Is there a way to find out which records were 
> grouped in a group by query, and maybe flag that record with a specific tag
> 
> for example in this scenario:
> 
> ID       Amount     Name
> 1         123          First
> 2         123          First
> 3         123          F-irst
> 4         345          Second
> 5         345          Second
> 6         345          Secon-d
> ......
> ......
> 
> 
> 
> If I put this table in a group by query and Sum the amounts, I take the 
> LastOfName and LastOfID then I get something like this:
> 
> 
> ID       Amount     Name
> 3         369          F-irst
> 6         1035        Secon-d
> 
> I would like to be able to know which records were grouped maybe "mark" them 
> for the record? Something like:
> 
> ID       Amount     Name     Flag
> 1         123          First       G1
> 2         123          First       G1
> 3         123          F-irst      G1
> 4         345          Second   G2
> 5         345          Second   G2
> 6         345          Secon-d  G2
> 
> Where G1 is Group 1, and G2 is group 2. I would then know based on G1 which 
> records "can" be grouped
> 
> I am not sure if this makes sense, but I need to know what records can be 
> grouped for a specific table, and just mark them somehow. In the end I need 
> to show those records in a report. For each set of groups, there will be a 
> matching record already grouped (this is output by a system) and they will be 
> compared to make sure they match.
> 
> So when the report shows let's say G1 like this:
> 
> ID       Amount     Name     Flag
> 1         123          First       G1
> 2         123          First       G1
> 3         123          F-irst      G1
> 
> a user will already have a record that looks like this:
> 
> ID            Amount     Name
> 123AC         369           F-ir-st
> 
> They will then look at similiarities based on partial Name, Amount and 
> partial ID to decide wheather the Amount is the same and the record is the 
> same
> So they'll say, oh OK, my ID of 123AC contains 1, 2 and 3, already showing 
> above, the amount also matches, and the name is not exaclty the same, but 
> similiar, so they can be looked at being the same. This is the business rule. 
> I just have to provide the flagging
> 
> 
0
Utf
8/1/2007 10:00:03 PM
My mistake. Name and amount are always the same. It's the IDs that are 
different. 
-- 
Leo 


"KARL DEWEY" wrote:

> You can not do it the way you expressed it as it will not group on two 
> different spellings -  First & F-irst    OR   Second &  Secon-d
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Leo" wrote:
> 
> > Hello. I need a little help. Is there a way to find out which records were 
> > grouped in a group by query, and maybe flag that record with a specific tag
> > 
> > for example in this scenario:
> > 
> > ID       Amount     Name
> > 1         123          First
> > 2         123          First
> > 3         123          First
> > 4         345          Second
> > 5         345          Second
> > 6         345          Second
> > ......
> > ......
> > 
> > 
> > 
> > If I put this table in a group by query and Sum the amounts, I take the 
> > LastOfName and LastOfID then I get something like this:
> > 
> > 
> > ID       Amount     Name
> > 3         369          F-irst
> > 6         1035        Secon-d
> > 
> > I would like to be able to know which records were grouped maybe "mark" them 
> > for the record? Something like:
> > 
> > ID       Amount     Name     Flag
> > 1         123          First       G1
> > 2         123          First       G1
> > 3         123          F-irst      G1
> > 4         345          Second   G2
> > 5         345          Second   G2
> > 6         345          Secon-d  G2
> > 
> > Where G1 is Group 1, and G2 is group 2. I would then know based on G1 which 
> > records "can" be grouped
> > 
> > I am not sure if this makes sense, but I need to know what records can be 
> > grouped for a specific table, and just mark them somehow. In the end I need 
> > to show those records in a report. For each set of groups, there will be a 
> > matching record already grouped (this is output by a system) and they will be 
> > compared to make sure they match.
> > 
> > So when the report shows let's say G1 like this:
> > 
> > ID       Amount     Name     Flag
> > 1         123          First       G1
> > 2         123          First       G1
> > 3         123          F-irst      G1
> > 
> > a user will already have a record that looks like this:
> > 
> > ID            Amount     Name
> > 123AC         369           F-ir-st
> > 
> > They will then look at similiarities based on partial Name, Amount and 
> > partial ID to decide wheather the Amount is the same and the record is the 
> > same
> > So they'll say, oh OK, my ID of 123AC contains 1, 2 and 3, already showing 
> > above, the amount also matches, and the name is not exaclty the same, but 
> > similiar, so they can be looked at being the same. This is the business rule. 
> > I just have to provide the flagging
> > 
> > 
0
Utf
8/2/2007 12:56:09 PM
Use a totals query to group the information.  Make it an append query.
You can append the data into a temporary table, but which will have an 
autonumber field in addition to the other fields you have data. That 
autonumber field will get the values 1, 2, 3, ... as records are appended to 
it, even if you do it in just one batch.

Then using the temp table joined to your data table update a group field 
from the autonumber field of the temp table.

-- 
KARL DEWEY
Build a little - Test a little


"Leo" wrote:

> My mistake. Name and amount are always the same. It's the IDs that are 
> different. 
> -- 
> Leo 
> 
> 
> "KARL DEWEY" wrote:
> 
> > You can not do it the way you expressed it as it will not group on two 
> > different spellings -  First & F-irst    OR   Second &  Secon-d
> > 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Leo" wrote:
> > 
> > > Hello. I need a little help. Is there a way to find out which records were 
> > > grouped in a group by query, and maybe flag that record with a specific tag
> > > 
> > > for example in this scenario:
> > > 
> > > ID       Amount     Name
> > > 1         123          First
> > > 2         123          First
> > > 3         123          First
> > > 4         345          Second
> > > 5         345          Second
> > > 6         345          Second
> > > ......
> > > ......
> > > 
> > > 
> > > 
> > > If I put this table in a group by query and Sum the amounts, I take the 
> > > LastOfName and LastOfID then I get something like this:
> > > 
> > > 
> > > ID       Amount     Name
> > > 3         369          F-irst
> > > 6         1035        Secon-d
> > > 
> > > I would like to be able to know which records were grouped maybe "mark" them 
> > > for the record? Something like:
> > > 
> > > ID       Amount     Name     Flag
> > > 1         123          First       G1
> > > 2         123          First       G1
> > > 3         123          F-irst      G1
> > > 4         345          Second   G2
> > > 5         345          Second   G2
> > > 6         345          Secon-d  G2
> > > 
> > > Where G1 is Group 1, and G2 is group 2. I would then know based on G1 which 
> > > records "can" be grouped
> > > 
> > > I am not sure if this makes sense, but I need to know what records can be 
> > > grouped for a specific table, and just mark them somehow. In the end I need 
> > > to show those records in a report. For each set of groups, there will be a 
> > > matching record already grouped (this is output by a system) and they will be 
> > > compared to make sure they match.
> > > 
> > > So when the report shows let's say G1 like this:
> > > 
> > > ID       Amount     Name     Flag
> > > 1         123          First       G1
> > > 2         123          First       G1
> > > 3         123          F-irst      G1
> > > 
> > > a user will already have a record that looks like this:
> > > 
> > > ID            Amount     Name
> > > 123AC         369           F-ir-st
> > > 
> > > They will then look at similiarities based on partial Name, Amount and 
> > > partial ID to decide wheather the Amount is the same and the record is the 
> > > same
> > > So they'll say, oh OK, my ID of 123AC contains 1, 2 and 3, already showing 
> > > above, the amount also matches, and the name is not exaclty the same, but 
> > > similiar, so they can be looked at being the same. This is the business rule. 
> > > I just have to provide the flagging
> > > 
> > > 
0
Utf
8/2/2007 2:46:02 PM
Reply:

Similar Artilces:

how to find on screen amimated character
how do u get an animated screen character to come up on ur screen? i have windows vista Hello LFGuard395, I'm sorry there are no animated screen characters in the Microsoft Project software, so I am guessing that you are not discussing Project? I suggest looking again at the software program you are trying to view the animated screen character in (Word?, Excel?) and then reposting your question to the appropriate newsgroup. See the listing at: http://social.answers.microsoft.com/Forums/en-US/group/Office I hope this helps. Let us know how you get along. Julie Pr...

how to delete rows #2
I have a work sheet that has many rows like this and 3 coloums 1 chair custom 24 2 table custom 0 3 spoon custom 3563 4 hat custom 0 now I would like to make another colom and only extract those that their sum is greather than zero. in other words I would like to delete rows 2 and 4 how can i do that using if-function or any other function tnx khers You can use Auto Filter ,First select all then From menu choose Data - Filter - Auto Filter , Now you can select 0 Value from drop down o...

help with SQL query for HQ
Hello! Need two queries. 1. to clear the bin location field for all items 2. to assign a numberical value for the bin location field for each of our stores. ex. store 1 bin location field to read 1, store store bin location field to read 2 any help would as always be greatly appreciated... thank you hi zcsf, 1. To Clear bin location Run this UPDATE ITEM SET BINLOCATION='' 2. To Update the bin location with store id UPDATE ITEM SET BINLOCATION=storeid from Configuration Rate please. "ZCSF" wrote: > Hello! > Need two queries. > 1. to clear the bin location field ...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Can't get Word Mail Merge to find my Excel file of names and addre
I can't get my excel file to show up in the Select Data Source window. I clicked on "New Source" then selected ODBC DSN, selected Excel File, and got a Select Database and Table screen with no options for me, except ack and Cancel. I'm sure I'm doing something wrong, can anyone help? Thanks. Marilyn Collins Did you navigate [using the Directories list] to the folder where your Excel file is stored? Double-Click the folders following the path to that directory. Once you get to a folder containing Excel files they will appear in the left list of Database Nam...

Group policy 2k8:an old software (test) package is causing GPO err
At one point in time, forwhatever reason, there was a package "tested" in the default domain policy.. (which also meant it got pushed to servers potentially)... I removed this from group policy a long while ago.. now, as i'm preparing to goto 2008 R2, i ran dcdiag and noticed a ton of errors.. like these: An Warning Event occurred. EventID: 0x00000065 Time Generated: 01/29/2010 12:25:17 Event String: The assignment of application Wise InstallTailor 6.0 from policy Default Domain Policy failed. The error was : %%1274 ...

cannot find Adobe Acrobat previewer for Outlook
There does not appear to be an Adobe Acrobat previewer for Outlook. Does anyone know where this can be found? If you receive a pdf attachment, and open it, it will open in Acrobat reader, if that is installed. www.adobe.com "ec96" <ec96@discussions.microsoft.com> wrote in message news:7475E317-2EDB-40AA-8286-5268DD5F5449@microsoft.com... > There does not appear to be an Adobe Acrobat previewer for Outlook. Does > anyone know where this can be found? Yes...this works OK. A double click takes me out from Outlook and opens up the Acrobat file in a separate windo...

Group by field the Inbox
If I group by "Recevied" field the Inbox folder, Outlook 2002 (10.xxx ver.) create a gruop for every message (!!!) because it gruop by year-month-day-hour-minute and second too (like in the example image at http://web.tiscali.it/errediemme/img.jpg ). Is there a way to group only by day (year-month-day) and not even by second (hour-minute and second)? Thanks and sorry for my "englian" D�vide from Italy Try going to View | Arrange By | Custom. Click the Group By option and make sure that the check box on top is selected (Automatically Group According to Arrangement). ...

AfxBeginThread failure
In an MFC pgm under VS2005sp1, I am creating a worker thread using AfxBeginThread. Under certain circumstances which I can't quite pin down, AfxBeginThread returns NULL, indicating it has failed. Is there any way I can find out the cause of the failure, such as an error code? I have tried tracing through the code with the debugger, but what is apparently detecting the error condition is in MS code for which I don't have the source. My specific code is CWinThread* pThread=AfxBeginThread(MyControlFunction,&ti,0/*priority*/, 230000000/*stack size*/); and a test for pThread==NULL im...

find a date on sheet 2 and count text in that column
I'm lookung for the formula to use that if I was under one date on one page, to look what date im under go to the 2nd page, find that date then count the text specified under that date. My pages are set up as follows: sheet 1 A B C D E 1| 1-oct 2-oct 3-oct 4-oct 2| john text 1 text 1 text 2 text 1 3| jack text 1 text 2 text 1 text ...

Search does not find messages in Outlook 2007
Hello All, I have Outlook 2007 and Windows Desktop search trunning on a Windows XP Professional machine. The indexing has been completed but I noted that the search will not find certain emails, which contain the text that I am searching for in the content. I can find the message by author, subject, etc. but not based on the keyord. This makes me dowbt of the reliability of the search. Any ideas of what might be going on? Kind regards CD ...

Option group controlling objects
Hello All, Is there a faster way of doing the following. It's kind of choppy when the objects disappear then reappear. I'm sure it's pretty obvious what I'm trying to do. Private Sub OptionButton1_Click() If OptionButton1.Value = True Then Label3.Visible = False Label4.Visible = False Label5.Visible = False Label6.Visible = True ComboBox1.Visible = False ComboBox2.Visible = False ComboBox3.Visible = False ComboBox4.Visible = True Label7.Visible = True Label8.Visible = True ComboBox5.Visible = True ComboBox6....

Query Question - Friend - Friend - Friend
There are two tables. Table1 has names of all people in town, and Table2 has friend relationship of people in Table1. TABLE 1 | TABLE 2 | NAME | IndexID NAME FRIENDNAME A | 1 A B B | 2 A C C | 3 B A D | 4 B D E | 5 C A F | 6 C E G | 7 D B H | 8 D E | 9 ...

Moveing rows
I have to move a block of rows in order to have the spreadsheet in the order I want. I did a move and paste to move the rows and the existing rows ware overwritten. How do I move rows and not have them overwrite existing rows? Thanks, Dennis If you do ctrl + x and then select where you want it, then right click and select insert cut cells or copy and do it, then after you are sure you got it right delete the original -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you...

AD Group Members Disappear
Why would most of the domain members disappear from thier assigned Security and Distribution Groups? This has happened twice to our server. Once during a swing migration about a month ago and then again more recently. The only change I recall doing yesterday was editing the Standard User role and unchecking the Enforce the shared folder quota box. The first time this happened I blamed it on a fluke with the swing migration and possibly a bad replication or something. To fix it, I reassigned members to thier groups manually. Any thoughts on doing this again to get people work...

Can't find origination module
In GP 10, we have some invoices we need to delete - errors from when we just started up about a month ago. When we go to Transactions>>Sales>>Posted Transactions, we can bring up the invoice, but when we hit VOID, it gives us this message: "this transaction didn't originate in Receivables Management. Reversing entries will be made in Receivables Management and General Ledger only. Do you want to continue?" We hit NO because we don't want to screw up the system, but then we can't find the invoices anywhere else. We entered them in Transactions>>Sal...

Move Public Store to new Storage Group
I have 4 Storage Groups, one of which contains the Public Folder Store (running Exchange 2003 SP2). I want to move the public folders to one of the other Storage Groups. What is the best way to do this? - You can't move a Store - public or mailbox Store - between Storage groups. - One PF store can be associated with one tree on a server. - If this is the only replica of PFs you have - you can replicate all public folders using pfmigrate (to another server with a PF store). Once they're in synch remove the Store, create new Store in new SG and replicate stuff back. - You will need ...

Q: open a query with parameters
All, I think I took a stupid pill today because I can't figure this one out. I want to programmatically open up a query like when you use the docmd.openquery("query name") function. But the query I'm using has parameters which need to be filled in. I vaguely remember something about querydef objects, but I can't seem to get it to work. Can someone point me in the right direction? Thanks so much! -Mark ...

Need to find and replace
I have two Sheets. I have a list of numbers in FIRST sheet and another list in the SECOND sheet. But in the second sheet there is another column with another information. What i want to do is that Excel could find the value of the first sheet in the second sheet and could write that data next to the list of values in the first sheet. Here how it shoud look: Sheet1 Column A | Column B 25 35 40 50 Sheet2 Column A | Column B 30 | 1.5 31 | 2.1 40 | 3.1 25 | 4 I WANT: Sheet1 Column A | Column B 25 | 4 35 | NO DATA 40 | 3.1 50 | NO DATA The VLOOKUP function will do much of what you want. ...

progressive totals in a query for a chart
I would like to be able to create progressive totals in a query for display on a chart. The total required only has entries every few months while remaining data appears daily. is this possible? ...

Coloring Alternative Rows only if data is there
Hi, I have a spreadsheet with [ =MOD(ROW(),2)=1] conditional formatting in it. I want to highlight this only if there data in there. I have googled if but cannot find the method can anyone post the method or provide me with link. It will be 2nd conditional formatting I think :l =AND(COUNTA(1:1)>0,MOD(ROW(),2)=1) -- __________________________________ HTH Bob "BetaDocuments" <betadocuments@gmail.com> wrote in message news:c0c21d2b-e800-4c9a-b310-24a10793eb7c@g38g2000yqn.googlegroups.com... > Hi, > > I have a spreadsheet with [ =MOD(ROW(),2)=1] conditiona...

Highlight rows with certain condition in some cells
I want to highlight every row that has a certain word in one column and a different word in another column of the same row. How do I do this? SteveK Here's a great site to learn about conditional formatting :) http://www.mvps.org/dmcritchie/excel/condfmt.htm -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31708 View this thread: http://www.excelforum.com/showthread.php?threadid=520844 "SteveK" wrote: > I want to highlight every row that h...

Last transaction in a query?
I have a table with 5 fields ID PersonID EDate Amount and TransType I want a query with the last record of each person based on date and I want the results like this PersonID LastOfEDate Amount TransType if a person has tow transactions in the max date I want only one of them How can i do that? thanks You must have a "main" table with a single list of PersonID. Lets assume it is called tblPeople, PK = "ID" So, use that table to provide a unquite list of PersonID. The query can be like: select FirstName, Lastname, WorkPhone, PersonID, EDate, Amount, Tr...

Update table with data from rows with previous dates from specifie
I need to update a table where if a row contains a specified stat value I need to change a date value to the date value of a row with the same subID but a different stat value. My primary question would be if I need a cursor to perform this operation or if it can be done without a cursor (Sql Server 2000 Tsql please). In the sample data below if a stat value is in ('A', 'E', 'U') I need to update that row - I need to update the date2 column with a date1 value from the closest row with the same subID but the stat is in ('N', 'R') and ...

How Do I Add The Letter P in Front Of Each Number in a Seriers of Rows in one collum?
Hi, I have a collum with different numbers in each row. Example: 23 34 56 67 44 I'd like to add the letter P before each number all at once rather then typing P before each one manually. Example: P23 P34 P56 P67 P44 Was wondering to go about doing this? Thanks Dino -- Dno ------------------------------------------------------------------------ Dno's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33947 View this thread: http://www.excelforum.com/showthread.php?threadid=537242 One way: Enter in an unused column (say, column B): B1: ="P&quo...