complex query to pull unique values

I have an inspection table to keep track of rooms in various buildings. 
These rooms are shared with different supervisors. One inspection of a 
location would result in x # of inspection records. If the room was shared 
by 3 supervisors, the same inspection would generate 3 inspection records. 
Comment field on each record would be different according to the supervisor, 
were their employees following protocol, safety issues etc.

Since there are so many fields, trying to pull unique values is difficult, 
so I made a new field in the query which concatenates SupervisorID, Bldg & 
Room to make a single unique value. This part is fine.

I can pull unique Supervisor/location records with no problem. However, I 
only want the most recent inspection. I set the Totals part of the 
concatenated field to "Group By" and the rest to "First". Even though the 
dates are sorted as descending in the query, it pulls the First date from 
the original table, which by default is sorted ascending. So essentially I 
get the most outdated inspection, not the most recent.

I've tried a two query approach. Make the first query, just sort the dates 
descending. Then make a new query based on the first, with the concatenated 
fields and so on. It gives the same result.

I've tried putting my date field as the first column in the query. It gives 
the same result.

How can I solve this date sorting issue?



0
JR
6/7/2010 1:24:38 AM
access.queries 6343 articles. 1 followers. Follow

6 Replies
3071 Views

Similar Articles

[PageSpeed] 43

Of course 5 minutes after I post, I found a solution but I'm not sure how it 
works.

Same query, except the for the date instead of First, I put Last and it 
works. I get the most recent inspection record per Supervisor/Room combo. 
Great.

But when I look at my query results, Inspection ID = 2, but its date is not 
the same date Inspection ID = 2 is in the table.

Inspection ID is pk for the table, autonumbered.

Query result: InspectionID = 2, Name/Room = Smith 123, Date = January 6, 
2010
Table row: InspectionID = 2, Name/Room = Smith 123, Date = June 6, 2009


So if i want to go back to see the details from that particular inspection 
(the most recent of Supervisor/Room), the Inspection ID is incorrect.

Not quite gettin' it........



"JR" <iloveVBA@gmail.com> wrote in message 
news:%232BNd$dBLHA.1888@TK2MSFTNGP05.phx.gbl...
>I have an inspection table to keep track of rooms in various buildings. 
>These rooms are shared with different supervisors. One inspection of a 
>location would result in x # of inspection records. If the room was shared 
>by 3 supervisors, the same inspection would generate 3 inspection records. 
>Comment field on each record would be different according to the 
>supervisor, were their employees following protocol, safety issues etc.
>
> Since there are so many fields, trying to pull unique values is difficult, 
> so I made a new field in the query which concatenates SupervisorID, Bldg & 
> Room to make a single unique value. This part is fine.
>
> I can pull unique Supervisor/location records with no problem. However, I 
> only want the most recent inspection. I set the Totals part of the 
> concatenated field to "Group By" and the rest to "First". Even though the 
> dates are sorted as descending in the query, it pulls the First date from 
> the original table, which by default is sorted ascending. So essentially I 
> get the most outdated inspection, not the most recent.
>
> I've tried a two query approach. Make the first query, just sort the dates 
> descending. Then make a new query based on the first, with the 
> concatenated fields and so on. It gives the same result.
>
> I've tried putting my date field as the first column in the query. It 
> gives the same result.
>
> How can I solve this date sorting issue?
>
>
> 


0
JR
6/7/2010 1:42:34 AM
On Mon, 7 Jun 2010 21:23:45 -0400, "JR" <iloveVBA@gmail.com> wrote:

>I have an inspection table to keep track of rooms in various buildings. 
>These rooms are shared with different supervisors. One inspection of a 
>location would result in x # of inspection records. If the room was shared 
>by 3 supervisors, the same inspection would generate 3 inspection records. 
>Comment field on each record would be different according to the supervisor, 
>were their employees following protocol, safety issues etc.
>
>Since there are so many fields, trying to pull unique values is difficult, 
>so I made a new field in the query which concatenates SupervisorID, Bldg & 
>Room to make a single unique value. This part is fine.

Fine... but unnecessary. You can group by up to TEN fields; it is neither
necessary nor particularly helpful to create a redundant concatenated field.

>I can pull unique Supervisor/location records with no problem. However, I 
>only want the most recent inspection. I set the Totals part of the 
>concatenated field to "Group By" and the rest to "First". Even though the 
>dates are sorted as descending in the query, it pulls the First date from 
>the original table, which by default is sorted ascending. So essentially I 
>get the most outdated inspection, not the most recent.

