Query - Show rows...

[paalschau]

I hope someone can help me with this...

I have a query where I wish to show only rows like the number displayed in the column for [Uttr1] for current [User]. The query must display those rows with lowest sum in [Uttr2]. If number in [Uttr2] is equal to another row for this user - it shall display just one of them…

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205 (Show)
xx1 Ole Nilsen 3 450 (Show)
xx1 Kari Karlsen 3 570 (Show)
xx1 Hans Hansen 3 570
xx2 Per Persen 1 145 (Show)
xx2 Nils Nilsen 1 538

Like this:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx2 Per Persen 1 145

I like to do this in a query – not in a report.

Thanks in advance

 

[Pat Hartman]

This is a two step process. First you need to get the minimum amount. Then you need to get related information. Since the second query may return multiple rows, you need some way of selecting one of them. I chose First(). You could use Last() if you prefer.

Query1:
Select User, Uttr1, Min(Uttr2) as MinUttr2
From YourTable
Group By User, Uttr1;

Query2:
Select q.User, q.Uttr1, q.MinUttr2, First(t.Customer) As FirstCust
From query1 as q Inner Join YourTable as t on q.User = t.User and q.Uttr1 = t.Uttr1 and q.MinUttr2 = t.Uttr2
Group by q.User, q.Uttr1, q.MinUttr2;

 

[paalschau]

Hey Pat !

The result is not like expected…

Your query2 shows:

User Uttr1 MinUttr2 FirstCust
xx1 3 206 Ole Olsen
xx2 1 146 Per Persen

I want this result:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx2 Per Persen 1 145


I would like the query to show number of rows like the number displayed in the column for [Uttr1]. If [Uttr1] show (3) then three customer shall display (even if it’s four rows) – if [Uttr1] show (1) then one customer shall display. But… if the number in [Uttr2] is like another number for the same [user] – se the example above for user xx1 – four rows – just one off these shall display – but the total shall always be like the number in [Uttr1] for current user…

I hope this was clearer – and that you still can help me

 

[paalschau]

Still needing help!
Please

 

[RichMorrison]

In the first query, instead of Group By User, try Group By Customer.

In the second query, join on Customer instead of User.

RichM

 

[Pat Hartman]

You said

The query must display those rows with lowest sum in [Uttr2].

. That was the purpose of the first query. It seems that your criteria is really only

If number in [Uttr2] is equal to another row for this user - it shall display just one of them…

. Therefore, you don't need query1

Query:
Select User, Uttr1, Uttr2, First(t.Customer) As FirstCust
From YourTable
Group by User, Uttr1, Uttr2;

 

[RichMorrison]

Pat,

I am interpreting the original question as:
how do I find the lowest "uttr2" for each Customer.

Your first reply addressed that question but grouped by the non-unique User number.

I suggested the same approach but grouped by Customer instead.

That would seem to do it.

RichM

 

[Jon K]

I don't think it can be solved using queries.

The number of records to be retrieved for each user is determined by the number in Uttr1, but there is no means of relating the queries to the numbers in Uttr1.

 

[paalschau]

Thanks for your interest in my problem – but the problem is still not solved…

The problem is like Jon indicates – “The number of records to be retrieved for each user is determined by the number in Uttr1”… and Uttr2 if same number (se my previous explanations)…

I need the result in a query because another program is depended on Access and a query – this program shall use the result.

Still hoping for help

 

[antomack]

Should 'xx2 Nils Nilsen 1 538' not be showing as well since he is not a repeat of CUSTOMER or of UTTR2

 

[paalschau]

That’s right!

Out of this table:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx1 Hans Hansen 3 570
xx2 Per Persen 1 145
xx2 Nils Nilsen 1 538

I want this result:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx2 Per Persen 1 145

 

[antomack]

Assuming that you want the results below then the following solution should work.

Required Results
User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx2 Per Persen 1 145
xx2 Nils Nilsen 1 538

First off create a make table query:
SELECT Table6.User, Null AS Customer, Table6.Uttr1, Table6.Uttr2 INTO Table7 FROM Table6 GROUP BY Table6.User, Null, Table6.Uttr1, Table6.Uttr2;

Then create the following update query to update the Customer details with the first customer that matches the record.
UPDATE Table6 INNER JOIN Table7 ON (Table6.Uttr1 = Table7.Uttr1) AND (Table6.Uttr2 = Table7.Uttr2) AND (Table6.User = Table7.User) SET Table7.Customer = [table6].[customer];

Running the two queries will result in the 'Required Results' above from the data you gave. All you need to do is change the table names to whatever names you have.

 

[paalschau]

I like your thinking – but I don’t wont “xx2 Nils Nilsen 1 538” to show… because the number in [Uttr1] = (1) for this user (the number of records to be retrieved for each user is determined by the number in [Uttr1]).

