Help with select query

[Mattias]

Hi

Need pro assistance with this select query...

I am going to use it as a recordset to loop throw with code later. Have one
problem with the query though to make it usable..I would like it to be one
unique record on the left side and only one top value on the right side for
each unique recordnumber on the left side.

Posted the SQL below.

Thanks in advance...

Mattias

The query behaves like this now...

Left side: Right side:

record1 200.000:-
record1 100.000:-
record1 50:-
record2 1.000.000:-
record3 Null
record4 Null


And would like to be like this...


Left side: Right side:

record1 200.000:-
record2 Null
record3 50:-
record4 1.000.000:-
record5 Null
record6 Null






SELECT AuktionsrutinradFråga.OBJEKTMOTTAGNINGRAD,
AuktionsbudradAutobudFråga.Bud, AuktionsbudradAutobudFråga.BuddatumTid,
AuktionsbudradAutobudFråga.MaxBud
FROM AuktionsrutinradFråga LEFT JOIN AuktionsbudradAutobudFråga ON
AuktionsrutinradFråga.OBJEKTMOTTAGNINGRAD =
AuktionsbudradAutobudFråga.Objektmottagningrad
ORDER BY AuktionsrutinradFråga.OBJEKTMOTTAGNINGRAD,
AuktionsbudradAutobudFråga.Bud DESC ,
AuktionsbudradAutobudFråga.BuddatumTid;

 

[[MVP] S. Clark]

Without the actual data it's hard to understand why you want a Null for
record 2, when it currently has 1,000,000, for example.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Mattias]

Hi

I do not want to change any data!!

It just an example. If record2 is Null it is ok.
if record2 has 1.000.000 and it is the only one on the right side, it is
also ok.
If record2 has 1.000.000 and one more record on right side: 1.500.000..then
I would like the query only to show top value "1.500.000" on the right side.

Mattias

 

[[MVP] S. Clark]

You need to post an excerpt of your existing data. Otherwise, your final
desired product makes no sense.

I have to know where you are coming from, in order to give you directions to
where you want to go.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Mattias]

Autobud OBJEKTMOTTAGNINGRAD Benämning TomUtropsprisSEK Budrad Bud
BuddatumTid
332 Kustlandskap 80 000,00 kr



335 Boxningsmatchen 30 000,00 kr 26 100 000,00 kr 2003-06-03 11:54:50
335 Boxningsmatchen 30 000,00 kr 21 4 000,00 kr 2003-05-27 09:24:24
336 Interiör 30 000,00 kr 15 100 000,00 kr 2003-05-22 12:31:13
336 Interiör 30 000,00 kr 19 80 000,00 kr 2003-05-24 10:58:28
337 Kristallkrona 40 000,00 kr



338 Porträtt 10 000,00 kr



340 BBBBB 6 000,00 kr



344 blaaaljklöjklöjklj 8 000,00 kr



345 Herrporträtt 15 000,00 kr 33 6 000,00 kr 2003-10-06 10:57:23
345 Herrporträtt 15 000,00 kr 8 5 500,00 kr 2002-11-23 12:14:21
346 Berglandskap 200 000,00 kr 11 170 000,00 kr 2002-12-15 14:39:11
346 Berglandskap 200 000,00 kr 12 150 000,00 kr 2003-01-05 16:54:57
349 Kustlandskap 150 000,00 kr 2 100 000,00 kr 2002-09-27 12:31:25
350 Berglandskap 200 000,00 kr



351 Rokokobyrå 35 000,00 kr



398 taveltavel 1 400,00 kr



716 vvv 0,00 kr



717 ggg 0,00 kr



718 rrrrrrrrrrr 0,00 kr





Hi

Hopes this helps

Mattias

 

[[MVP] S. Clark]

Good. Now, using just the 332-337 values, show what your desired output
would be.

 

[Mattias]

Hi again,

Would like the output to be like below:

332 Kustlandskap 80 000,00 kr
335 Boxningsmatchen 30 000,00 kr 26 100 000,00 kr
2003-06-03 11:54:50
336 Interiör 30 000,00 kr 15 100 000,00 kr
2003-05-22 12:31:13
337 Kristallkrona 40 000,00 kr

 

[Mattias]

332 Kustlandskap 80 000,00 kr
335 Boxningsmatchen 30 000,00 kr 26 100 000,00 kr 2003-06-03
11:54:50
336 Interiör 30 000,00 kr 15 100 000,00 kr
2003-05-22 12:31:13
337 Kristallkrona 40 000,00 kr

Just want to tell the above "26 100 000", this amount can appear several
time in the query for the same record "335" if this happens I would like to
use the latest record using the "Date/field"....

Mattias

 

[[MVP] S.Clark]

I think I can give you an idea of how to solve it using another example.
The data comes from the Northwind sample database. Suppose you want to find
the last order for a customer, and show some of the order table data in the
result.

First, create a query to find the max order date (or order id) per customer.

qryLastOrderDatePerCustomer
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders GROUP BY Orders.CustomerID;

Next, create a second query, that uses the info from the first query, as
well as any matching detail info.
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID,
Orders.OrderDate, Orders.RequiredDate, Orders.ShipVia
FROM qryLastOrderDatePerCustomer INNER JOIN Orders ON
(qryLastOrderDatePerCustomer.MaxOfOrderDate = Orders.OrderDate) AND
(qryLastOrderDatePerCustomer.CustomerID = Orders.CustomerID);

I think this is kind of the same thing that you want to accomplish when
there is a 335 and 26 in your data.

 

[Mattias]

Hi again,,

Did what you suggested and it worked thanks, only one thing...cannot update
the query now. That is why I need to run some code and update.

Guess it is because using the Max in the query ..post the new SQL below.

Mattias

SELECT OBJEKT.OBJEKTMOTTAGNINGRAD, OBJEKT.Autobudnr, OBJEKT.Utropsnummer,
OBJEKT.Statusnr, OBJEKT.KlubbatBeloppSEK,
AutobudSubFråga.Objektmottagningrad, AutobudSubFråga.MaxOfBud,
AutobudSubFråga.MaxOfBuddatumTid
FROM OBJEKT LEFT JOIN AutobudSubFråga ON OBJEKT.OBJEKTMOTTAGNINGRAD =
AutobudSubFråga.Objektmottagningrad;