First is misleading. It returns the first record *in disk storage order* -
essentially an arbitrary, meaningless record.  A Subquery with a criterion
such as

=(SELECT Max([datefield]) FROM tablename AS X WHERE X.SupervisorID =
tablename.SupervisorID) 

will be a better approach.

>I've tried a two query approach. Make the first query, just sort the dates 
>descending. Then make a new query based on the first, with the concatenated 
>fields and so on. It gives the same result.

Because of the same problem - FIRST isn't the "first" in the way you would
think.
-- 

             John W. Vinson [MVP]
0
John
6/7/2010 2:28:38 AM
On Mon, 7 Jun 2010 21:41:24 -0400, "JR" <iloveVBA@gmail.com> wrote:

>Same query, except the for the date instead of First, I put Last and it 
>works. 

sheer coincidence and good luck. Use the subquery instead.
-- 

             John W. Vinson [MVP]
0
John
6/7/2010 2:29:17 AM
in the query view or sql view?



"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:4bmo06dietl01r3g02nm25rijo4a2jlhjj@4ax.com...
> On Mon, 7 Jun 2010 21:41:24 -0400, "JR" <iloveVBA@gmail.com> wrote:
>
>>Same query, except the for the date instead of First, I put Last and it
>>works.
>
> sheer coincidence and good luck. Use the subquery instead.
> -- 
>
>             John W. Vinson [MVP] 


0
JR
6/7/2010 3:14:28 AM
On Mon, 7 Jun 2010 23:13:18 -0400, "JR" <iloveVBA@gmail.com> wrote:

>in the query view or sql view?

You can use either, but in the query grid (which is NOT the query, just a tool
to help build SQL) you'll need to put the subquery - as a SQL string in
parentheses - in the Criteria box. You'll need to adapt the fieldnames and
tablenames to match your actual tables, which I cannot see.
-- 

             John W. Vinson [MVP]
0
John
6/7/2010 5:45:05 AM
Absolute brilliance!

Thanks so much, John.


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:to1p06pdr15b942lo90r488c20q0hr4ov6@4ax.com...
> On Mon, 7 Jun 2010 23:13:18 -0400, "JR" <iloveVBA@gmail.com> wrote:
>
>>in the query view or sql view?
>
> You can use either, but in the query grid (which is NOT the query, just a 
> tool
> to help build SQL) you'll need to put the subquery - as a SQL string in
> parentheses - in the Criteria box. You'll need to adapt the fieldnames and
> tablenames to match your actual tables, which I cannot see.
> -- 
>
>             John W. Vinson [MVP] 


0
JR
6/7/2010 10:45:29 PM
Reply:

Similar Artilces:

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Want to make complex header on 2nd and 3rd page
I need to make a quotation which consists of a first page, thats fully on its own and then two pages which has a same header. The header, though, consists of quite a lot of text and also a company logo and that was byond the header functionality. How to get the 2nd and 3rd pages with a complex upper part? Bart Exc 2003 ...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...

Access 2007-Table not displaying the values from a combo box
I have built a database to schedule senior/disabled transportation appointments. I have created two tables and associated forms for data entry. I placed a combo box in the Schedule form so end users choose a client and fields complete on the form with client name, address, city, and home phone number. I did instruct Access to store the data in the SeniorIntakeID field in the Schedule table. I wrote a report that staff execute to see what is scheduled for a given date, for each driver. On the report I want to see the actual client's name, address, city, and home phone n...

How can I pull out position 4 in a string?
Column A contains a string MyString aaaaaa bbbbbb cccccc dddddd eeemee fffmff gggmgg I want the value of B1 to be the value of A1 unless the 4th character in A1 is a "m". So column B would be B1=aaaaaa B2=bbbbbb B3=cccccc B4=dddddd B5 = empty cell B6 = empty cell B7 = empty cell What's the simplest way to do this? =IF(MID(A1,4,1)="m","",A1) -- Regards, Peo Sjoblom "Stephanie" <nowhere@microsoft.com> wrote in message news:OjT2P9s2DHA.2528@TK2MSFTNGP10.phx.gbl... > Column A contains a string > > MyString > aaaaaa > b...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

