Repost Help with select query

  • Thread starter Thread starter Mattias
  • Start date Start date
M

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;
 
Hi,


You can't use an aggregate (or a query using an aggregate) in the main
query if you want the query to be updateable. Take a look at
http://www.mvps.org/access/queries/qry0020.htm and try solution 2. It should
lead to an updateable query since the aggregate occurs in the "WHERE" clause
(in a sub-query in the where clause, to be more exact), and that does not
destroy updateability.



Hoping it may help,
Vanderghast, Access MVP
 
Hi and thanks for your suggestion. Unfortunatly I reive a syntax error and I
cannot figure out what is wrong here. Can you see it?

Mattias

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.Bud)
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad)
 
Hi,


A SELECT statement is of the form

SELECT ... FROM ... WHERE


It seems you have one SELECT ... WHERE ( no FROM). I would try

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad)


if that makes sense.



Hoping it may help,
Vanderghast, Access MVP
 
Hi again,

Cannot get this thing this, can you please help me to rewrite this sql to
work with your idea of solution. Below is the main query from
Objekt.Objektmottagningrad to Objekt.KlubbatBeloppSEK and below this starts
the subquery.

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;
 
Hi,


If Objekt is the table,
if BuddatumTid is something that may appear many times for each
Objektmottagningrad in the table,
if you want only keep the record, where, for each Objektmottagningrad,
the value of BuddatumTid is maximum,

then

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad)


should do it.

With simulated data:

Objektmottagningrad Statusnr KlubbatBellopSEK BuddatumTid
1 xxx xxx1 1878
1 yyy yyy1 1988
1 zzz zzz1
160
2 aaa aaa1 1121


the query should return

1 yyy yyy1 1988
2 aaa aaa1 1121



When you say it is "not working", you get an ERROR or you do not get the
right result?



Vanderghast, Access MVP
 
hi again,

but you borgot the field "Bud" in Budregistreringrad. It is a currency field
and this can appear several times with the same data for one
objektmottagningrad...if this is the case then I use the date/time field
"BuddatumTid" to get the latest one to show in the result.

That is why I first had Maxofbud and then MaxOfBuddatumTid

Mattias
 
Guess this is how it should look, but it still not working right...it does
not create Budregistreringrad As T so when running the query I does not find
Bud and BuddatumTid...do you know why this is happening?

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))
 
Hi,


I don't understand the purpose of the second sub-query ( you have THREE levels of SELECT. The intermediate SELECT seems un-necessary, I would remove what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem, at the moment, but...if you ever need three levels of SELECT, I repeat that I really doubt it is where you should continue to look at, but if this is what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a MAX is not generally what we may look for (even if in this case, the group over which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad" and "Budregistreringrad" do not awake any meaning, any "second hint" about the nature of the problem, so, I am left on the pure mathematical/mechanical nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP
 
Hi

Hi again,

Thank you you put a lot of effort into this and I appreciate it very much.
Still just creates the Q with the below

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))

