Find 3rd Record

Hi there,

I need to find the third record in a query.  This is my basic select query:

SELECT tblGuests.GID, tblGuests.Date
FROM tblGuests
ORDER BY tblGuests.GID, tblGuests.Date;

There are several thousand records in tblGuests, some Guests (GID) have just 
one record, others have 20 or more.  I've thought of DateDiff function but 
that doesn't seem to work.  I have also thought that I need to find only 
records where there are more than 2 records for a certain GID so I can filter 
out the Guests who have only visited 1 or 2 times using the count function 
but have no idea how to do this either.

Any help is greatly appreciated!

0
Utf
2/5/2008 8:28:08 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
929 Views

Similar Articles

[PageSpeed] 56

There is no such thing as third record as they are all in one big storage 
container.   They get organized when you do a query and sort.
If you want guest with 3 or more records then use this ---
SELECT tblGuests.GID
FROM tblGuests
GROUP BY tblGuests.GID
HAVING (((Count(tblGuests.GID))>=3));

You can use this query left join to the table to pull records of those with 
three or more. 
-- 
KARL DEWEY
Build a little - Test a little


"Johnny Bright" wrote:

> Hi there,
> 
> I need to find the third record in a query.  This is my basic select query:
> 
> SELECT tblGuests.GID, tblGuests.Date
> FROM tblGuests
> ORDER BY tblGuests.GID, tblGuests.Date;
> 
> There are several thousand records in tblGuests, some Guests (GID) have just 
> one record, others have 20 or more.  I've thought of DateDiff function but 
> that doesn't seem to work.  I have also thought that I need to find only 
> records where there are more than 2 records for a certain GID so I can filter 
> out the Guests who have only visited 1 or 2 times using the count function 
> but have no idea how to do this either.
> 
> Any help is greatly appreciated!
> 
0
Utf
2/5/2008 9:39:27 PM
Hi Karl,

Thanks, that does work but what I need to have isolated is the 3rd record 
for each guest.  I guess this isn't possible.  I'll have to try another way.

Thanks!

John


"KARL DEWEY" wrote:

> There is no such thing as third record as they are all in one big storage 
> container.   They get organized when you do a query and sort.
> If you want guest with 3 or more records then use this ---
> SELECT tblGuests.GID
> FROM tblGuests
> GROUP BY tblGuests.GID
> HAVING (((Count(tblGuests.GID))>=3));
> 
> You can use this query left join to the table to pull records of those with 
> three or more. 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Johnny Bright" wrote:
> 
> > Hi there,
> > 
> > I need to find the third record in a query.  This is my basic select query:
> > 
> > SELECT tblGuests.GID, tblGuests.Date
> > FROM tblGuests
> > ORDER BY tblGuests.GID, tblGuests.Date;
> > 
> > There are several thousand records in tblGuests, some Guests (GID) have just 
> > one record, others have 20 or more.  I've thought of DateDiff function but 
> > that doesn't seem to work.  I have also thought that I need to find only 
> > records where there are more than 2 records for a certain GID so I can filter 
> > out the Guests who have only visited 1 or 2 times using the count function 
> > but have no idea how to do this either.
> > 
> > Any help is greatly appreciated!
> > 
0
Utf
2/5/2008 10:04:27 PM
Try this ---
SELECT Q.GID, Q.Date, (SELECT COUNT(*) FROM tblGuests Q1
      WHERE Q1.[GID] = Q.[GID]
        AND Q1.Date < Q.Date)+1 AS Rank
FROM tblGuests AS Q
WHERE ((((SELECT COUNT(*) FROM tblGuests Q1
      WHERE Q1.[GID] = Q.[GID]
        AND Q1.Date < Q.Date)+1)=3))
ORDER BY Q.GID, Q.Date;

-- 
KARL DEWEY
Build a little - Test a little


"Johnny Bright" wrote:

> Hi Karl,
> 
> Thanks, that does work but what I need to have isolated is the 3rd record 
> for each guest.  I guess this isn't possible.  I'll have to try another way.
> 
> Thanks!
> 
> John
> 
> 
> "KARL DEWEY" wrote:
> 
> > There is no such thing as third record as they are all in one big storage 
> > container.   They get organized when you do a query and sort.
> > If you want guest with 3 or more records then use this ---
> > SELECT tblGuests.GID
> > FROM tblGuests
> > GROUP BY tblGuests.GID
> > HAVING (((Count(tblGuests.GID))>=3));
> > 
> > You can use this query left join to the table to pull records of those with 
> > three or more. 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Johnny Bright" wrote:
> > 
> > > Hi there,
> > > 
> > > I need to find the third record in a query.  This is my basic select query:
> > > 
> > > SELECT tblGuests.GID, tblGuests.Date
> > > FROM tblGuests
> > > ORDER BY tblGuests.GID, tblGuests.Date;
> > > 
> > > There are several thousand records in tblGuests, some Guests (GID) have just 
> > > one record, others have 20 or more.  I've thought of DateDiff function but 
> > > that doesn't seem to work.  I have also thought that I need to find only 
> > > records where there are more than 2 records for a certain GID so I can filter 
> > > out the Guests who have only visited 1 or 2 times using the count function 
> > > but have no idea how to do this either.
> > > 
> > > Any help is greatly appreciated!
> > > 
0
Utf
2/5/2008 10:26:20 PM
Reply:

Similar Artilces:

Reset Transaction Date in GL20000 to match SOP30200 records
I'm not sure if this is possible. Very recently, One of our clients posted the Sales Transactions of about 4,000 and posted to GL as well. After posting, he realized that all GL entries for these batches are updated to one single date. On research, We found that the Postings Settings are somehow changed to pick up the Posting Date from Batch for General Entry Transactions. Now, I'm just checking on a possibility if we could reset these transactions dates by way of a SQL statement to copy the dates from SOP30200 table to GL20000 for these transactions alone. Say, A Transaction for whic...

Find workbook with code.
Hi All, I have about 4 excel workbooks that related to my db.I want to include these 4 workbooks as one package with my DB.I create a package cause need to copy to another computer. Let say their names are MyWorkbook 1,2,3,and 4. Currently I write the code to open the workbook like below. Set appXL = CreateObject("Excel.Application") Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkbook.xls") Set wks = wkb.Worksheets(1) But if I copy the package and put it in another computer,mean the code need to be modified,isn't it?.And I don't want to get busy with that. I w...

Lookup window should default to first record in table
Right now if you click on the looking glass, it opens the window at the record that is already on the field prior to clicking the looking glass. It would be nice if it only did that on the change script so if you didn't change the field at all, when you open the looking glass it brings you to the first record in the table. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open ...

Query that can number records on the fly???
How can I make a query number each line in sequence of records found? Example if a query return 6 lines of records, I would like the query to return 1 for record 1, 2 for record 2 and so on. Example: 1 Red House 2 Blue House 3 Orange House 4 Green House 5 Yellow House You'd need to include a "ranking" field to give you that number. What is the SQL statement of the current query? How is the "number sequence" to be defined? -- Ken Snell <MS ACCESS MVP> "Michael Kintner" <michaelkintner@nospam.com> wrote in message news:13gn7ln...

Correct name to find Outlook Express 6 group please
Hi can anyone please tell me the correct name for the Outlook Express 6 news group please I have tried but my ISP does not carry descriptions. Thanks, Mick. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5.5x microsoft.public.windows.inetexplorer.ie6_outlookexpress for OE 6.x -- Russ Valentine [MVP-Outlook] Mick Cant wrote: > Hi can anyone please tell me the correct name for the Outlook Express 6 news > group pl...

How to select one record of each from a column
Hello, I have a large worksheet with data. Here are the columns (a, b & c): Order # Employee Hrs. 1000 Mike 2 2000 Mike 1 1000 Paul 2 3000 Rose 3 8000 Mike 1 3000 Mike 2 1000 Rose 1 4000 Paul 1 7000 Jhon 3 5000 Rose 2 1000 Jhon 3 6000 Mike 1 9000 Mike 3 1000 Mike 2 Is there a formula to select only one of each record on "Order #" (column a) and put them on another column (column d)??? Once the formula is created, column d should look like this: 1000 2000 3000 4000 5000 6000 7000 8000 9000 Help ...

