What wrong here? Me or access?

M

Marco

Hello.

I have a list of productions of products with the dates that they were
produced.

but I have to make a query that returns me per product the oldest produced
date.

When I do order by Date it makes the oldes to be on the top but when I group
to get the first it doesn't works.

I have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
21-11-2007 21-11-2012 14652
05-12-2007 05-12-2012 14652
07-12-2007 07-12-2012 14652
19-11-2007 19-11-2012 14652
06-12-2007 06-12-2012 14652
06-12-2007 06-12-2012 14652

and I nedd to have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
19-11-2007 19-11-2012 14652

I'm doing this:
SELECT First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataProducao) AS
FirstOfDataProducao,
First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataValidade) AS
FirstOfDataValidade, [21_tbl_StockEnviado_por_Expedicao].CodigoInterno
FROM 21_tbl_StockEnviado_por_Expedicao INNER JOIN
27_tbl_StockEnviado_por_Expedicao_Lotes ON
[21_tbl_StockEnviado_por_Expedicao].ID =
[27_tbl_StockEnviado_por_Expedicao_Lotes].ID_LinhaExpedicao
GROUP BY [21_tbl_StockEnviado_por_Expedicao].CodigoInterno;


Any help is very much appreciated. :)

Regards,
Marco
 
B

Bob Barrows [MVP]

Marco said:
Hello.

I have a list of productions of products with the dates that they were
produced.

but I have to make a query that returns me per product the oldest
produced date.

When I do order by Date it makes the oldes to be on the top but when
I group to get the first it doesn't works.

I have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
21-11-2007 21-11-2012 14652
05-12-2007 05-12-2012 14652
07-12-2007 07-12-2012 14652
19-11-2007 19-11-2012 14652
06-12-2007 06-12-2012 14652
06-12-2007 06-12-2012 14652

and I nedd to have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
19-11-2007 19-11-2012 14652

I'm doing this:
SELECT First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataProducao)
AS FirstOfDataProducao,
First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataValidade) AS
FirstOfDataValidade, [21_tbl_StockEnviado_por_Expedicao].CodigoInterno
FROM 21_tbl_StockEnviado_por_Expedicao INNER JOIN
27_tbl_StockEnviado_por_Expedicao_Lotes ON
[21_tbl_StockEnviado_por_Expedicao].ID =
[27_tbl_StockEnviado_por_Expedicao_Lotes].ID_LinhaExpedicao
GROUP BY [21_tbl_StockEnviado_por_Expedicao].CodigoInterno;


Any help is very much appreciated. :)

Regards,
Marco
Click to expand...

This query gets the latest production date per CodigoInterno:

SELECT CodigoInterno, Max(DataProducao) As LastProductionDate
FROM 27_tbl_StockEnviado_por_Expedicao_Lotes l INNER JOIN
21_tbl_StockEnviado_por_Expedicao e ON
l.ID_LinhaExpedicao = e.ID
GROUP BY CodigoInterno

Save the query as qryMaxProdDatePerCodigoInterno

Then create another query like this:

SELECT l.DataProducao, l.DataValidade, e.CodigoInterno
FROM (27_tbl_StockEnviado_por_Expedicao_Lotes l INNER JOIN
21_tbl_StockEnviado_por_Expedicao e ON
l.ID_LinhaExpedicao = e.ID) Inner Join
qryMaxProdDatePerCodigoInterno q ON
e.CodigoInterno=q.CodigoInterno AND
l.DataProducao=q.LastProductionDate

If you are using that join between
27_tbl_StockEnviado_por_Expedicao_Lotes and
21_tbl_StockEnviado_por_Expedicao in a lot of places, you could make
your life simpler by saving a query that does the join and supplies all
the fields that would be needed from the tables. That would allow the
above to be simplified to:

SELECT CodigoInterno, Max(DataProducao) As LastProductionDate
FROM qryJoinedTables
GROUP BY CodigoInterno

