SubQuery Syntax

[john.bartlett]

I received some generous help from the users of this group to get this
far. This works great for me.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
FROM tblMain
WHERE tblMain.PrizeAssigned = 0)));

I thought I was done, but I ran into one more issue I haven't been
able to resolve. How do I deal with duplicate Max(Weight1) values
(i.e. A tie!) being selected. I can only have one winner.

I thought it would be pretty straight forward since I have another
sorting element avialable in the table. It is tblMain.Time (a unique
time of completion for each participant), but I can't get it to work.

Any ideas on how I can add this next sorting element (tblMain.Time)to
this existing code? I tried several approaches but I am really
struggling with the SubQuery syntax.

As always, thanks.

 

[Michel Walsh]

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.Weight1 =
(
SELECT TOP 1 Weight1
FROM tblMain
WHERE tblMain.PrizeAssigned = 0
ORDER BY weight1, time, ... other fields to be sure there is no more tie
)



Vanderghast, Access MVP

 

[Jerry Whittle]

Hi John,

How's things at A3OA?

Back to your question, does the table have a primary key field? If so,
something like this might work. You may have to play with DESC in the Order
By clause. Test this one a lot before using it on real data.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.PrimaryKey=(SELECT Top 1 tblMain.PrimaryKey
FROM tblMain
WHERE tblMain.PrizeAssigned = 0
ORDER BY tblMain.MaxWeight, tblMain.[Time]) ;

BTW: You job title is a little funny in the Global Address List!

 

[john.bartlett]

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.Weight1 =
  (
    SELECT TOP 1 Weight1
    FROM tblMain
    WHERE tblMain.PrizeAssigned = 0
    ORDER BY weight1, time, ... other fields to be sure there is no more tie
   )

Vanderghast, Access MVP











- Show quoted text -

This is what I tried but it doesn't work for me. It still updates two
records.

 

[john.bartlett]

Hi John,

How's things at A3OA?

Back to your question, does the table have a primary key field? If so,
something like this might work. You may have to play with DESC in the Order
By clause. Test this one a lot before using it on real data.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.PrimaryKey=(SELECT Top 1 tblMain.PrimaryKey
   FROM tblMain
   WHERE tblMain.PrizeAssigned = 0
   ORDER BY tblMain.MaxWeight, tblMain.[Time]) ;

BTW: You job title is a little funny in the Global Address List!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I received some generous help from the users of this group to get this
far. This works great for me.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
    FROM tblMain
    WHERE tblMain.PrizeAssigned = 0)));

I thought I was done, but I ran into one more issue I haven't been
able to resolve. How do I deal with duplicate Max(Weight1) values
(i.e. A tie!) being selected. I can only have one winner.

I thought it would be pretty straight forward since I have another
sorting element avialable in the table. It is tblMain.Time (a unique
time of completion for each participant), but I can't get it to work.

Any ideas on how I can add this next sorting element (tblMain.Time)to
this existing code? I tried several approaches but I am really
struggling with the SubQuery syntax.

As always, thanks.- Hide quoted text -

- Show quoted text -

This works. Thanks! But I am not sure why it only works with the
primary key. I will have to spend some time with it. Always new things
to learn! Thanks again. I will have to check the GAL.

 

[Michel Walsh]

You are right... sorry. Try


UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE EXISTS
(
SELECT * FROM query1
WHERE query1.weight1=tblMain.weight1
AND query1.time = tblMain.time
AND ...
)



and query1 is:

SELECT TOP 1 weight1, time, ...
FROM tblMain
ORDER BY weight1, time, ...



Vanderghast, Access MVP

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.Weight1 =
(
SELECT TOP 1 Weight1
FROM tblMain
WHERE tblMain.PrizeAssigned = 0
ORDER BY weight1, time, ... other fields to be sure there is no more tie
)

Vanderghast, Access MVP











- Show quoted text -

This is what I tried but it doesn't work for me. It still updates two
records.

 

[Jerry Whittle]

The primary key picks out one and only one record. I guess there's a very
slight chance that more than one record will have the exact same weight and
times and Top 1 would still produce more than one record. That could cause an
error as I used = instead of IN for the subquery.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi John,

How's things at A3OA?

Back to your question, does the table have a primary key field? If so,
something like this might work. You may have to play with DESC in the Order
By clause. Test this one a lot before using it on real data.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE tblMain.PrimaryKey=(SELECT Top 1 tblMain.PrimaryKey
FROM tblMain
WHERE tblMain.PrizeAssigned = 0
ORDER BY tblMain.MaxWeight, tblMain.[Time]) ;

BTW: You job title is a little funny in the Global Address List!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I received some generous help from the users of this group to get this
far. This works great for me.

UPDATE tblMain
SET tblMain.PrizeAssigned = 1
WHERE (((tblMain.Weight1)=(SELECT Max(Weight1)
FROM tblMain
WHERE tblMain.PrizeAssigned = 0)));

I thought I was done, but I ran into one more issue I haven't been
able to resolve. How do I deal with duplicate Max(Weight1) values
(i.e. A tie!) being selected. I can only have one winner.

I thought it would be pretty straight forward since I have another
sorting element avialable in the table. It is tblMain.Time (a unique
time of completion for each participant), but I can't get it to work.

Any ideas on how I can add this next sorting element (tblMain.Time)to
this existing code? I tried several approaches but I am really
struggling with the SubQuery syntax.

As always, thanks.- Hide quoted text -

- Show quoted text -

This works. Thanks! But I am not sure why it only works with the
primary key. I will have to spend some time with it. Always new things
to learn! Thanks again. I will have to check the GAL.