Automated transaction grouping and numbering

I have been asked to help a co-worker automate a data review task. I
am working in Access 2003. We have a query output in Excel (from our
mainframe) that lists employees with the following fields:
SSN, EmplName, ActionCd, ActionEffDt

Action codes (ActionCd) include Hire, Term and Rehire.

The objective is to identify employees who rehired within 365 days of
their termination. We have some people who have termed and been
rehired up to 5 or 6 times (seasonal workers, perhaps?)

Here's how I am currently calculating. I assigned all "Hire" Action
Codes a TxNo of "1". I sorted remaining transactions ascending by
date. I manually entered 'pairs' of TxNo's for the terms and rehires.

TxNo	SSN	EmplName	ActionCd	               ActionEffDt
1	111223333	DOE JOHN M	HIRE      	                22-Jan-01
2	111223333	DOE JOHN M	TERM      	                30-Jan-01
2	111223333	DOE JOHN M	REHIRE      	06-Mar-01
3	111223333	DOE JOHN M	TERM      	                16-Mar-01
3	111223333	DOE JOHN M	REHIRE    	07-Jun-04
4	111223333	DOE JOHN M	TERM      	                15-Jun-04
4	111223333	DOE JOHN M	REHIRE    	20-Aug-07
5	111223333	DOE JOHN M	TERM      	                29-Aug-07

I created queries for each transaction 'pair' and calculated number of
days from termination to rehire. Assembled Txn query results in a
query so that all transaction results would show on a single row for
an individual.

(This didn't paste too well, but maybe you can get the idea... the
first full line would be the column headings, the second full line is
how the data shown above would display and calculate.)
SSN	Employee Name	Term2Date	Rehire2Date	Days2Diff	Date2Diff	Term3Date
Rehire3Date	Days3Diff	Date3Diff	Term4Date	Rehire4Date	Days4Diff
Date4Diff
111223333	DOE JOHN M	1/30/2001	3/6/2001	35	Within 12 months	3/16/2001
6/7/2004	1179	N/A	6/15/2004	8/20/2007	1161	N/A

The problem with this solution is that it requires manual entry of the
"Transaction pair numbers" and we have too many records for this to be
a viable solution. Can anyone offer a possible solution for automating
either assigning numbers to the transactions so that I can group them
OR another way to automate this process?

Thanks in advance!
0
Ruth
3/10/2008 8:32:35 PM
access 16762 articles. 3 followers. Follow

1 Replies
850 Views

Similar Articles

[PageSpeed] 20

So the goal is to select all records of re-hirings where the person 
previously left within 365 days.

You could use a subquery to examine the same table for the records where 
TxNo is other than 1, it's the same employee, and the date is in the 
previous year.

This is just an example, but hopefully sets you on the track:
SELECT * FROM Table1
WHERE TxNo = 1
AND EXISTS
  (SELECT Max(ActionEffDt) AS MaxDt
  FROM Table1 AS Dupe
  WHERE Dupe.TxNo <> 1
  AND Dupe.SSN = Table1.SSN
  AND Table1.ActionEffDt - Dupe.ActionEffDt
    Betwen 1 And 365);

If subqueries are new, here's an introduction:
    http://allenbrowne.com/subquery-01.html#AnotherRecord

-- 
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.

"Ruth" <ruthhays@gmail.com> wrote in message
news:91a2fc96-bb56-437b-bd1a-26d7f9736e63@2g2000hsn.googlegroups.com...
>I have been asked to help a co-worker automate a data review task. I
> am working in Access 2003. We have a query output in Excel (from our
> mainframe) that lists employees with the following fields:
> SSN, EmplName, ActionCd, ActionEffDt
>
> Action codes (ActionCd) include Hire, Term and Rehire.
>
> The objective is to identify employees who rehired within 365 days of
> their termination. We have some people who have termed and been
> rehired up to 5 or 6 times (seasonal workers, perhaps?)
>
> Here's how I am currently calculating. I assigned all "Hire" Action
> Codes a TxNo of "1". I sorted remaining transactions ascending by
> date. I manually entered 'pairs' of TxNo's for the terms and rehires.
>
> TxNo SSN EmplName ActionCd                ActionEffDt
> 1 111223333 DOE JOHN M HIRE                      22-Jan-01
> 2 111223333 DOE JOHN M TERM                      30-Jan-01
> 2 111223333 DOE JOHN M REHIRE      06-Mar-01
> 3 111223333 DOE JOHN M TERM                      16-Mar-01
> 3 111223333 DOE JOHN M REHIRE    07-Jun-04
> 4 111223333 DOE JOHN M TERM                      15-Jun-04
> 4 111223333 DOE JOHN M REHIRE    20-Aug-07
> 5 111223333 DOE JOHN M TERM                      29-Aug-07
>
> I created queries for each transaction 'pair' and calculated number of
> days from termination to rehire. Assembled Txn query results in a
> query so that all transaction results would show on a single row for
> an individual.
>
> (This didn't paste too well, but maybe you can get the idea... the
> first full line would be the column headings, the second full line is
> how the data shown above would display and calculate.)
> SSN Employee Name Term2Date Rehire2Date Days2Diff Date2Diff Term3Date
> Rehire3Date Days3Diff Date3Diff Term4Date Rehire4Date Days4Diff
> Date4Diff
> 111223333 DOE JOHN M 1/30/2001 3/6/2001 35 Within 12 months 3/16/2001
> 6/7/2004 1179 N/A 6/15/2004 8/20/2007 1161 N/A
>
> The problem with this solution is that it requires manual entry of the
> "Transaction pair numbers" and we have too many records for this to be
> a viable solution. Can anyone offer a possible solution for automating
> either assigning numbers to the transactions so that I can group them
> OR another way to automate this process?
>
> Thanks in advance! 