I will try to explain a little deeper so you will be able to understand why I need two subs.
Objekt is a table containing objekts for sale at a auction. "Objektmottagningrad" is autonumber field. The auctioneer sets an estimate (price) for every objekt for sale and there is a field called "KlubbatBeloppSEK" (Hammer price, final price at the auction) This is updated with a 0 if it is not sold. "Statusnr" is linked to a status table to be able to show the correct status for the objekt...Sold, Not sold etc. Budregistreringrad containing Bud (Bids) currency field and BuddatumTid is a datetime field wich is updated wiht now() when the bid is endtered into the system. If many bids with the same value on the same objekt then I use the BuddatumTid to tell witch bid is the top/latest (winning Bid which I use to update the hammer price with.All this fields are normally taken care of automaticly in the system in different routines.

What I am trying to achive here is a way for the custumer to push the button and get alot of work done automaticly..if planned auction has to be inhibited late. All objekts and bids etc are registred into the system and the has to be cancled. I need to have the query updatable use it as a recordset and loop throw the records one by one and set the status, hammer price = highest/latest bid etc etc.

Hope you understand me little better this time..

Mattias
Hi,


I don't understand the purpose of the second sub-query ( you have THREE levels of SELECT. The intermediate SELECT seems un-necessary, I would remove what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem, at the moment, but...if you ever need three levels of SELECT, I repeat that I really doubt it is where you should continue to look at, but if this is what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a MAX is not generally what we may look for (even if in this case, the group over which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad" and "Budregistreringrad" do not awake any meaning, any "second hint" about the nature of the problem, so, I am left on the pure mathematical/mechanical nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,


So, Bud is not part of Q?


The way I now see the system is that we may want to update KlubbatBeloppSEK
with the maximum Budregistreringrad.Bud:


UPDATE Objekt
SET Objekt.KlubbatBeloppSEK = DMax("Dub", "Budregistreringrad",
"Objektmottagningrad =" & Objektmottagningrad )


Once the value is updated, then, we can look back in Budregistreringrad to
see from where it comes from (else, with its zero, or its default value,
that price of 0 may not appear in Budregistreringrad).


SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON T.Objektmottagningrad = Q.Objektmottagningrad
AND
T.Bud=Q.KlubbatBeloppSEK
WHERE T.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = T.Objektmottagningrad
AND
V.Bud=T.Bud
)


So, in cases where there are many bids with the same price, the first one,
the MIN(BuddatumTid) is the one to associate with Objektmottagningrad. Note
that I assume Q.KlubbatBeloppSEK is already assigned to MAX(T.Bud), so, we
do not have to tell it again.




Hoping it may help,
Vanderghast, Access MVP


Hi

Hi again,

Thank you you put a lot of effort into this and I appreciate it very much.
Still just creates the Q with the below

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))

I will try to explain a little deeper so you will be able to understand why
I need two subs.
Objekt is a table containing objekts for sale at a auction.
"Objektmottagningrad" is autonumber field. The auctioneer sets an estimate
(price) for every objekt for sale and there is a field called
"KlubbatBeloppSEK" (Hammer price, final price at the auction) This is
updated with a 0 if it is not sold. "Statusnr" is linked to a status table
to be able to show the correct status for the objekt...Sold, Not sold etc.
Budregistreringrad containing Bud (Bids) currency field and BuddatumTid is a
datetime field wich is updated wiht now() when the bid is endtered into the
system. If many bids with the same value on the same objekt then I use the
BuddatumTid to tell witch bid is the top/latest (winning Bid which I use to
update the hammer price with.All this fields are normally taken care of
automaticly in the system in different routines.

What I am trying to achive here is a way for the custumer to push the button
and get alot of work done automaticly..if planned auction has to be
inhibited late. All objekts and bids etc are registred into the system and
the has to be cancled. I need to have the query updatable use it as a
recordset and loop throw the records one by one and set the status, hammer
price = highest/latest bid etc etc.

Hope you understand me little better this time..

Mattias
Hi,


I don't understand the purpose of the second sub-query ( you have THREE
levels of SELECT. The intermediate SELECT seems un-necessary, I would remove
what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in
green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem,
at the moment, but...if you ever need three levels of SELECT, I repeat that
I really doubt it is where you should continue to look at, but if this is
what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a MAX is
not generally what we may look for (even if in this case, the group over
which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad"
and "Budregistreringrad" do not awake any meaning, any "second hint" about
the nature of the problem, so, I am left on the pure mathematical/mechanical
nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP

Mattias said:
Guess this is how it should look, but it still not working right...it does
not create Budregistreringrad As T so when running the query I does not find
Bud and BuddatumTid...do you know why this is happening?

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))
Mattias said:
hi again,

but you borgot the field "Bud" in Budregistreringrad. It is a currency field
and this can appear several times with the same data for one
objektmottagningrad...if this is the case then I use the date/time field
"BuddatumTid" to get the latest one to show in the result.

That is why I first had Maxofbud and then MaxOfBuddatumTid

Mattias



sql syntax
error
Click to expand...
aggregate)
in
Click to expand...
solution
2. new
SQL Suppose
you order
table
Click to expand...
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.

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
Click to expand...
happens
Click to expand...
Click to expand...
 
