SQL PROB

[mn12]

Hi,
I use access DB in my vb program,and I have SQL problem.
I have a table with 4 records:date,number and 2 other records.
each number can appear on some dates,e.x.:
number date
10 10/2003
10 1/2003
10 5/2003
20 3/2003
20 5/2003
20 6/2003

I want all the records that appear till 6/2003,
but-I want the one latest record only for each number.
e.x:
for number 20- I want the record of 6/2003.
for number 10- I want the record of 5/2003.
I want to get all the fields of each record.(not only number and date).

I have tried this query:
&quot;SELECT MAX(DATE) AS MAX_DATE, number FROM table WHERE DATE<#06/01/2003# GROUP BY number&quot;

but--it doesn't work because I want to select more than one field,and I can use &quot;GROUP BY&quot; on one field only.

can someone help me please???

thank you

mn12

 

[ca8msm]

You will have to watch ou for how you store your dates but the basic idea will be :

Table1:

Field1 Field2
10 1
10 2
10 3
20 1
20 2
20 3

SELECT Max(Table1.Field1) AS MaxOfField1, Max(Table1.Field2) AS MaxOfField2
FROM Table1
GROUP BY Table1.Field1

When I say watch out for dates I mean that it may be better to store that date in a format such as dd/mm/yyyy (as this may affect how the database interprets the date field). In the above example I've just demonstrated it using numbers but for your needs you will need to add the where clause for the dates.

 

[Daisy450]

Assuming that you actually have the whole date. You either need to save it as a whole date or reverse it YYYYMM.

Two choices
Choice 1:
Go ahead and create this qry
&quot;SELECT MAX(DATE) AS MAX_DATE, number FROM table WHERE DATE<#06/01/2003# GROUP BY number&quot;

Then create this qry
SELECT table.Number, table.Date, table.Field1, table.Field2,
FROM

INNER JOIN Qry1 ON (table.Date = Qry1.MAX_DATE) AND (table.Number = Qry1.number);

Choice 2
SELECT Last(table.Date) AS MAX_DATE, table.Number, Last(table.Field1) AS LastOfField1, Last(table.Field2) AS LastOfField2
FROM

WHERE (((table.Date)<#6/1/2003#))
GROUP BY table.Number
ORDER BY Last(table.Date);

Hope this helps,
Daisy

 

[mn12]

Hi Daisy,
thank you for trying help me.
I want to use the 2nd choise you have suggested,and it nearly works correctly.
the problem:
I get the right number and date,but not the right values on field1 and field2.
e.x:
the table:
number date desc
10 10/2003 &quot;aaa&quot;
10 1/2003 &quot;bbb&quot;
10 5/2003 &quot;ccc&quot;
20 3/2003 &quot;ddd&quot;
20 5/2003 &quot;eee&quot;
20 6/2003 &quot;fff&quot;

results of your query:
number date desc
10 5/2003 &quot;bbb&quot;
20 6/2003 &quot;eee&quot;

you see that the field of &quot;desc&quot; have wrong values.
how can I solve the problem?

thank you for your time,
mn12