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

Similar Articles

[PageSpeed] 13

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:

Displaying parameter query dates in a report?
I want to base a report on a query which used parameters to select dates. How can I include the dates I have chosen in my report? On Wed, 4 Jul 2007 15:40:50 +0100, C Tate wrote: > I want to base a report on a query which used parameters to select dates. > How can I include the dates I have chosen in my report? Add an unbound control to the report header. Set it's control source to: ="For sales between " & [Enter Start Date] & " and " & [Enter End Date] The text within the brackets must be identical to the bracketed text in the query parameter...

Default Value for bits
Hi, I would like to know where is the default value of bit fields are kept in the database. I found in Metabase, in table attribute, a column DefaultValue but this column is not working, if I change in CRM the predefined value of a bit field nothing happens to the defaultvalue in Metabase it's always with NULL value. This is strange because if I change in CRM the bit value it saves my choice. But WHERE?? There must be another place in the database that I can't find... Thanks It's stored with the form definition, which you can find in the FieldXML (or possibly FormXML) field...

SUM cells with values within intervalls?
Hi, What is the best approach - or maybe you have a solution? I need to check a column with values and sum the cells with values within intervals, like 0-100000, 100001-500000 and 500001-1000000 There is a column with values within these intervals and as result I want to have three rows with sums of these intervals. I tried pivottables, but it seems not to be able to create intevals in it I have tried to create array formulas that sum the matrix values if interval is true, but bite the dust. Can't find a formula that return a matrix/areas with cells within the intervalls(to use wi...

Public Folder tree
We have some public folder trees that are getting 5-10 levels deep, as they are by default matching our projects drive folder structure. I am just wondering, is there a limit to how deep and wide a public folder matrix can/should go? I can easily see a structure of 20 projects, each as many as a hundred subfolders in a tree up to 10 levels deep, with literally thousands of files in each project. Am I courting disaster? Thanks, Gordon On Tue, 1 Nov 2005 11:02:09 -0800, Gordon Price <GordonPrice@discussions.microsoft.com> wrote: >We have some public folder trees that are getting...

Complex formula change making m,e crazy
I have thsi formula... myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN""," _ & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))<999," _ & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN"...

Printing Complex Workbooks
Hi -- I am wondering if anyone has experienced this issue. Some of my users have created very complex workbooks, with different page orientations, formulas, colors and different page sizes. When these documents are printed (NDPS), Excel crashes. This only happens when the entire workbook is printed. Not the active page. The only solution is to use an older version of the Novell print driver - NDPPNT.DLL. Environment specs: Windows 2000 Excel 2000 Netware 5.1 SP6 Novell Client 4.90 HP PCL 5E (latest firmware on printers, latest version of PCL 5E driver) Novell's stance is if pr...

How do I find missing value?
At any time I would need to enter the three values I have to solve for the fourth. When I have Margin, Index and rate I will need teaser. When I have Teaser, Index, and Rate I will need Margin. Teaser=(INDEX + Margin)-Start rate' Margin=(Rate + Teaser)-INDEX' Index=(Rate + Teaser) - Margin Rate = Will not need to solve for this! What is your question? Are you saying that you have 4 columns labeled Teaser, Index, Margin, and Rate, and you want to solve for the fourth one given the other 3? Please post back and clarify what you want/need. HTH Otto "edwinvo...

How to get the info from form to query to report
I have a form that uses 5 tables for the information so I built a query to have the info all in one place. I also want to use the submit button to send the information to a report to be emailed, right now the report comes in blank. Please help I am fairly new to access and trying to build this for work. Base you report on a query. Use the form reference as criteria in the query like this --- [Forms]![YourFormName]![YourObject] [YourObject] is displaying the data from the 5 tables. -- KARL DEWEY Build a little - Test a little "sandyL" wrote: > I have a form that us...

a running sum (by date) in a query?
Is this possible? I have dates (some of which are the same) and I would like to use them to create a running sum of another column in my query. Is this possible? On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote: > Is this possible? I have dates (some of which are the same) and I > would like to use them to create a running sum of another column in my > query. Is this possible? Sorry, not only do i need to create a running sum by date but it also has to be PER sales person. So even though all the sales people are in one table and I am querying the table different ways, I need to m...

Automatically backing up Mailboxes Query
I am using Exchange 2000 SP3. I have been manually backing my Exchange Mailboxes using ExMerge. Is there a batch file (.bat) to do it automatically. IE One that doesnt add to the previous pst file. It would have to overwrite the previous pst file as space is an issue. Any help would be appreciated. Joe. ...