Hi again,

The update part went great.

The part with SELECT Q.*, T.* also seemed to work first when I opened the
guery but I noticed that it was not updatable! Tried to go to design view of
the sql I get the errormessage "Cant represent the join expression
T.Bud=Q.KlubbatBeloppSEK in design view. One field have been deleted or
renamed or misspelled or and operator thet is not uspported in Design view
such as < and >

But I must agree we seem to be on track now!

Mattias


Michel Walsh said:
Hi,


So, Bud is not part of Q?


The way I now see the system is that we may want to update KlubbatBeloppSEK
with the maximum Budregistreringrad.Bud:


UPDATE Objekt
SET Objekt.KlubbatBeloppSEK = DMax("Dub", "Budregistreringrad",
"Objektmottagningrad =" & Objektmottagningrad )


Once the value is updated, then, we can look back in Budregistreringrad to
see from where it comes from (else, with its zero, or its default value,
that price of 0 may not appear in Budregistreringrad).


SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON T.Objektmottagningrad = Q.Objektmottagningrad
AND
T.Bud=Q.KlubbatBeloppSEK
WHERE T.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = T.Objektmottagningrad
AND
V.Bud=T.Bud
)


So, in cases where there are many bids with the same price, the first one,
the MIN(BuddatumTid) is the one to associate with Objektmottagningrad. Note
that I assume Q.KlubbatBeloppSEK is already assigned to MAX(T.Bud), so, we
do not have to tell it again.




Hoping it may help,
Vanderghast, Access MVP


Hi

Hi again,

Thank you you put a lot of effort into this and I appreciate it very much.
Still just creates the Q with the below

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))

I will try to explain a little deeper so you will be able to understand why
I need two subs.
Objekt is a table containing objekts for sale at a auction.
"Objektmottagningrad" is autonumber field. The auctioneer sets an estimate
(price) for every objekt for sale and there is a field called
"KlubbatBeloppSEK" (Hammer price, final price at the auction) This is
updated with a 0 if it is not sold. "Statusnr" is linked to a status table
to be able to show the correct status for the objekt...Sold, Not sold etc.
Budregistreringrad containing Bud (Bids) currency field and BuddatumTid is a
datetime field wich is updated wiht now() when the bid is endtered into the
system. If many bids with the same value on the same objekt then I use the
BuddatumTid to tell witch bid is the top/latest (winning Bid which I use to
update the hammer price with.All this fields are normally taken care of
automaticly in the system in different routines.

What I am trying to achive here is a way for the custumer to push the button
and get alot of work done automaticly..if planned auction has to be
inhibited late. All objekts and bids etc are registred into the system and
the has to be cancled. I need to have the query updatable use it as a
recordset and loop throw the records one by one and set the status, hammer
price = highest/latest bid etc etc.

Hope you understand me little better this time..

Mattias
Hi,


I don't understand the purpose of the second sub-query ( you have THREE
levels of SELECT. The intermediate SELECT seems un-necessary, I would remove
what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in
green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem,
at the moment, but...if you ever need three levels of SELECT, I repeat that
I really doubt it is where you should continue to look at, but if this is
what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a MAX is
not generally what we may look for (even if in this case, the group over
which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad"
and "Budregistreringrad" do not awake any meaning, any "second hint" about
the nature of the problem, so, I am left on the pure mathematical/mechanical
nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP

Mattias said:
Guess this is how it should look, but it still not working right...it does
not create Budregistreringrad As T so when running the query I does
Click to expand...
not
find
Bud and BuddatumTid...do you know why this is happening?

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))
currency
field
Click to expand...
get
the this
sql aggregate) solution
Click to expand...
and
that
Click to expand...
the
new order the
first
Click to expand...
amount
can
Click to expand...
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

000,00
Click to expand...
is
ok.
Click to expand...
only
one
Click to expand...
record
on
Click to expand...
1,000,000,
for
Click to expand...
only
one
Click to expand...
Click to expand...
Click to expand...
 
Hi,


Have you tired adding parenthesis,

SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON (T.Objektmottagningrad = Q.Objektmottagningrad)
AND
(T.Bud=Q.KlubbatBeloppSEK)


or, if still with problems, start a new query, with the designer, bring
Objekt and Budregistreringrad, then, drag and drop Objektmottagningrad from
the first table over the second, and do the same with
Objekt.KlubbatBeloppSEK over Budregistreringrad.Bud



that join is definitively "representable" in the query designer...


Having that part working, I would switch in SQL view, and used aliases to
the tables and append the WHERE clause.



Hoping it may help,
Vanderghast, Access MVP



Mattias said:
Hi again,

The update part went great.

The part with SELECT Q.*, T.* also seemed to work first when I opened the
guery but I noticed that it was not updatable! Tried to go to design view of
the sql I get the errormessage "Cant represent the join expression
T.Bud=Q.KlubbatBeloppSEK in design view. One field have been deleted or
renamed or misspelled or and operator thet is not uspported in Design view
such as < and >

But I must agree we seem to be on track now!

Mattias


Michel Walsh said:
Hi,


So, Bud is not part of Q?


The way I now see the system is that we may want to update KlubbatBeloppSEK
with the maximum Budregistreringrad.Bud:


UPDATE Objekt
SET Objekt.KlubbatBeloppSEK = DMax("Dub", "Budregistreringrad",
"Objektmottagningrad =" & Objektmottagningrad )


Once the value is updated, then, we can look back in Budregistreringrad to
see from where it comes from (else, with its zero, or its default value,
that price of 0 may not appear in Budregistreringrad).


SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON T.Objektmottagningrad = Q.Objektmottagningrad
AND
T.Bud=Q.KlubbatBeloppSEK
WHERE T.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = T.Objektmottagningrad
AND
V.Bud=T.Bud
)


So, in cases where there are many bids with the same price, the first one,
the MIN(BuddatumTid) is the one to associate with Objektmottagningrad. Note
that I assume Q.KlubbatBeloppSEK is already assigned to MAX(T.Bud), so, we
do not have to tell it again.




Hoping it may help,
Vanderghast, Access MVP


Hi

Hi again,

Thank you you put a lot of effort into this and I appreciate it very much.
Still just creates the Q with the below

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))

