How to choose only the first record from a query

[Marco]

Hi.

I have two tabels connected with a relationship.

I need to make a query that updates me the table1 from only one record of
table 2. The query should get the oldest record (take attention the date) and
update the table1 with that data.

This is the result of my query:
IntCode Lote ProdDate BBE
10141 C122 19-11-2007 19-11-2012
10114 C107 21-11-2007 21-11-2012
10114 C109 05-12-2007 05-12-2012
10141 C135 06-12-2007 06-12-2012
10141 C134 06-12-2007 06-12-2012
10114 C110 07-12-2007 07-12-2012


I need to upload my table on with the data of that first record. In this
query I get the record I need in first because I'm ordering by ProdDate.

How can I do this?

Regards,
Marco

 

[Michel Walsh]

SELECT TOP 1 * FROM queryName


can be a start.


Vanderghast, Access MVP

 

[Michel Walsh]

Note that TOP 1 can return ex-equo, as defined by the ORDER BY, so you may
decide to add ex-equo breaker fields:


ORDER BY ProdDate, Lote


as example, break the equality occurring the 06-12-2007 (you have 2
records for that date).



Vanderghast, Access MVP

 

[Marco]

Hi. thanks, it really works.

but what do you mean with ex-equo and add ex-equo breaker fields?

PS: Access suprises me everyday in a good way.

Thanks.
Marco

 

[Michel Walsh]

If you write:


SELECT TOP 1 field1
FROM myTable
ORDER BY field2



where field2 values are: 10, 10, 11, 12, 13


The TWO records with field2=10 will get out, since the two records are ties,
or 'ex-equo'. To break the equality, you can supply extra fields in the
order by clause, like "AB" and "AC" will both be in first position if you
only consider the first letter only, so you look at the second letter to
break the tie:

SELECT TOP 1 field1
FROM myTable
ORDER BY field2, primaryKeyField


always break any tie field2 can have, since the primarykey field cannot have
duplicated values.




Vanderghast, Access MVP