0
Allen
3/11/2008 4:49:47 AM
Reply:

Similar Artilces:

Automation
Hello Everyone, Recently our IT group installed service pack 3 on our computers. I wrote some code in word that automatically attaches it self to an outlook email and sends it out. I'de like to disable two windows that i didn't get before they installed service pack 3 there are not titles so here is a description of the boxes: 1 - A program is trying to access email addresses you have store in outlook. do you want to allow this? then it asks for "allow access for" (1,5,10 minutes) 2 - then i get a similar one that just asks you if you're sure you want to send ...

Bank Management
Hi, My client uses the Bank Management module, and for some unknown reason they have a problem with one specific user - this user when they go to the Bank Reconcile window do not see any transactions. However as another user, the transactions are clearly visible. Also if the user logs into the problem users machine as themaelves, they too can see the transactions, indicating it is a problem with the user and not machine. Any ideas? Neil Palmer ...

Restrict CD-Rom to specific group
I am trying to Disbale access to the CD/DVD-ROM drive on a single computer where only certain users are allowed to access removble media. I found the Group Policy to disable the CD-ROM drive at http://support.microsoft.com/kb/555324, but I actually would like to restrict the CD/DVD-Rom to a specific user group. Is tis possible? This policy just disables it, but I can accomplish this through device manager. ...

CRM and Fax automation question
Hi, I'm looking for a good or better fax server that can be easily and seamlessly integrate with MS CRM 3.0 or 4.0. I'm thinking of RightFAX or GFIFax... or even if I install these fax server in my environment, do I still need to do programming and customizations, such as post callout when a new Fax activity is created? Please advise :) You immediate response is appreciated. "Benjamin Fallar III" <fallar@pldtdsl.net> wrote in message news:3484C69D-B193-4B48-86A4-A4529D9DE4C6@microsoft.com... > I'm thinking of RightFAX or GFIFax... or even if I install ...

Text to number
How is a string of numbers from a userform textbox converted to a numberic value? cdbl(Textbox1.Text) or if an integer clng(textbox1.text) or just set it to a variable declared to be the proper type Dim dblVal as double, lngVal as Long dblVal = textbox1.Text lngVal = Textbox2.Text -- Regards, Tom Ogilvy "bob" <bob@thenet.com> wrote in message news:069c01c35dd1$1a9905d0$a001280a@phx.gbl... > How is a string of numbers from a userform textbox > converted to a numberic value? ...

Provisioning/Automation tools for Exchange??
I have been asked to find some tools possible of automoating mailbox moves to specific databases/storage groups based on the department of a user for instance. Let's say User1 is part of HR and has transferred to Marketing. All the HR mailboxes reside on SG1 DB1 and all Marketing reside on SG1 DB2. Is there any software anyone knows of that can read the department attribute from AD and then move the mailbox according to defined rules you put in place. Any and all help appreciated! Mike In a couple of situations, organizations I have worked in have talked about having something li...

Pivot Table Groupings Fail if 1of the items isn't in the next data
I am Grouping items together in a Pivot Table and everything works fine until I use the next months data. For example, I have different types of earning codes and I am grouping as follows. Codes 1, 3 & 5 = Group A 2, 4 = Group B 6 = Group C If in the next pay period, I do not have any Code 4 Earnings, Group B looses the Group B name, and becomes '2'. It changes every pay period and I rarely have all codes in the same pay period. Hi Jerry Double click on the field Code, and select Show Items with no data. -- Regards Roger...