query is too complex error message
does anyone know if access 2007 has increased the capabilities of their queries? i have a query that was built in the 2003 version that keeps giving me the subject line error message. the query includes 7 fields that have a criteria requirements. it works for about a month and then just stops working. any help would be appreciated. thanks,andrea-- Andrea On Tue, 27 Mar 2007 18:13:51 -0700, Andrea <Andrea@discussions.microsoft.com>wrote:>the query criteria is as follows:>>([forms]![frmSearchByMultipleFunctions]![ThisValue5] >or([forms]![frmSearchByMultipleFunctions]![ThisVa...

Reset option buttons to default value
I'm setting up a questionnaire on a worksheet and have assigned pairs of option buttons as Yes / No to compile answers to a series of questions. I have 10 pairs of option button on one sheet, each with their own unique name. The option buttons are Active X. However only one option button at any time can be checked. Why is that and what can I do to prevent it? I want one button of each pair to be checked as an individual works through the questions. Second related question: At the end of the questionnaire I want to reset all the option buttons to their default value, which for the Yes...

Why won't pasted values from a formula appear in a pivot table
I am trying to make a pivot table from an array of information that was the result of formulas on another worksheet page. I used a macro to copy these formulas on the other worksheet page, then paste their values in the array that I need a pivot table from. The pivot table will not sum any of the values in the array and it does not combine cells in one column that are alike if their corresponding cells in the adjacent column are different. Everything works if I retype the data instead of copying and pasting. This is not an option becuase there are several thousand lines of data. Th...

Using complex formula in Diagram,AutoShape,WordArt,Organization Ch
Download and free try AddinTools Create from http://www.addintools.com Make you using formulas and cell reference in Excel objects: diagram, autoshape, wordart, and organization chart. To get thousands of Diagrams, AutoShapes, WordArts, Organization Charts, Charts, Reports, Tabs, Cards, and Forms that contain different values in several minutes! The quantity of results that you can produce depends on the volume of data that saved in worksheets. Fully Utilize The Data In Workbooks. The data in results may source from the same worksheet, and may also source from one or more workbooks and...

Duplicate Records in Query
I have five names David Joe Smith Hank Jim I want the query to return. David David Joe Joe Smith Smith Hank Hank Jim Jim Thanks, David I don't understand. While I do see that there are two of each, the order of the names is different between starting condition and ending condition... and the ending names are not sorted alphabetically. How did you come up with the sort order? Will you always only want 2 of each? Could it be three? Regards Jeff Boyce Microsoft Office/Access MVP "gumby" <david.isaacks@va.gov> wrote in message news:1175729567.219968.146140@q75g2...

Update Query
Hello, For every record in a table I need to update one of four Yes/No check box fields based upon date span. If the "In Date" is in the same month as "Serve D" or "Return D" field place a check in the "DispodWithinSameMonth" yes/no field. If the "In Date" is within 30 days of the "Serve D" or "Return D" field place a check in the "DispodWithin30Days" yes/no field. If the "In Date" is within 60 days of the "Serve D" or "Return D" field place a check in the "Dis...

Pulling my hair to consume custom webservice in ms crm
:( i m pulling my hair to consume custom webservice in ms crm . is there anybody to help me(telling me the complete process) to consume custom webservice in ms crm 4.0 . thanks in advance!!!!!!!!!!! "Mahain" wrote: > :( i m pulling my hair to consume custom webservice in ms crm . > > is there anybody to help me(telling me the complete process) to > consume custom webservice in ms crm 4.0 . > > > thanks in advance!!!!!!!!!!! Hi there, I'm also trying to consume a custom webservice in ms crm. I can browse to the webservice in my web broswer but i am...

Query based distribution list problem
Running exchange 2003. Our query based lists work fine except the sender gets an error message for accounts that have expired. Is there a way to get around this? TIA...Bob Did you mean accounts that are disabled? Accounts with expired passwords shouldn't have an issue receiving mail, afaik. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message news:Xns974A7C1C06982bobarcabca@207.46.248.16... > Running exchange 2003. Our query based lists work fine ...

Automatically increment a unique number when filling in a userform in excel
Hi all, I have created a userform in Excel to enter data into a worksheet. In Column A, I have a unique number beginning at number 1 and incrementing everytime data is entered into Column B, C, etc. I would like to have a unique field in the userform that will increment the number in Column A (with no input from the user) and for the user to enter the remaining data into the userform that will fill in Columns B,C, etc for the row as the unique incrementing number. Is this possible? Thanks in advance for helping. Calculate it with iId = Cells(Rows.Count,"A").End(xlUp...

External web query
I am not a programmer and a new excel user. I have an external web query that I am using tracking external links from a web site. I need to create an additional column with the web page info that relates to where the link is coming from. I tried but when I refresh the data it inserts cells with the any new links listed but then that throws off my additional column info. I looked in Data Range Properties and checked the middle option but that doesn't work the way I thought it would. Suggestions? -- Message posted via http://www.officekb.com ...

need help..Simple, compound, complex, compound-complex examples
Need to write a paragraph on the four sentence types...Just need to have an example of each type....please someone help!! Thanks On 30/05/2010 4:18 PM, Peggylynne wrote: > Need to write a paragraph on the four sentence types...Just need to have an > example of each type....please someone help!! Thanks See http://www.eslbee.com/sentences.htm and some of the other sites that appear when you google for the subject of your post. -- Hope this helps, Doug Robbins - Word MVP Please reply to the newsgroup unless you want to obtain my services on a professional basis. ...

Parameter query with expressions and calculations
Hi Everyone, I am using Access 2003. I have been working on this query for a few weeks using my Access book, examples from this group and google searches. I am pretty close to getting what I want but I am getting frustrated and mixed up and I can't seem to figure it out. I'm starting to get mixed up trying to explain this too so I am going to post and let you guys help me organize my thoughts...geesh! In regular words here is what I want. My database is of patients and information about the therapy we are giving them and how long they are on that therapy. I w...

How do I not plot zero values that result from a formula in Excel?
I have a data table with two rows. One row calculates a percent complete value from the other row. The data is monthly. I am charting the results. The percent complete for a month is blank until I fill in the data for the month. The chart is plotting the blank percent complete as a zero. I set the chart options already to not plot blank cells, but I'm guessing since there is a formula in there it doesn't think that the cell is blank. What can I do? The problem is that "" looks like a blank, but Excel does not treat it as a blank. Excel treats it like any other text, an...

Stacking queries and Calculations
Hello All I have a general question about how best to do something. I have a lot of queries in my app that do calculations at some point in the stacks of queries to get results i need for various functions in the app. is it better to pull fields and do calculations later? meaning if i have a stack of 3 queries should i just pull fields in the first query and then have the second query pull the information from the first and then in the third do all my calculations? or would it be better to do a calculation right away and then just pull that through my data sets? the reaso...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...