This is my table:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx1 Hans Hansen 3 570 (this or the above customer shod not show because [Uttr1]=3 and same number in [Uttr2].
xx2 Per Persen 1 145
xx2 Nils Nilsen 1 538 (not show because [Uttr1]=1 and higher number in [Uttr2])

This is the result:

User Customer Uttr1 Uttr2
xx1 Ole Olsen 3 205
xx1 Ole Nilsen 3 450
xx1 Kari Karlsen 3 570
xx2 Per Persen 1 145

Still hoping for help

 

[antomack]

Going on what you have detailed it appears that essentially you are attempting to get the first n records for each user grouped by UTTR2 descending. The n is determined for each user from what is in UTTR1. Thus the following function should do this for you. Firstly you will have to create a copy of the table you have, structure only.

All you need to do is replace 'Table6' with your current table name and 'Table7' with the table name you want fopr your results.

Function DoTopVal()

Dim db As DAO.Database
Dim qdfResult As QueryDef, qdfAddRes As QueryDef, qdfUpdRes As QueryDef
Dim rstUser As DAO.Recordset
Dim strSQL As String, strTop As String

Set db = CurrentDb

' Table7 is just a copy of your original table
db.Execute "DELETE * from Table7"

' get list of users
strSQL = "SELECT Table6.User, Table6.Uttr1 FROM Table6 GROUP BY Table6.User, Table6.Uttr1;"
Set rstUser = db.OpenRecordset(strSQL)
rstUser.MoveFirst
Do While Not rstUser.EOF
strTop = "Top " & rstUser.Uttr1
strSQL = "INSERT INTO Table7 ( User, Customer, Uttr1, Uttr2 ) "
strSQL = strSQL + "SELECT " & strTop & " Table6.User, Null As Customer, Table6.Uttr1, Table6.Uttr2 FROM Table6 "
strSQL = strSQL + "GROUP BY Table6.User, Null, Table6.Uttr1, Table6.Uttr2 "
strSQL = strSQL + " HAVING Table6.User='" & rstUser.User & "' ORDER BY Table6.Uttr2;"
Set qdfAddRes = db.CreateQueryDef("AddResults", strSQL)
qdfAddRes.Execute
db.Execute "drop table AddResults"
rstUser.MoveNext
Loop

' update results table with customer details
strSQL = "UPDATE Table6 INNER JOIN Table7 ON (Table6.Uttr1 = Table7.Uttr1) AND"
strSQL = strSQL + " (Table6.Uttr2 = Table7.Uttr2) AND (Table6.User = Table7.User)"
strSQL = strSQL + " SET Table7.Customer = [table6].[customer];"
Set qdfUpdRes = db.CreateQueryDef("UpdateCust", strSQL)
qdfUpdRes.Execute
db.Execute "drop table UpdateCust"

End Function

 

[paalschau]

First tank’s antomack !

Now it’s almost right… (See last example below)…

If [Uttr1] is like (4) – and if it’s just four customers - then four customers shod be returned – even if same number in [Uttr2]… Your functions returned 3 ??

If you manage this as well – the problem is solved…

User Customer Uttr1 Uttr2
xx1 Ole Olsen 4 205
xx1 Ole Nilsen 4 450
xx1 Kari Karlsen 4 570
xx1 Hans Hansen 4 570

 

[antomack]

Assuming the following rules
1) If number of customers for each user is equal to UTTR1 then show all customers
2) If not equal then show Top n, (n = UTTR1), for each user grouped by UTTR2

the following function should do the job

Function DoTopVal()

Dim db As DAO.Database
Dim qdfResult As QueryDef, qdfAddRes As QueryDef, qdfUpdRes As QueryDef
Dim rstUser As DAO.Recordset
Dim strSQL As String, strTop As String
Dim i As Integer

Set db = CurrentDb

' Table7 is just a copy of your original table
db.Execute "DELETE * from Table7"

' get list of users
strSQL = "SELECT Table6.User, Table6.Uttr1, Count(Table6.Customer) AS CountCust FROM Table6 GROUP BY Table6.User, Table6.Uttr1;"
Set rstUser = db.OpenRecordset(strSQL)
rstUser.MoveFirst
Do While Not rstUser.EOF
If rstUser.uttr1 = rstUser.countcust Then
strSQL = "INSERT INTO Table7 ( User, Customer, Uttr1, Uttr2 ) "
strSQL = strSQL + "SELECT Table6.User, Table6.Customer, Table6.Uttr1, Table6.Uttr2 FROM Table6 "
strSQL = strSQL + " where Table6.User='" & rstUser.User & "';"
Else
strTop = "Top " & rstUser.uttr1
strSQL = "INSERT INTO Table7 ( User, Customer, Uttr1, Uttr2 ) "
strSQL = strSQL + "SELECT " & strTop & " Table6.User, Null As Customer, Table6.Uttr1, Table6.Uttr2 FROM Table6 "
strSQL = strSQL + "GROUP BY Table6.User, Null, Table6.Uttr1, Table6.Uttr2 "
strSQL = strSQL + " HAVING Table6.User='" & rstUser.User & "' ORDER BY Table6.Uttr2;"
End If
Set qdfAddRes = db.CreateQueryDef("AddResults", strSQL)
qdfAddRes.Execute
db.Execute "drop table AddResults"
rstUser.MoveNext
Loop

' update results table with customer details
strSQL = "UPDATE Table6 INNER JOIN Table7 ON (Table6.Uttr1 = Table7.Uttr1) AND"
strSQL = strSQL + " (Table6.Uttr2 = Table7.Uttr2) AND (Table6.User = Table7.User)"
strSQL = strSQL + " SET Table7.Customer = [table6].[customer] WHERE (((Table7.Customer) Is Null));"
Set qdfUpdRes = db.CreateQueryDef("UpdateCust", strSQL)
qdfUpdRes.Execute
db.Execute "drop table UpdateCust"

End Function