query sort on date

I've got a table where applicant data is entered (via a form) everytime an 
application is submitted.  Each time a person submits an application it is 
enteed as a new submission, thus I can have duplicate records on a person, 
except for the submission date and maybe the address if it changed but it is 
still a new record.  

My questions are how can I query the table data to only show the most recent 
applications without having duplicate records on a person?  Another field in 
the table is the SSN.  

The other question pertaining to the same query is say that an application 
was submitted on 2/28/06 and they were due resubmittal in 2 years (which all 
would be).  How can I have the same query also sort to show from one year out 
of the resubmittal date.  EX: on 2/28/07 this persons record will keep 
showing up until a new application was submitted and entered in with a new 
submission date.  

FYI:  I didn't create this dbase but was asked to help out.  I probably 
would have had the structure a little different.

Any help is appreciated!

-- 
Todd
0
Utf
2/28/2008 5:30:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
739 Views

Similar Articles

[PageSpeed] 2

About the first question, one of the four methods presented at 
http://www.mvps.org/access/queries/qry0020.htm could probably help.

Vanderghast, Access MVP


"Todd" <tak@discussions.microsoft.com> wrote in message 
news:2F3E5278-8633-44FF-A6E7-E9448611A8A2@microsoft.com...
> I've got a table where applicant data is entered (via a form) everytime an
> application is submitted.  Each time a person submits an application it is
> enteed as a new submission, thus I can have duplicate records on a person,
> except for the submission date and maybe the address if it changed but it 
> is
> still a new record.
>
> My questions are how can I query the table data to only show the most 
> recent
> applications without having duplicate records on a person?  Another field 
> in
> the table is the SSN.
>
> The other question pertaining to the same query is say that an application
> was submitted on 2/28/06 and they were due resubmittal in 2 years (which 
> all
> would be).  How can I have the same query also sort to show from one year 
> out
> of the resubmittal date.  EX: on 2/28/07 this persons record will keep
> showing up until a new application was submitted and entered in with a new
> submission date.
>
> FYI:  I didn't create this dbase but was asked to help out.  I probably
> would have had the structure a little different.
>
> Any help is appreciated!
>
> -- 
> Todd 


0
Michel
2/28/2008 7:56:20 PM
Thanks for the info.  It did work for the first part of my question and I 
then figured out the second part?  Again Thanks.
-- 
Todd


"Michel Walsh" wrote:

> About the first question, one of the four methods presented at 
> http://www.mvps.org/access/queries/qry0020.htm could probably help.
> 
> Vanderghast, Access MVP
> 
> 
> "Todd" <tak@discussions.microsoft.com> wrote in message 
> news:2F3E5278-8633-44FF-A6E7-E9448611A8A2@microsoft.com...
> > I've got a table where applicant data is entered (via a form) everytime an
> > application is submitted.  Each time a person submits an application it is
> > enteed as a new submission, thus I can have duplicate records on a person,
> > except for the submission date and maybe the address if it changed but it 
> > is
> > still a new record.
> >
> > My questions are how can I query the table data to only show the most 
> > recent
> > applications without having duplicate records on a person?  Another field 
> > in
> > the table is the SSN.
> >
> > The other question pertaining to the same query is say that an application
> > was submitted on 2/28/06 and they were due resubmittal in 2 years (which 
> > all
> > would be).  How can I have the same query also sort to show from one year 
> > out
> > of the resubmittal date.  EX: on 2/28/07 this persons record will keep
> > showing up until a new application was submitted and entered in with a new
> > submission date.
> >
> > FYI:  I didn't create this dbase but was asked to help out.  I probably
> > would have had the structure a little different.
> >
> > Any help is appreciated!
> >
> > -- 
> > Todd 
> 
> 
> 
0
Utf
2/29/2008 9:30:00 PM
Reply:

Similar Artilces:

date stamp #2
I am a new user to excel and would like to know how I put a stamp in the corner of my workbook that says the file that it is under. Ex h:\xw\... Also would this change if I change the file it is in. Thankyou Jessica ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: =LEFT(CELL("filename",A1), FIND("]", CELL("filename",A1))) Note that the file must be saved for this to work. In article <jdorion.vgeay@excelforum-nospam.co...

Merge different queries
Hello all, I have two tables where I enter my stock trades as well as foreign exchange (FX) trades. The two tables contain different fields as the trading instruments require different entries. I then created two seperated queries in order to calculate the profit from each stock and each FX trade. I need two queries as the calculations are different, too. Now, I would like to merge the two queries in order to see my overall profit and losses no matter if the trade is a stock or FX trade. That is, in the final query I would like to see the trade ID (which is the primary key in eac...

Date #6
hi I have a workbook that contains several formatted cells cell "A1" is formated as dd/mm/yyyy and has the today() formula in it what i want to do is to copy the value of "A1" and paste it into "A4" ie. "A1" = 13/01/2005 i only want the text value to be copied and pasted into A4 "A4" = 13/01/2005 When i try this i get the numeric value of 38365 which is excels text value of the date. I need the above format thanks in advance kevin Pre format or use edit>paste special or use a formula =text(a1,"yourformathere") or...

Checkbox to a query
I have a form with a checkbox; I'm trying to build a query that grabs that value from the checkbox (-1), and use it as a criteria. In the criteria, I want to say: if the checkbox is checked, give me all records that do not have a -1, otherwise give me all records. So to clarify: I have two tables. first one is "new table" and the second one is "archive table". I want to build a query, so that if the checkbox is checked, all records in the "new table" that are NOT in the "archive table" will be shown. If the checkbox is not checked, then I want all...

Number cells won't reformat / sort
I'm dealing w/ a spreadsheet composed of cut-and-pasted rows from 2 sources (A and B) both originally exported from Filemaker at different times. When trying to sort the data by a "Record Number" field, the 2 datasets remain split and will not integrate. In other words, instead of the sort result being something like: # Code 1 WEX 3 WAF 4 WAF 6 WEX 7 WAF 9 WEX They look like this: # Code 1 WEX 6 WEX 9 WEX 3 WAF 4 WAF 7 WAF The 2 groups remain separate and sort individually. It gets stranger, though. If I reformat the column to, say, a percent or a currency r...

Add a date to a Userform
I am using Office 2010 in advance of roll-out in our organization. I have been testing some of our VBA enabled templates for compatibility and have run into a problem. Specifically a lose of the Calendar control. Since we have a number of Userforms that use the calendar control, I need to come up with some sort of replacement. Any suggestions? I thought there was a date picker control, but I can not find it (I have installed the complete Office 2010 Professional Plus) The calendar control uses the library c:\program Files\Microsoft Office\Office13\MSAL.OCX Usually th...

Date Range on Report in Money 2003
I have a favorite report in Money 2003 which lists scheduled payments within a range of dates. My file was upgraded from Money 2000. I used to have the problem of the report omitting many of the scheduled transactions within the date range. That was fixed by running the Salvage program. Now the problem is the other way around: the report shows transactions BEYOND the date range, as well as those within. Is there a fix for that? I still have the Salvage program. :-) Thanks, Elliott ...

Insert date into file import
I have a database that imports a .csv file generated by anotherprogram. Unfortunately this program doesn't include a date in eachrecord in the .csv file.I can import the .csv file into my table alright, but I need to attach/insert a date (which I can get via VB code) into each importedrecord. Any ideas?At the moment I'm using the Docmd.TransferText command. have you tried importing the data, then running an Update query on thetable, to add a date to each record?hth<b_lwalker@hotmail.com> wrote in messagenews:1174264665.772034.212550@n59g2000hsh.googlegroups.com...> I have a ...