Reprsent negative values in excel charts
Hi, Can someone help in how to reprsent the negative values in the chart and which is the best chart to represent it? What type of chart are you making? In a line, column, bar, XY, or area chart, the negative values are merely plotted on the other side of an axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "uha" <uha@discussions.microsoft.com> wrote in message news:166604B5-A331-4EE6-8010-D921C1EEA017@microsoft.com... > Hi, > Can someone help in how to reprsent t...

How to find value that is bigger or smaller than 2SD?
I am new to excel, my question may be silly for you. But I really don't know how to do it and hope to get your help. I need to do statistic analysis of my data table which has lots of columns. First of all, in each column, I want to find and highlight the value that is bigger than mean+two times standard derivation, or smaller than mean-2* standard derivation. Do you have any suggestions about how to do it? Thanks alot, ming Hello, Maybe something like this ... Sub HighlightForMePlease() Dim rngLook As Range, c As Range, firstAddy As String Dim lngSTDEV1 As Double, lngSTD...

how to read textbox value from vb.net
i have added a textbox from developer>control tab>activeXcontrol>textbox in word document 2007. the content of the textbox includes "my name is supriya upadhyay". now i want to open this word file from vb.net and wanna read the content of the textbox. plzzzzzzzzzzzzzz help me out ...

Weighted Avg
I maintain a spreadsheet for a telephone hotline. We track total number of calls received (column F) as well as average time to answer the call (column J). To analyze the month's average time to answer, I use the following weighted average formula: SUMPRODUCT (J5:J26,F5:F26)/SUM(F5:26). I have 2 questions relating to this: 1) Is the above formula correct to determine the average speed to answer? I've assumed the weights to be the total number of calls received. 2) I have a different spreadsheet for each month (Jan, Feb, etc.). How can I properly show the weighted average as...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

union queries
I need to build a query which combine records from a number of similar tables. Building a union query works great with "normal" records. Problem is, these tables contain attachments in some fields and MS ACCESS 2007 treat these fields as multi-value fields and does not allow building APPEND or UNION queries with multi-value fields. I am so frustrated with this, because I would like to base my searches and some other queries on this. PLEEEESe help Cheers Dawie Theron On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron <DawieTheron@discussions.microsoft.com&g...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...

FRx
Is there any way to import values to a column of FRx 6.7 report from excel/doc file? Thanks in Advance, Arun. You can't "import" data but you can include data from a spreadsheet in an FRx report. See Chapter 4 of the FRx Report Designer User's Guide. -- Charles Allen, MVP "Arun" wrote: > Is there any way to import values to a column of FRx 6.7 report from > excel/doc file? > > Thanks in Advance, > Arun. ...

linked values not displayed unless source file open
i have an excel sheet with cells linked to cells in another seperate excel file. Excel will not show the values unless I have the source file open. this always used to work with the source file closed. Now the target file shows #value. this is a major nuisance if there are 7 or 8 linked files! please help. Sandy There are some worksheet functions that don't work with closed workbooks. =indirect(), =countif(), =sumif() are a few. If you share your formula, you may find that there's an alternative that you can use. Sandyc wrote: > > i have an excel sheet with cells l...

2 queries, a date range and a report?!
I'm pretty much a novice access user, and am designing my DB with the frequent assistance of these forums. However I'm now trying to do something that I can't find any reference to - Can anyone advise? I have a report that is to be printed from a form using a print control button. The report consists of 2 queries which look up 2 different types of "item" by date range. I want to be able to prompt the user once for the start and end date. If I put a start/end request on both of the queries, then the user has to enter the dates twice, and when you choose...

Some queries of User Interface Thread
Hello All Recently I started working on User Interface thread , But certain things are quite confusing. Hope I will get some clarification from all of you. 1: The thread does not receive user message sent by PostThreadMessage when I am doing some lengthy operation in Run() method (overrided Run() function ) , Can you tell me why it is so ? I have written a handler for user defined message, But it's of no use when I am doing lengthy operation in Run(). 2: I read that the Run() function provides a default message loop for user interface thread. How Run() function works internally ? How it...

Wretched Queries!!!!!!
Hi,In my database each entry has several scores assigned to it (fordifferent attributes). Each score is in the 1 -4 range.I would like to construct a query that will select those entries thathave even so much as a single attribute that scores below a 4 and, ifpossible, only return the attributes that fell below standard (4).The database will have each employee (entry) assessed on a dozen or sodifferent attributes, each of which will be rated with a score of 1 -4. I would like to be able to find out which employees had a score inany attribute below 4 and which attributes those were, and only th...