I will try to explain a little deeper so you will be able to understand why
I need two subs.
Objekt is a table containing objekts for sale at a auction.
"Objektmottagningrad" is autonumber field. The auctioneer sets an estimate
(price) for every objekt for sale and there is a field called
"KlubbatBeloppSEK" (Hammer price, final price at the auction) This is
updated with a 0 if it is not sold. "Statusnr" is linked to a status table
to be able to show the correct status for the objekt...Sold, Not sold etc.
Budregistreringrad containing Bud (Bids) currency field and BuddatumTid
Click to expand...
is
a
datetime field wich is updated wiht now() when the bid is endtered into the
system. If many bids with the same value on the same objekt then I use the
BuddatumTid to tell witch bid is the top/latest (winning Bid which I use to
update the hammer price with.All this fields are normally taken care of
automaticly in the system in different routines.

What I am trying to achive here is a way for the custumer to push the button
and get alot of work done automaticly..if planned auction has to be
inhibited late. All objekts and bids etc are registred into the system and
the has to be cancled. I need to have the query updatable use it as a
recordset and loop throw the records one by one and set the status, hammer
price = highest/latest bid etc etc.

Hope you understand me little better this time..

Mattias
Hi,


I don't understand the purpose of the second sub-query ( you have THREE
levels of SELECT. The intermediate SELECT seems un-necessary, I would remove
what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in
green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem,
at the moment, but...if you ever need three levels of SELECT, I repeat that
I really doubt it is where you should continue to look at, but if this is
what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a
Click to expand...
MAX
is
not generally what we may look for (even if in this case, the group over
which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad"
and "Budregistreringrad" do not awake any meaning, any "second hint" about
the nature of the problem, so, I am left on the pure mathematical/mechanical
nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP

right...it
does not get below
this
Click to expand...
look
at in
the and the 15
100 amount 26
Click to expand...
000,00
kr show
what 100
000,00
Click to expand...
000,00
kr existing
data. in
order
Click to expand...
it
is
ok.
if record2 has 1.000.000 and it is the
Click to expand...
only
one
on
the
right
side,
it
is
also ok.
If record2 has 1.000.000 and one more
Click to expand...
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"
Without the actual data it's hard to
understand
why
you
want
a
Null
for
record 2, when it currently has
Click to expand...
1,000,000,
Click to expand...
Click to expand...
 
Hi

Sorry took so long, could reply until now..

Have tried your suggestions and had create a new query...seem to be
close...but still not updatable..

SELECT OBJEKT.*, Budregistreringrad.*
FROM OBJEKT INNER JOIN Budregistreringrad ON (OBJEKT.KlubbatBeloppSEK =
Budregistreringrad.Bud) AND (OBJEKT.OBJEKTMOTTAGNINGRAD =
Budregistreringrad.Objektmottagningrad)
WHERE Budregistreringrad.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = OBJEKT.OBJEKTMOTTAGNINGRAD
AND
V.Bud=OBJEKT.KlubbatBeloppSEK);
Michel Walsh said:
Hi,


Have you tired adding parenthesis,

SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON (T.Objektmottagningrad = Q.Objektmottagningrad)
AND
(T.Bud=Q.KlubbatBeloppSEK)


or, if still with problems, start a new query, with the designer, bring
Objekt and Budregistreringrad, then, drag and drop Objektmottagningrad from
the first table over the second, and do the same with
Objekt.KlubbatBeloppSEK over Budregistreringrad.Bud



that join is definitively "representable" in the query designer...


Having that part working, I would switch in SQL view, and used aliases to
the tables and append the WHERE clause.



Hoping it may help,
Vanderghast, Access MVP



Mattias said:
Hi again,

The update part went great.

The part with SELECT Q.*, T.* also seemed to work first when I opened the
guery but I noticed that it was not updatable! Tried to go to design
Click to expand...
view
of
the sql I get the errormessage "Cant represent the join expression
T.Bud=Q.KlubbatBeloppSEK in design view. One field have been deleted or
renamed or misspelled or and operator thet is not uspported in Design view
such as < and >

But I must agree we seem to be on track now!

Mattias
Click to expand...
Budregistreringrad
to
Click to expand...
so,
we understand
why
Click to expand...
BuddatumTid
is
a
datetime field wich is updated wiht now() when the bid is endtered
Click to expand...
into
the
system. If many bids with the same value on the same objekt then I
Click to expand...
use
the
BuddatumTid to tell witch bid is the top/latest (winning Bid which I
Click to expand...
use
to
update the hammer price with.All this fields are normally taken care of
automaticly in the system in different routines.

What I am trying to achive here is a way for the custumer to push the button
and get alot of work done automaticly..if planned auction has to be
inhibited late. All objekts and bids etc are registred into the system and
the has to be cancled. I need to have the query updatable use it as a
recordset and loop throw the records one by one and set the status, hammer
price = highest/latest bid etc etc.

Hope you understand me little better this time..

Mattias
Hi,


I don't understand the purpose of the second sub-query ( you have THREE
levels of SELECT. The intermediate SELECT seems un-necessary, I would remove
what is in red:

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))

Note that Bud should be in Q (in Objekt ); if it is not, make it so (in
green) :

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM ( Objekt INNER JOIN Buddatum ON ... ) AS Q
WHERE Q.Bud= (SELECT Max(T.BuddatumTid) ...


My feeling is that is more what is probably in the nature of the problem,
at the moment, but...if you ever need three levels of SELECT, I
Click to expand...
repeat
that
I really doubt it is where you should continue to look at, but if this is
what you need, you probably need to change the alias too, here, I used V

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud =
(SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid =
(SELECT Max(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = Q.Objektmottagningrad))


but again, taking the MAX of something already been take equal to a
Click to expand...
MAX
is
not generally what we may look for (even if in this case, the group over
which you take the max is not the same in both cases).


Sorry that I can't get more "in" the problem, but "Objektmottagningrad"
and "Budregistreringrad" do not awake any meaning, any "second hint" about
the nature of the problem, so, I am left on the pure mathematical/mechanical
nature of the problem itself.


Hoping it may help,
Vanderghast, Access MVP

Guess this is how it should look, but it still not working right...it
does
not create Budregistreringrad As T so when running the query I
Click to expand...
does
not
find
Bud and BuddatumTid...do you know why this is happening?

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBeloppSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad = Q.Objektmottagningrad))
hi again,