query problem 12-01-09
I've got a table of Organizations and a table of Memberships. Memberships have an OrgID and one Org can be part of many Memberships. Memberships have an expiration date. I'm trying to build a list of Organizations that have at least one non- expired Membership associated with it. I'd like each current Org to show once in a list (for an unbound combo box). I tried: SELECT tblOrganizations.OrganizationID, tblOrganizations.Name, [tblMemberships.Anniversary]>(Date()-60) AS [Current] FROM tblOrganizations INNER JOIN tblMemberships ON tblOrganizations.OrganizationID ...

date format #32
i have a sheet where in some cells the date are in ddmmyy format along with text & in some cells mmddyy format along with text. is there any command to change all in mmddyy format On Sat, 20 Aug 2005 03:40:03 -0700, "Ankur" <Ankur@discussions.microsoft.com> wrote: >i have a sheet where in some cells the date are in ddmmyy format along with >text & in some cells mmddyy format along with text. > >is there any command to change all in mmddyy format You need to describe more precisely exactly what is in the cells, and how one can tell the difference betwee...

Excel default date format
1) The default date format for Excel is "21-Mar" when I enter date recognized data. This is unusable for me, I need the year displayed. How do I change the default date format to display the year. 2) With default also, Excel sometimes requires "=" in equations and sometimes not; i.e., if I enter a '-' it converts the cell to an equation. How do i stop this so that it only makes equations when I enter '='. 3) How do I get help on Excel inconsistancies like the above in Excel documentation anywhere? mjk #1. If I type in the date that includes the ye...

Multiple Column Sorting
I've been reading online about sorting multiple columns and I'm confused. Selecting one column to sort also selects all the other columns as well, at least in Excel 2000 and XP. I didn't see any difference in selecting one column or using the "Then by" boxes, in fact, the "Then by" choices didn't work. If the first column sorted in ascending order, and I chose to sort the second and third columns to sort in descending order, Excel still sorted all three columns in ascending order. If Excel sorts the all the columns together by default, why use the "Then...

how do i set an auto date function in excel or word?
I would like all documnts in both word and excel to automatically date as the document or report is prepared. =NOW() "hpkcommish" wrote: > I would like all documnts in both word and excel to automatically date as the > document or report is prepared. for excell Go to View> Header & footer >custom header footer then click on the clock pic & the date pic (choose 1st if you want it in the left middle or right feild) For word basically the same Cheers peterm "hpkcommish" wrote: > I would like all documnts in both word and excel to automatica...

Popup a message when query finished
I have a form that has a command button with code that updates 7 tables by a series of 7 queries. Is there any way to popup a message box to indicate to the user that all the queries have run successfully and they can move on? Although it happens very quickly there is no visual evidence to the user that the query is running or finished. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1 Yes, you can put a Msgbox...

May I post here questions about SQL queries against Excel via ODBC?
and if not, can you recommend of a relevant forum? For example, I'm desperately looking for an answer to the question: How can I retrieve the row number of a record in a query? Somethin like: select x.*,rownum() from sheet1$ as x Thanks in advance, Noam ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try posting in the Security Forum, under SQL Server Security, that's where most people go when they have questions about SQL. ...

Sorting in Pivot Charts
I'm using Access 2003 & am trying to sort in a Pivot Chart. I have a single series & I'm trying to sort by the qty of that series. Thanks much in advance, M.C. ...

Problem with copying a to from date to another cell
I am trying to enter the first 7 days of the year in one cell. ie: A1 -> 1/2/05 to 1/8/05 And then I want to drag that down to 53 cells and have it copy in the following format. ie: A2 -> 1/9/05 to 1/15/05 A3 -> 1/16/05 to 1/22/05 etc... Is this possible? I would do it by using two separate columns, typing in the two top row dates, the highlight the area you wish to fill, then Edit > Fill > Series > Trend..........then when you have the two columns of dates, use a third helper column with this formula copied down...... =TEXT(A1,"mm/dd/yy")&...