how to record macro to roll over monthly data
hi , I need to create /record a macro to roll over a monthly data ,each month the 3 month ago figures must be added to the YTD.The remining data must be then moved one month ahead allowing space to include the new figures Thanks Anna Your description gives no idea of the overall structure and you are unlikely to get an answer better than Tools>Macro>Record new macro an record yourself taking the steps, but this is rarely repeatable each month as data is in different cells, etc. It sound like you may need an application written, but without stating yourself and trying to compl...

Struggling when there are no records
Forgive me...I know this has been covered extensively, but I still can't get it quite right. I have a query that is currently set up like this: FFAmended: IIf([tblAmended].[FF]=0,[tblContact].[FF],[tblAmended].[FF]) But what I really need it to do is to choose the data from tblContact if either A) the value in the tblAmended is 0 or B) there is no corresponding record in tblAmended. Both tables are related via a ContactID field. I know this should be simple and I've looked at several posts covering the NZ function, but I can't seem to apply it correctly to my s...

Can't find Publisher on my laptop
I am running Windows XP 2002, service pack 2. I've looked under control panel, add/delete programs and found that I have both Office Basic Edition 2003 and Office Standard Edition 2003 installed. 1st - how do I know if Publisher 2003 is a part of that install? 2nd - if it is installed, how do I access it? 3rd - if it is not installed, what should I purchase? Susan Melenchuk <SusanMelenchuk@discussions.microsoft.com> wrote: >I am running Windows XP 2002, service pack 2. I've looked under control >panel, add/delete programs and found that I have both Office Basic Edit...

Can I know wat is the command in powershell to find the program
Can I know wat is the command in powershell to find the program is installed or not in a system. On May 17, 8:24=A0am, Coolguy <Cool...@discussions.microsoft.com> wrote: > Can I know wat is the command in powershell to find the program is instal= led > or not in a system. for example: gwmi -comp computername win32_product | select name (though this only shows applications installed with windows installer) ...

find multiple data cells for one data source
I need a formula that will help me organize country codes. In Column A, I have 3 digit codes that have a country code, in column B, associated with it. The 3 digit codes appear multiple times in the spreadsheet. I need a way to show the 3 digit code and any and all of the country codes that are associated with it on one row. Example of my raw data below: ABF CN ABF MX ABF US ACC CN ACC IE ACC MX ACC TW ACC US Desired Output: Col A Col B Col C Col D Col E Col F ABF CN MX US ACC CN IE MX TW US Satisfactory out...

Adding new record to a form
I have a form with a command button to add a new record. The first field on the form is a lookup field where I would like to select an item to fill in the field, the rest of the fields require data entry. When clicking the add record button a blank form opens but I cannot add any data to it. I can see the option in the lookup field but cannot select one. On the other fields that require data entry, I cannot enter anything. On the forms property I have 'allow additions, yes'. How can I get this to work? Bg1 - What is the recordsource for your form? If it is a query, t...

Sharing or records in CRM 1.2
Dear Guys, We are facing a problem in MS CRM 1.2. Database and the CRM are in the same server. When a sales manager shifts the accounts from one sales person to another sales person. Older sales person still can see the accounts, allthough the ownership is changed. Lately we found that those accounts have sharing enabled and their names are still in the sharing. Now sales manager has to remove the share after assigning the records. This is fine if it is matter few records. but it is really a problem when bulk records are required to shift from one sales person to another. Kindly help...

best way of finding the differences in two bit of xml
hi all if i have two fragments of xml, whats the best way of doing a "diff" on them to find any differences TI b bg wrote: > if i have two fragments of xml, whats the best way of doing a "diff" on them to find any differences. There are .NET tools for this, online and for download at the following URL: http://apps.gotdotnet.com/xmltools/xmldiff/ -- Martin Honnen http://JavaScript.FAQTs.com/ ...

Error: "Search key was not found in any record"
Dear all, Recently, my Access 2000 is upgraded to XP version. After convert all 2000 mdb file to 2002, I've got a big trouble. 1.Path of all link tables cannot be changed. 2.Saving a new form dialog box repeat and repeat again when save a new form. 3.New saved form cannot be open. 4.Form cannot be deleted. A same error message is found in above case: "Search key was not found in any record" Could any one help me? Thank you very much! Billy "Billy" <billysoo@hotmail.com> wrote: >Recently, my Access 2000 is upgraded to XP version. >After convert all 20...