but you borgot the field "Bud" in Budregistreringrad. It is a currency
field
and this can appear several times with the same data for one
objektmottagningrad...if this is the case then I use the date/time
field
"BuddatumTid" to get the latest one to show in the result.

That is why I first had Maxofbud and then MaxOfBuddatumTid

Mattias



Hi,


If Objekt is the table,
if BuddatumTid is something that may appear many times for each
Objektmottagningrad in the table,
if you want only keep the record, where, for each
Objektmottagningrad,
the value of BuddatumTid is maximum,

then

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad =
Q.Objektmottagningrad)


should do it.

With simulated data:

Objektmottagningrad Statusnr KlubbatBellopSEK BuddatumTid
1 xxx xxx1
1878
1 yyy yyy1
1988
1 zzz zzz1
160
2 aaa aaa1
1121


the query should return

1 yyy yyy1 1988
2 aaa aaa1 1121



When you say it is "not working", you get an ERROR or you do
Click to expand...
not
get
the
right result?



Vanderghast, Access MVP


Hi again,

Cannot get this thing this, can you please help me to
Click to expand...
rewrite
this
sql
to
work with your idea of solution. Below is the main query from
Objekt.Objektmottagningrad to Objekt.KlubbatBeloppSEK and below
this
starts
the subquery.

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;


message
Hi,


A SELECT statement is of the form

SELECT ... FROM ... WHERE


It seems you have one SELECT ... WHERE ( no FROM). I
Click to expand...
would
try
SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad =
Q.Objektmottagningrad)


if that makes sense.



Hoping it may help,
Vanderghast, Access MVP



Hi and thanks for your suggestion. Unfortunatly I reive a
syntax
error
and
I
cannot figure out what is wrong here. Can you see it?

Mattias

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.Bud)
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad =
Q.Objektmottagningrad)
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
Click to expand...
wrote
in
message
Hi,


You can't use an aggregate (or a query using an
aggregate)
in
the
main
query if you want the query to be updateable. Take a
Click to expand...
look
at
http://www.mvps.org/access/queries/qry0020.htm and try
solution
2.
It
should
lead to an updateable query since the aggregate occurs in
the
"WHERE"
clause
(in a sub-query in the where clause, to be more
Click to expand...
exact),
and
that
does
not
destroy updateability.



Hoping it may help,
Vanderghast, Access MVP


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
Click to expand...
...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;
in from
the want
to 000,00 000,00 000,00
kr
Click to expand...
from,
Click to expand...
Click to expand...
Click to expand...
 
Hi,


I am a little bit lost... a simpler, but similar, query in Northwind seems
to work fine, and be updatable:

SELECT Orders.*, [Order Details].*, [Order Details].Discount
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE [Order Details].Discount=(SELECT MAX(Discount)
FROM [Order
Details]);



Vanderghast, Access MVP


Mattias said:
Hi

Sorry took so long, could reply until now..

Have tried your suggestions and had create a new query...seem to be
close...but still not updatable..

SELECT OBJEKT.*, Budregistreringrad.*
FROM OBJEKT INNER JOIN Budregistreringrad ON (OBJEKT.KlubbatBeloppSEK =
Budregistreringrad.Bud) AND (OBJEKT.OBJEKTMOTTAGNINGRAD =
Budregistreringrad.Objektmottagningrad)
WHERE Budregistreringrad.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = OBJEKT.OBJEKTMOTTAGNINGRAD
AND
V.Bud=OBJEKT.KlubbatBeloppSEK);
Michel Walsh said:
Hi,