How do I sort a column consisting of fractions, decimals, and who.
I need to sort products by the horsepower. Horsepower can be a fraction, decimal or whole number. For example, I need 1/100 to come before 1/10, 1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before 1...etc. Why don't you use the Fraction format under Number format? That way you only enter the decimal value and it will be represented by it's fraction, but the sorter won't be confused. Another option is to use this formula: =IF(ISERR(SEARCH("/",E8)),VALUE(E8),LEFT(E8,SEARCH("/",E8)-1)/RIGHT(E8,SEARCH("/",E8)-1)) Where E8 is ...

How do I sort data in column B to align with data in column A?
Im fairly green to Excel but what I am after is I want to be able to sort a range of codes in Column B to basically move and realign to the same row as the data that is in Colmn A eg Start Column A Column B R F G B B H H R F G Finish Column A Column B R R G G B B H H F F Piece a cake. Delete columm B, then copy column A and paste into Column B "Jephri1" wrote: > Im fairly green to Excel but what I am after is I...

Word 2007 Show Markup checkmarks and Date/Time Issue
Hi, I have a new HP desktop computer and finally got Word 2007. I have two questions. In the Review section of Word 2007, the Show Markup subsection has checkmarks for ink, comments, etc. as the default selection. Is there any way to change this default and uncheckmark them so it isn't automatic? I have looked everywhere online for a solution and can't find one. Second, in the bottom right-hand corner of my monitor, the time is showing as usual. However, when I first got my computer (last week), it also showed the date (I loved this feature). After re-starting my compute...

Problem with Microsoft Query
It doesn't return some of the data. It returns all but one actually. And I went into Microsoft Query help and followed the instructions. The file is closed, and the data is formatted the same way. Any advice on how to fix it? Jul, Your question is extremely vague. You should probably provide more explicit information about the details of your query such as The type of file are you trying to query The program you are using to perform the query Details of the query parameters Details of the information that is not being retrieved Also, make sure you change the Subject of your messag...

Date Format Problems
Is there anyway to make excel change the numbers I type in to the appropriate date? If I type 112603, how can I get the cell to display 11/26/03. I have tried formatting the cell, but it just doesn't seem to come out right. Any help would be greatly appreciated. Thank you Jennifer Only through VBA. See Chip Pearson's website. http://www.cpearson.com/excel/DateTimeEntry.htm Gord Dibben XL2002 On Fri, 5 Dec 2003 08:06:01 -0800, "Jennifer W" <icecream3@earthlink.net> wrote: >Is there anyway to make excel change the numbers I type in to the appropriate date?...

Update query result to table
I can't find a good example on the web showing how to update table with the result from query. Table1 name "Agent1" column have: phone#(primary key), birthdate, and agent (total record 100). table2 name "Agent 2" have the same field (total record 150) except the data on agent is different. I run a query for this two tables and want to update the table1 with the table2 data where the phone#(primary Key). I want the result form the query will replace the agent in Agent1 table. How can i do that? UPDATE Table1 INNER JOIN Table2 ON Table1.[Phone#] = Table2.[Phone#]...

Problem formatting date values in chart...
I have a blood pressure tracking spreadsheet -- at a minimum, it tracks Systolic and Diastolic pressure for a given day. A B C E.g.: Date Systolic Diastolic 1 9/3/04 120 90 2 9/4/04 123 87 3 9/5/04 129 79 I currently have a simple line graph that shows systolic data points over diastolic data points. The left hand (Y axis) value is "Pressure". My problem is that I want the "date" values of Col. A to display at the bottom of the graph as a label with each corresponding data point and...

Due date VS processing date in Money '06
This is really bothering me. In my previous version, when I entered a due date for an epay, my register showed that date. It didn't remind me that I need to have XXX dollars in my account on due date - 4 and then change the date in my register to due date - 4. I want my register to show the due date I entered. I have enough sense to know I need money in my account. How can I change this? The check box about using processing date instead of entered date is NOT checked. Did my upgrade magically switch me to a bill payment service that is actually drafting my account early and pu...