Contacts Group Mailing
I'm using Outlook 2000 Internet Only in XP and can't find the answer to my question in "Help." I wonder if I might ask it here? I've built a group of about fifty individuals in my Contacts - just as a Group. I want to mail them something and not have individual e-mail addresses show. When I compose my letter only the Group Title appears in the To space. Is that all the recipients will see too? I don't want them to see all the e-mail addresses and don't want to send until I'm sure that's all they'll see - the Group Title that is. Many of you must...

Any way to get around the number of columns limitation?
I'm pretty sure there's not, but figured I'd ask anyway. ;) I have a charting workbook that has grown to very probably needing more than the 256 columns of data to do all the charting. At least for it to continue doing it the way it is. I was hoping this particular deal I'm doing right now would stop prior to running out of columns, but it's not looking like it will. Any help? Thanks! Shawn Wait for the next version of excel??? 16K columns by 1 meg of rows. 43fan wrote: > > I'm pretty sure there's not, but figured I'd ask anyway. ;) > > ...

renaming CRM group in CRM OU in AD
Hi there, Just wanting to check renaming of groups in CRM OU created by CRM instalation will be a problem? IT manager at a site wants to keep it to inhouse naming convention and wnated to know renaming them will cause problem. Rgds, Kyaw CRM uses the SID's rather than the names, so I think you should be ok. Don't take my word for it however, as you'll severely screw your CRM environment if it does have a detrimental effect. Try it in a VPC first. HTH, R. "Kyaw" wrote: > Hi there, > > Just wanting to check renaming of groups in CRM OU created by CR...

Is it Possible to Automate a Graph?
I have a sheet that has data in weekly fashion in the first and second rows. (Row 1, week: Row 2, data.) Is it possible to have a graph populate off of the information in rows 1 and 2 that automatically includes data for new weeks as they are entered in to the sheet? For example, I want to create a graph for weeks 1 through 3 now, and want the chart to automatically update if I add data for week 4. Row 1 Week 1 2 3 Data 12.5 17 18 Thanks for the assist! -Adam Use a defined name using the offset function for your series --...

Macro for Finding account numbers
I am a beginner at writing macros so I am asking for assistance. What I would like to do is have a macro that pulls from a cell or input box an account number the user is wanting to see and locates on that same worksheet. Basically identical to the Find function but with a macro for the input. Thanks in advance for any assistance. Try something like the following: Dim FindWhat As String Dim FoundCell As Range FindWhat = InputBox("Find What", "Find") If StrPtr(FindWhat) = 0 Then Exit Sub End If On Error Resume Next Set FoundCell = Range("A1:A100").Find(...

Gluing to a Grouped Shape Using VBA
Hi, You've all been so helpful! Another question. I create a line using Set shLine = .DrawLine(1, 1, 2, 2) Set clGlueStart = shLine.Cells("BeginX") I have a grouped shape that contains a circle with different pie-shaped segments and I'd like to glue one end of the line to that shape. I try it using Set cel1 = .Shapes("MyPie 1").Cells("PinX") clGlueStart.GlueTo (cel1) but I get a "Compile error: type mismatch" message. Both clGlueStart and cel1 are dimensioned as cells, so that's not the problem. And when I check to see if the two ...

Seperate Numbers from Text
Hi I have 1200 lines of text and numbers in the form below Gross Sales 1200 1500 2400 1100 Garden 40 50 65 75 Plants 20 25 35 45 Each entry is in 1 cell , how can I examine each cell , find the end of text and move any subsequent numbers to the next cell right, ( I can then use text to columns to seperate the numbers) Regards GW Hi! I would use Text to Columns right away. I know this leaves some tidying up to do, because of the differen number of words preceding the numbers. I would add a helper column n...

Duplicate Transactions Downloading
The last few days, duplicates have been showing up in my Schwab accounts. I assume it must have something to do with Schwab itself, but has anyone else experienced this phenomenon? I do an online update, and then accept transactions. I update again immediately (or later) and the same transactions appear as new. I have to either match them or accept them and then go in an delete them. Hope this doesn't keep up too much longer! Thanks In microsoft.public.money, Submarine LDO wrote: >The last few days, duplicates have been showing up in my Schwab accounts. I >assume it must ha...

Way to automate adding pictures in a series in Excel?
I'm putting a column of pictures of people next to a column with their names. If I have the pictures together in a folder, with the photo file names numbered with the corresponding row numbers that the pictures go in, is there a way to record a macro so that (for instance) 28.jpg will go into the picture column in row #28, then 29.jpg will go into the picture column in row #29, etc. In other words, insert a whole series of pictures (filenames the same as their appropriate row numbers) in their correct places, by initiating one macro? If anyone has any idea of how that could be done, I wou...

group e-mail
How do I create a nick name for a group and create a list of e-mail addresses to go with that nick name? I have tried to copy a list on in-coming e-mail, but it won't let me paste into my address book. "=?Utf-8?B?bWFjIHRvIGRlbGw=?=" <mac to dell@discussions.microsoft.com> wrote in news:AA2782ED-7291-420D-AEA2-4EEC64E4BCC5@microsoft.com: > How do I create a nick name for a group and create a list of e-mail > addresses to go with that nick name? I have tried to copy a list on > in-coming e-mail, but it won't let me paste into my address book. > What ver...

Automating transfers, is this possible?
Hi All. I am using MM 2007 Premium and I'm using Advanced Bills. In "Help" it says .... "Make electronic transfers. Set up automated transfers between any accounts you track in Money" I have a set up an "xfer" from my checking to my savings (same bank) to occur once a month but Money doesn't actually do anything unless I manually go to "Bills Summary", check it and and click "Pay Online". This isn't what I would call an automated transfer. Is there any way to actually make this happen? Thanks, Richard ----== Posted via ...

VBA automating Word from PowerPoint 11-22-09
I have an add-in macro that exports PPT content to a new Word document for each PPT file in the same folder as the currently open PPT file. I've noticed that when the macro is finished, and I go to Word, Word seems "paralyzed" (I can't use any menu commands or close the doc). If I Alt+Tab back to PowerPoint, and then Alt+Tab back to Word, all is well. Is there something about automating Word from PowerPoint that needs to be "cleared" when the PowerPoint macro ends - some way to release PowerPoint's control over Word? I found http://www.pptfaq.com...

