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

Similar Articles

[PageSpeed] 58

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:

Missing action button on local data group
I am missing the 'Actions' button on the data group tab. Therefore I can not set up the correct sync to my outlook clients. What am I missing? Thanks ...

Skipping Columns/Rows
Is it possible to skip a column or row when tabbing in a workbook? In other words, when I'm in A1 and I hit the tab key, I want to be in C1. P.S. -- I do not want to hide the columns as I'm an advocate of using the least amount of steps as necessary to produce a document. In other words, I do not want to hide, enter data, unhide, print, receive additional data, hide, enter data, unhide, print, and so on and so on. Thanks!! Lilbit, Format columns A and C to be unlocked (on the protection tab of the format dialog). Then protect the sheet, and allow selection of unlocked cells on...

looking for an empty row to paste a range of copied cells
Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1 and then look for the next empty row on sheet 2 and paste it in range ("A27:L27") . I would also like the macro to insert a new blank row (or insert the copied row) for the purpose of shifting existing SUM functions on sheet 2 down. I would like those functions to be right below the copied/pasted cells every time the macro is executed. Thanks for any help - JimA On Feb 11, 5:21=A0pm, Jim A <J...@discussions.microsoft.com> wrote: > Hi - I am trying to make a code that will copy a ...

How to merge columns and rows into one cell, besides using the Merge and Center icon?
Hello, I want to merge the columns and rows starting from A1 right up to J2; no lines in between as one whole cell. I have text data value in D1 and D2 respectively. Using merge and center icon can only retain upper-left most data only. This means that only the text data in D1 is retained, data in D2 is deleted. How can I merge the stated rows and columns into one cell and center the text within? Note: I�m not talking about any formula here) Thanks for any help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages...

Emailing a large contact group
I am sending out an email to around 1500 emails for a PTO notification. I have these 150 emails in a contact group. I type my email and hit send. During the sending it shows an error that one of the emails is not valid. My question is how can I remove that email from that email and get it going again? I don't want to have to delete that email and take out the invalid address in the contact group each time then retype the email and try to send again. Does that make since? I assume I can send that many addresses in one email. What I am sending is a long letter and I am t...

Rows to Keep at Top...
I see there is on Page layout an area to select what cells are wanted to repeat on ALL pages. Is there a way to select non-contiguous rows. Example...A1-A4 and A7-A9. Thanks for any assistance. David Hide..? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "116" <116@discussions.microsoft.com> wrote in message news:E7792D4B-2FFC-4C95-AF61-9C28BBA8DCBF@microsoft.com... >I see there is on Page layout an area to select what cells are wanted to > repeat on ALL pages. Is there a way to select non-contiguous rows. > Example...

Query Based DG
What is the criteria that Exchange 03 uses to assign Query Based Distribution Groups to Administrative Groups? Rich, Take a look at this article and see if it answers the question you have. 822897 How to Troubleshoot Query-Based Distribution Groups http://support.microsoft.com/?id=822897 -- JamesA@online.microsoft.com This posting is provided "AS IS" with no warranties, and confers no rights. Note: Please do not send email directly to this alias. This alias is for newsgroup purposes only. "richnep" <ithopkinton@biosource.com> wrote in message news:O5I...