Have you tired adding parenthesis,

SELECT Q.*, T.*
FROM Objekt As Q INNER JOIN Budregistreringrad As T
ON (T.Objektmottagningrad = Q.Objektmottagningrad)
AND
(T.Bud=Q.KlubbatBeloppSEK)


or, if still with problems, start a new query, with the designer, bring
Objekt and Budregistreringrad, then, drag and drop Objektmottagningrad from
the first table over the second, and do the same with
Objekt.KlubbatBeloppSEK over Budregistreringrad.Bud



that join is definitively "representable" in the query designer...


Having that part working, I would switch in SQL view, and used aliases to
the tables and append the WHERE clause.



Hoping it may help,
Vanderghast, Access MVP



view Budregistreringrad first
one, so, sold
etc. BuddatumTid system
and so
(in this
is
Click to expand...
used
V a
MAX
Click to expand...
reive
a
syntax
error
and
I
cannot figure out what is wrong here. Can you see it?

Mattias

SELECT Q.Objektmottagningrad, Q.Statusnr, Q.KlubbatBellopSEK
FROM Objekt As Q
WHERE Bud = (SELECT Max(T.Bud)
WHERE BuddatumTid = (SELECT Max(T.BuddatumTid)
FROM Budregistreringrad As T
WHERE T.Objektmottagningrad =
Q.Objektmottagningrad)
in
message
Hi,


You can't use an aggregate (or a query using an
aggregate)
in
the
main
query if you want the query to be updateable. Take a look
at
http://www.mvps.org/access/queries/qry0020.htm and try
solution
2.
It
should
lead to an updateable query since the aggregate
Click to expand...
occurs
in
the
"WHERE"
clause
(in a sub-query in the where clause, to be more exact),
and
that
does
not
destroy updateability.



Hoping it may help,
Vanderghast, Access MVP


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;
Orders
ON if
this 000,00
kr 26
100
Click to expand...
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

Hi

Hopes this helps

Mattias
"[MVP] S. Clark"
<[email protected]>
wrote
in
message
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
Click to expand...
from,
Click to expand...
Click to expand...
Click to expand...
 
hi

This is updatable but only show one record

SELECT OBJEKT.*, Budregistreringrad.*, Budregistreringrad.BuddatumTid
FROM OBJEKT INNER JOIN Budregistreringrad ON OBJEKT.OBJEKTMOTTAGNINGRAD =
Budregistreringrad.Objektmottagningrad
WHERE (((Budregistreringrad.BuddatumTid)=(SELECT MIN(BuddatumTid)
FROM
[Budregistreringrad])));


Michel Walsh said:
Hi,


I am a little bit lost... a simpler, but similar, query in Northwind seems
to work fine, and be updatable:

SELECT Orders.*, [Order Details].*, [Order Details].Discount
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
WHERE [Order Details].Discount=(SELECT MAX(Discount)
FROM [Order
Details]);



Vanderghast, Access MVP


Mattias said:
Hi

Sorry took so long, could reply until now..

Have tried your suggestions and had create a new query...seem to be
close...but still not updatable..

SELECT OBJEKT.*, Budregistreringrad.*
FROM OBJEKT INNER JOIN Budregistreringrad ON (OBJEKT.KlubbatBeloppSEK =
Budregistreringrad.Bud) AND (OBJEKT.OBJEKTMOTTAGNINGRAD =
Budregistreringrad.Objektmottagningrad)
WHERE Budregistreringrad.BuddatumTid=
(SELECT MIN(V.BuddatumTid)
FROM Budregistreringrad As V
WHERE V.Objektmottagningrad = OBJEKT.OBJEKTMOTTAGNINGRAD
AND
V.Bud=OBJEKT.KlubbatBeloppSEK);
opened
the Design
view MAX(T.Bud),
so, endtered
into I
use I
use care
of
Click to expand...
as
a
Click to expand...
it
so used
Click to expand...
to
a group
over do
not wrote
in reive only
one code
and of
the date
(or
Click to expand...
Max(Orders.OrderDate)
Click to expand...
 
Back
Top