12 digit number rounding down why?
12 digit number entered with cell formatted as just a number is rounding to end in zero all the time. Help please. cell is not locked Can you give an example or two? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Lyd" <Lyd@discussions.microsoft.com> wrote in message news:330466D7-D861-4705-A84C-D393AE9B059F@microsoft.com... > 12 digit number entered with cell formatted as just a number is rounding > to > end in zero all the time. Help please. cell is not locked You sure it is only 12 digits? Excel will add ...

if we enter number(500) in cell A1, can we display it as five hun.
if we enter number(500) in cell A1, can we display it as five hun... shaj, have a look here http://support.microsoft.com/default.aspx?scid=kb;[LN];Q95640 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "shaj" <shaj@discussions.microsoft.com> wrote in message news:FFB6F7C5-091C-41BA-8F0D-7DD87A171F55@microsoft.com... > if we enter number(500) in cell A1, can we...

Outlook group by sender errors
We have outlook 2000 and the view configured to group messages by sender. One particular sender has had all it's emails deleted but is still showing with an email from another sender. The item count is incorrect. Any one got any ideas on this one? Thanks Paul ...

Category Group
How can we add new category group or modify pre-defined category groups? In microsoft.public.money, Mark wrote: >How can we add new category group or modify pre-defined >category groups? See FAQ available at http://www.bollar.org/msmoney/ for information. Plus it was not desirable to post twice. "Mark" <anonymous@discussions.microsoft.com> wrote in message news:74a201c3e76e$8c5a8ff0$a001280a@phx.gbl... > How can we add new category group or modify pre-defined > category groups? I asked a question about Category Groups not too long ago. Here was Dick Watson...

automation
Hi there; I am copying specific cells from a file (actuallu use the address approach, like aa!c12) to another file. The cells address in source file are always the same, but in target file changes each time to lower rows than previous one. I have 100 source files at least, how to automate this, instead of clicking at each cell each time between source and target files. Thanks for your help. in your target file, use dir to get the first source file, copy the cell(s) desired, close this source file, use dir() to get 2nd file and repeat till finish "Darius" wrote: > Hi there; ...

Groups Gone Wild
It seems that Microsoft has accidentally created a few groups over the years, then removed them from their own servers. I'm afraid it's a hit/miss story, though, as far as them still being available on the broader usenet. Two I'm aware of: microsoft.public.access microsoft.public.word.programming On some NNTP servers, not on others. I don't know by what criteria NNTP admins accept rmgroup commands, but my impression was most don't these days because they're so easily forged. Question is, how long the old microsoft.public.vb.* hierarchy will su...