Total Query - How to get unique value in field (1 Viewer)

[Rx_]

One table - need to filter by and reutrn a min ordinal value (Requested, Moved, Final) for each ID_Wells where SHLBHL = 'SHL'

This part of the totals query (The first 3 fields) works perfectlly and returns the right count.

The problem is obtaining the 4th column that is an autocounter.
The Requested, Moved, Final are rarely entered in an actual order.
So, First/Last Max/Min won't work.
I need the unique row's ID_SHLBHL that was filterd to ID_Wells, REQ_FIN, SHLBHL

The 4th column ID_SHLBHL will be used to relink to the unique row for the rest of the data.

Rule: There may only be zero or one FINAL (enforced by form). There may be zero or many MOVED, there may be zero or many Requested
If - Final - pick that. If no Final an 1 or more Moved - pick that. If no Final and no Moved and 1 or more Requested - pick that.

I can write code to create this, but it would take a while to run on a few hundred thousand records.

 

[plog]

Can you post some sample data from your data source and what you expect returned from your query based on that sample data? Include a few cases.

 

[Rx_]

Thanks! excel attached

I left out the column that repeats SHL -
It is retreiving the unique identifier that may not be in entry order

I am writing a vba function to meet a deadline. Will need to convert it to a TSQL script later. It just seems that SQL lanugage would have some way of identifying a record in a group - and then bring back the unique value based on the group.

 

[plog]

That's no help. I need sample starting data and then what you expect as the result. Your file makes no sense to me, I have no idea if you posted the sample data, the final result or some hybrid.

 

[Rx_]

.xls is same name - reformatted the informaton
Left side - the table
Right side - the expected output

 

[plog]

I think your expected data is a little off--you have ID_Wells=70 twice in the expected results data. Other than that, I was able to match your data with 3 sub queries. I used the field names in the Excel file and I used Wells_SHLBHL as the table name. Additionally, you will need a new table to prioritize your Req_Fin values. Below are the fields and data you need:

Req_Fin_Type, Req_Fin_Priority
Final, 1
Moved, 2
Requested, 3

Name the above table Req_Fin_Priorities. Below are the queries you need and what you need to name them:

sub_1

SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL
FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type
WHERE (((Wells_SHLBHL.SHLBHL)="SHL"));

sub_2

SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHL
FROM sub_1
WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]","sub_1","[ID_Wells]=" & [ID_Wells])));

sub_3

SELECT sub_2.ID_Wells, sub_2.MinPriority, Max(sub_2.ID_SHLBHL) AS MaxIDSHLBHL
FROM sub_2
GROUP BY sub_2.ID_Wells, sub_2.MinPriority;

mainQuery

SELECT sub_1.ID_Wells, sub_1.Req_Fin, sub_1.ID_SHLBHL, sub_1.SHLBHL
FROM sub_3 INNER JOIN sub_1 ON (sub_3.MaxIDSHLBHL = sub_1.ID_SHLBHL) AND (sub_3.MinPriority = sub_1.Req_Fin_Priority) AND (sub_3.ID_Wells = sub_1.ID_Wells);

Essentially, its an iterative process--sub_1 finds all the 'SHL' records and assigns a priority value to the Req_Fin field; sub_2 finds the lowest priority of each ID_Wells; sub_3 finds the highest ID_SHLBHL of all the records with that lowest priority; the main query links back to sub_1 to get the data from the record that meets all the criteria found in sub_2 and sub_3.

 

[Rx_]

Genius what you did with the DMIN.
I had created that table in order to sort by numeric, but as the query got more complex, it would seem to come undone. This is a basic foundation table for SHL then BHL - for each level of government - to be assembled in a cross-tab.

I finished up a Code solution to accomplish the same result for a deadline. But, look forward to using this on the SQL Server side of the project.
Thank you.

 

[plog]

No problem, if you have any issues with it, just post back here and we can work them out.

 

[Rx_]

Was trying to combine the first two together into a subquery (into sub2). Can't seem to get it to work. If you have any ideas on that.
Thanks!
SELECT ID_Wells, Req_Fin_Priority AS MinPriority, ID_SHLBHL
FROM
(SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL
FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type
WHERE (((Wells_SHLBHL.SHLBHL)="SHL")))
WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]",
(SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL
FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type
WHERE (((Wells_SHLBHL.SHLBHL)="SHL")))
,"[ID_Wells]=" & [ID_Wells])));

 

[Rx_]

Rebooted my system, sub_2 kept going into an endless loop.
Changed the following Where statement - it worked fine.
SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHL
FROM sub_1
WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]","sub_1","[Sub_1].[ID_Wells]=" & [ID_Wells])));

update: tried it the other way. Evidently a reboot was needed. However, this did run too.

 

[Rx_]

Here is a problem I created for myself. Instead of naming the query Sub_1, it was given a name 01_GISSHL
Tried to search and replace the Sub_2 query with this change. It failed.
e.g. SELECT sub_1.ID_Wells, became SELECT 01_GISSHL.ID_Wells
This would not run.
Solution: Replaced all 01_GISSHL with [01_GISSHL] and it ran fine.

The VBA solution I wrote verified your DMin is spot on perfect! Our numbers for each category match.

Here is somehthing interesting. They both take some time to run with a rull set of records. Was expecting SQL to be a little faster. The Dmin must use a cursor too.
But, this code will soon live on SQL Server. Will come back and make a note of the difference.

 

[Rx_]

In MS Access, we have DMin function, do we have any equivalent function in SQL Server?
No, it is uique to MSACCESS, in TSQL it needs to use this format:

SELECT Min(column) FROM table WHERE condition

Just wondering if this would be a sub-query?