and
SELECT j.DataProducao, j.DataValidade, j.CodigoInterno
FROM qryJoinedTables j Inner Join
qryMaxProdDatePerCodigoInterno q ON
j.CodigoInterno=q.CodigoInterno AND
j.DataProducao=q.LastProductionDate
 
M

Marco

Hi.

You're are using a very specific queries. I'm not that good. :(

But I think that I solved my problem. You gave me an idea, instead of using
the optiongroup first, i use min. and it makes a lot os sense, because is
the minimum date.

I hope this continues to work.

Thanks.
Marco



Bob Barrows said:
Marco said:
Hello.

I have a list of productions of products with the dates that they were
produced.

but I have to make a query that returns me per product the oldest
produced date.

When I do order by Date it makes the oldes to be on the top but when
I group to get the first it doesn't works.

I have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
21-11-2007 21-11-2012 14652
05-12-2007 05-12-2012 14652
07-12-2007 07-12-2012 14652
19-11-2007 19-11-2012 14652
06-12-2007 06-12-2012 14652
06-12-2007 06-12-2012 14652

and I nedd to have this:
DataProducao DataValidade CodigoInterno
09-04-2008 09-04-2011 10056
19-11-2007 19-11-2012 14652

I'm doing this:
SELECT First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataProducao)
AS FirstOfDataProducao,
First([27_tbl_StockEnviado_por_Expedicao_Lotes].DataValidade) AS
FirstOfDataValidade, [21_tbl_StockEnviado_por_Expedicao].CodigoInterno
FROM 21_tbl_StockEnviado_por_Expedicao INNER JOIN
27_tbl_StockEnviado_por_Expedicao_Lotes ON
[21_tbl_StockEnviado_por_Expedicao].ID =
[27_tbl_StockEnviado_por_Expedicao_Lotes].ID_LinhaExpedicao
GROUP BY [21_tbl_StockEnviado_por_Expedicao].CodigoInterno;


Any help is very much appreciated. :)

Regards,
Marco
Click to expand...

This query gets the latest production date per CodigoInterno:

SELECT CodigoInterno, Max(DataProducao) As LastProductionDate
FROM 27_tbl_StockEnviado_por_Expedicao_Lotes l INNER JOIN
21_tbl_StockEnviado_por_Expedicao e ON
l.ID_LinhaExpedicao = e.ID
GROUP BY CodigoInterno

Save the query as qryMaxProdDatePerCodigoInterno

Then create another query like this:

SELECT l.DataProducao, l.DataValidade, e.CodigoInterno
FROM (27_tbl_StockEnviado_por_Expedicao_Lotes l INNER JOIN
21_tbl_StockEnviado_por_Expedicao e ON
l.ID_LinhaExpedicao = e.ID) Inner Join
qryMaxProdDatePerCodigoInterno q ON
e.CodigoInterno=q.CodigoInterno AND
l.DataProducao=q.LastProductionDate

If you are using that join between
27_tbl_StockEnviado_por_Expedicao_Lotes and
21_tbl_StockEnviado_por_Expedicao in a lot of places, you could make
your life simpler by saving a query that does the join and supplies all
the fields that would be needed from the tables. That would allow the
above to be simplified to:

SELECT CodigoInterno, Max(DataProducao) As LastProductionDate
FROM qryJoinedTables
GROUP BY CodigoInterno

and
SELECT j.DataProducao, j.DataValidade, j.CodigoInterno
FROM qryJoinedTables j Inner Join
qryMaxProdDatePerCodigoInterno q ON
j.CodigoInterno=q.CodigoInterno AND
j.DataProducao=q.LastProductionDate






--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find then highlight in yellow 6
How to choose only the first record from a query 4
Too negative XIRR? 3
Calculating interest due on late loan payments 1
Advanced problem using auto-number and datediff to find readmissio 1
Windows 7 Duplicate files. 1
Trying to recreate bootable CD from zipped files 14
Please help for FV and XIRR function. 3

Top