Find Message doesn't tell me folder path
When I use Find Message (Ctrl-Shift-F), the resulting list only says the folder name of the mail. If I've created subfolders, it doesn't tell me how to find that folder, i.e., the "path" to the folder. Is there a way to do this. I have a fairly deep structure, and sometimes I have to spend a lot of time finding the location of a found email. How do other people handle this problem? Thanks. -- roricka If the "Include subfolders" option is checked, the subfolder name should be displayed in the Results. roricka wrote: > When I use Find...

Cmd Click --- check the values first,append record and then delete them.
Hi all, I want to execute below code but it seems absolutely fail, please help: Private Sub AddSpec_cmd_Click() On Error GoTo Err_AddSpec_cmd_Click If IsNull(Me.Model) Then MsgBox " Enter Model Name", vbOKOnly, "Model name empty" End If Model.SetFocus If Inputvoltage.Value < 11 Then MsgBox " Input correct voltage rate ", vbOKOnly, "Input voltage" End If Dim db As Database Set db = CurrentDb db.Execute "Appendix model spec1_qry", dbFailOnError db.Execute &quo...

Counting number of records based on criteria
Hello, (Try again to get the question in the google-groups. Perhaps not pushed on the send button). For counting records as expression in a report in ms.access I'm using =count(*) which shows all records. Now I also want to know how many of them has registered a specific data in a specific field. I'm trying to use =count([Field1]="999") to count how many of the selection reported has registered data 999. This option isn't work. Can somebody give me the correct solution. thanks, Johan The 999 needs to go in the criteria for the query driving the...

How to find context menu!
Hi, I am trying to write down some codes to find out the context menu in VC++. However, the codes don��t work with Office and some application. If you right-click on Desktop or IE, you will see the ��Find popup menu�� is outputted. However, it doesn��t take place if right-click on Office. BOOL FindPopupMenu(IAccessible* paccParent) { HRESULT hr; long numChildren; unsigned long numFetched; VARIANT varChild,varRole; IEnumVARIANT* pEnum = NULL; IDispatch* pDisp = NULL; IAccessible* pCAcc = NULL; BOOL found = false; //Get the IEnumVARIANT int...

Record Money transactions in OL Journal?
Is there any way to get Money to automatically record transactions in Outlook's Journal? What I'm looking for is the ability to, say, write a check in Money, and have an item automatically created in Journal noting that a check for x amount was paid to y on this date. No. Money doesn't integrate with Outlook in this way (or much of any other). "t. wise" <terwise111@yahoo.com> wrote in message news:eUOsbFqlEHA.2968@TK2MSFTNGP14.phx.gbl... > Is there any way to get Money to automatically record transactions in > Outlook's Journal? > > What I&#...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

Grouping Mail Merge Records and Printing a Group Header
Greetings, I need to be able to group records from a data source and display a group header. How can I accomplish this in Publisher 2003? -- David Guthu Not sure I know what you are wanting to do. Try the catalog merge, in step five convert to publication, insert your group headings. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "David G." <DavidG@discussions.microsoft.com> wrote in message news:C9882A94-FC86-464A-BC1C-1F75D192E1F9@microsoft.com......

Finding links in multiple files
We are changing the structure of our network and want to move several hundred files from one drive to another, changing the pathing. For instance the file would go from X:\budget\*.xls to G:\vol4\budget\*.xls. Our fiscal people use links within and across workbooks and folders extensively. I am wondering if there is some way to run a text report that lists every link in every workbook in a particular folder and then update all those links without having to open each and every workbook. I posed this issue previously and was given wonderful utilities that work within a workbook, but I...

Outlook not finding connection or pop server
AAArrrrggggghhhhhh! Outlook keeps showing a message stating it can't find the server. I'v checked the account set up and it's set for the cable modem. When i does find it it keeps asking for my password. When I put it in an click ok, it asks for it again! Please help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Code to find all / next in a row of data
XL 2007 on XP Pro: I have a s/s with many names in cells along a row (in random order.) Some names may be the same. So I am trying to write a macro to "Find" the names that match one entered into a cell by a user. He should be presented with those that match and be able to select the one required. The active displayed cell should then move to that column in the row so that he can update data in the cells below. I have tried recording a macro using the "Find" in Excel but it does not record. Can anyone assist? Instead try data>filter>autofilter>...