Date Related Query
hi need help again.... I have this Data Date Submitted On-Time 3/14/2010 x 5/29/2010 x 3/12/2010 I have to count the number of on time delivery(with x) of tasks for a certain range example from 3/8/2010 to 3/14/2010...for this case the return value should be 1..I hope this is clear enough... Thank you! Assume that your DATE SUBMITTED is in A column and the ON-TIME is in B Column. =SUMPRODUCT((A2:A100>=DATE(2010,3,8))*(A2:A100<=DATE(2010,3,14))*(B2:B100="X")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl...

Find Money 2006 Premium Rebate
I downloaded Money 2006 Premium but didn't print out the rebate at that time. Now I'm trying to find the link to the rebate again. Can I still get to it? Go to Google and search for "rebate Money 2006"; you'll find several links and copies of the $30 rebate coupon. One of them is at Amazon.com. BUT... the coupon specifically says it isn't valid on downloaded products. On Wed, 28 Dec 2005 21:12:04 -0800, Lyonheart <Lyonheart@discussions.microsoft.com> wrote: >I downloaded Money 2006 Premium but didn't print out the rebate at that time. >Now I'...

XML for finding artist items
Website one has a big catalog of my favourtie (but some usually I haven't heard of) artists names and album titles but Website two only provides songs but it is very complete. How do I use xml to link up these 2 websites? I want to build up a database in mySQL for example like this. Thanks Artist Songs songs contents checked Madonna papa don't preach @sldiufyhisufyiua y Madonna ........... Michael Jackson Smooth Criminal osoifoasrjajoijf n Yes....

total of multiple rows + multiply
I have column A (10 rows with different amounts) column B (10 rows with different amounts) I need the total from column B once it was multiplied by A I can do column C A*B and then B1:B10. What would be the formula without creating column C -- smile Try this: =SUMPRODUCT(A1:A10,B1:B10) -- Biff Microsoft Excel MVP "israel" <israel@discussions.microsoft.com> wrote in message news:300392AA-A4E6-4443-A21D-45B077DCCA51@microsoft.com... >I have column A (10 rows with different amounts) > column B (10 rows with different amounts) > I need the t...

Deleting rows 05-03-10
I'm trying to figure out if I can set up a macro to delete rows if they do not contain anything in specific columns. For example, I have product codes in column A and totals for on-hand and ordered numbers in columns B and C. If some rows do not have on-hand or ordered numbers I want to delete those rows. Any ideas? Start here http://www.rondebruin.nl/delete.htm For example in the first macro use Change the range in this line that you want to test If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete Sub Loop_Example() ...

Is the web-based interface to the microsoft.public groups working?
I was on this page: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx and I selected (expanded) the microsoft.public.win98 tree (on the left side), and then went back (scrolled down) to select microsoft.public.win98.gen_discussion and after a minute or two I get this: -------------- Service Temporarily Unavailable We apologize for this inconvenience. Please try again later. --------------- I got this last night, and I'm getting it again just now. Is there some other way to bring up the microsoft web-interface to this group? YES Start here! htt...

Printing one row at top of every page
I seem to remember being able to do this, but I can't say for certain and can't remember for the life of me how. If I have one row [the one I have on my sheet is not Row 1, but if it needs to be I can make it Row 1], and I want that particular row to print on the top of every page [as it has the column descriptions for my sheet]. Anyone know how to do that? Any help would be great. Thanks Scott Go into File, Page Setup and click onto the Sheet tab. Here, it says 'rows to repeat at top'. Click onto the row number of the one you want visible at the top of subsequent pa...

Count results from query with no results
I have continous form with an unbound text box [keyword] and a button [search]. There is also an unbound textbox [numberofresults] that counts the number of records returned. If the search produces no results, the [numberofresults] needs to read 0, instead it is just blank. What is the proper way to show zero results as 0 and not blank? It depends on how you are doing your search? Do you have a function? Are you just filtering the recordset? Dale -- email address is invalid Please reply to newsgroup only. "Ryan Tisserand" wrote: > I have continous form with an un...

Label error "You must select a valid Row and Start Column"
Jeff, Not sure you would remeber me. You had helped back in your SMS days. Can you help with this problem Label printer: When attempting to printing a label with a Cognitive Bar code blaster Advantage II I get this Store Operation Manager error "You must select a valid Row and Start Column" Thank you John momot ...

Query on NdisMResetComplete
Hi, When i try to complete a Pending Reset with NdisMResetComplete, the system / verifier bugchecks with ***NDIS*** : Miniport (null) - Completing reset when one is not pending. The AddressingReset value is set to FALSE and always return NDIS_STATUS_PENDING from the MiniportResetEx handler. I try to complete the reset from a workitem. Any help on this will be greatly appreciated. This is the bug check code. (0x0000007c) BUGCODE_NDIS_DRIVER (7c) This is the NDIS Driver Bugcheck for Windows Server 2003 and later. For Windows 2000 and Windows XP, see 0xD2, BUGCODE_ID_DRI...

Query Data Format
I am importing a simple patient list into Excel that is the data range for a vlookup formula. The challenge is that the patient RecordID used for the value to lookup is an autonumber in Access and imports in a format other than a number that the vlookup formula does not recognize. RecordID LastName FirstName 24001 Brown John If I go to the data range and manually place the curser behind each RecordID "number" and hit delete it then aligns to the right and is recognized as a number. I want to have the query updated or refreshed at opening of the file an...

select query including dates outside of specified range
Hi all, I have a query set up for my report that is supposed to return all records where the Admit Date is before or equal to the selected date, AND the Discharge Date is after the Selected Date. In the criteria for the Admit Date field, I put: =<[Requested Date] In the criteria for the Discharge Date field, I put: Is Null or >[Requested Date] The results for Discharge Date are fine. No records with a Discharge Date before or equal to the Requested Date appear. Not so good for Admit Dates. I get back all records with the appropriate Discharge Dates, even those with Admit Dates AFT...

- Need Help Finding Duplicate Customers
Hello Everyone, I have two customer lists with a names and customer numbers. They are currenlty in the same spreadsheet. I'd like to create a list of duplicate customers who have the same customer number. One list has over 10,000 customers. The other list only has a few hundred. How do I find the duplicates and create a separate list of only the duplicate customers with the same customer number? Thanks so much! Take a look here: http://cpearson.com/excel/duplicat.htm In article <7rhb51t68u88l69u44kbnc9gbj4jll2o10@4ax.com>, mary wei <marywei@pacbell.net> wrote: ...

Allow a group to have "Send As" permission for all mailboxes in a Mailbox Store
Hi, I'm running Windows and Exchange 2003 in 2003 native mode. In ESM I have set the security for an AD group (called "Helpdesk) to have "Receive As" and "Send As" permissions on our Mailbox Store. This has allowed them to easily access other users mailboxes, but it doesn't appear to actually let them "Send As" which I need to set up (not "Send on behalf of..."). If I go into a user's account in ADUC and go to "Exchange Advanced > Mailbox Rights" it shows Helpdesk as having "Full mailbox access" which I ass...

query error message
UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID = ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name] WHERE (((Len([name]))>0)) When I try to run this I get the error message An Error occurred while executing query: Inccorrect syntax near the keyword ‘inner’ We used to have local support that set this up we were able to run it once when it was set up and was told we could re run it when ever we needed to update the infor. Also was told they would send me a new query that would update this info constantly. Never got the email they disap...

Simple Rules Query
I'm running Outlook XP, connecting to an Exchange server for email (that gets left on the server). I'd like to set up a simple rule that states that all mail from people within my organization should be left in the Inbox, and everything else should be moved into another folder (also on the server) called Junk. This is what I've got so far: Apply this rule after the message arrives move it to the Junk folder except if the sender is in the Global Address List Address Book* As far as I can tell this should work, but it doesn't. All incoming mail (from both internal and e...

Multiple Queries to Multiple Reports
I have a database that tracks cadets that will commission into the military and they are broken into their year groups. My problem is that to generate a report for each year group I need a seperate query for each year group so I have multiple reports and multiple queries in my database. I'm trying to figure out a way to eliminate all those quieres down to one query and report so when I click the button to bring up the report a box will open up asking me to type in the year of commission and then the report will only show those cadets commissioning in that year. Any hel...

Group By 04-28-10
I have a data table which consists of the following fields: concatenate1(which concatenates my gvkey, & datadate), gvkey, datadate, tic, sic, pbaco, etc....Each field for a given concatenate number is either blank, or has some data in it across the entire data set. I would like to bring back one row, signified by the concatenate1 field with one line of data. Example is shown below. Thanks in advance. Concatenate 1 gvkey datadate tic sic pbaco 1000-20081503 1000 20081503 5 1000-20081503 1000 200...