Update de produtos com as menores datas de vencimento - Estoque PEPS

Boa tarde meus amigos. Será que vocês poderiam me orientar numa instrução SQL.

Até então o controle de estoques do meu sistema era bastante simplificado. Quando ocorria uma compra de um produto, o sistema somava aquela entrada e quando ocorria a venda, o sistema descontava aquela quantidade vendida no campo QuantProd da tabela de cadastro do produto, utilizando uma consulta UPDATE simples.

Agora estou aperfeiçoando o sistema, e preciso fazer um controle de estoque do tipo PEPS (primeiro que entra, primeiro que sai), baseado nas datas de vencimento dos produtos. Os produtos que entram primeiro devem sair primeiro, porque possuem datas de vencimento menores.

As compras de produtos estão sendo gravadas na tabela TBL_ESTOQUEAUX (principais campos: CodEstoqueAux, CodCompra, DescProduto, QuantProd, DataVenc e LoteProd). Cada produto tem um cadastro único em uma outra tabela central, mas cada compra gera um novo registro de entrada do produto nesta tabela auxiliar que eu criei para este controle, e cada um desses registros possui uma data de vencimento daquele lote comprado. E então a soma das quantidades de todas as entradas do produto corresponderá ao estoque deste produto.

Então, a grande questão é a necessidade de quando ocorrer a venda de produtos, o sistema deve atualizar as quantidades dos registros desses produtos que tiverem o vencimento mais antigo na tabela TBL_ESTOQUEAUX, utilizando os dados da tabela TBL_VENDASDET (campos: CodVendasDet, CodVenda, DescProduto, QuantProd) que contém as quantidades vendidas dos produtos.

Eu consigo facilmente criar uma consulta SELECT com um agrupamento dos produtos com menor data de vencimento da tabela TBL_ESTOQUEAUX, com o operador "Mín". O problema é que não posso criar uma consulta UPDATE com este mesmo tipo de agrupamento. Quando altero o tipo de consulta Seleção para Atualização, o Access desabilita a função de agrupamentos para escolhermos os operadores. E não consigo pensar em outros critérios no UPDATE que considere somente os registros com menor data de vencimento.

A dificuldade maior nesta questão é que não podemos utilizar um critério de "entre datas", que seria bem mais simples. O que precisamos é fazer uma verificação dos registros com as datas menores desta tabela, que não é tão simples. Existiria algum critério para a consulta Update atualizar somente os registros com as menores datas?

Conseguindo resolver esta questão, bastarei finalizar o código com uma consulta Delete para remover os registros com estoques zerados.

Para facilitar o entendimento deste caso, vamos simular a seguinte tabela TBL_COMPRASDET. Vamos considerar que tivemos três registros de compras: 3445, 3446 e 3447 (campo CodCompras), com entradas de três produtos A, B e C em cada uma das compras, e suas respectivas quantidades de compras, datas de vencimento e lotes de fabricação. Estamos considerando a tabela TBL_COMPRASDET como uma tabela auxiliar e que tem um relacionamento com uma tabela principal de compras, chamada TBL_COMPRAS (que contem a data da compra, o fornecedor, o número da Nota Fiscal, etc).

TBL_COMPRASDET:

CodComprasDet---------CodCompras---------DescProduto---------QuantCompra-----------DataVenc------------LoteFabric
40087------------------------3445---------------------Produto-A---------------100------------------------20/12/2019------------34467
40088------------------------3445---------------------Produto-B---------------150------------------------18/01/2020------------20856
40089------------------------3445---------------------Produto-C---------------80-------------------------15/12/2019------------45562
40090------------------------3446---------------------Produto-A----------------50-------------------------20/01/2020------------34468
40091------------------------3446---------------------Produto-B---------------100------------------------25/02/2020------------20857
40092------------------------3446---------------------Produto-C---------------120------------------------17/01/2020-----------45563
40093------------------------3447---------------------Produto-A----------------50-------------------------10/02/2020------------34469
40094------------------------3447---------------------Produto-B----------------70-------------------------20/03/2020------------20858
40095------------------------3447---------------------Produto-C---------------100------------------------10/02/2020------------45564

Considerando essas entradas, podemos afirmar que ficamos em estoque com as seguintes quantidades:

200 unidades do Produto A
320 unidades do Produto B
300 unidades do Produto C

Com essas informações, vamos para o nosso problema. Suponhamos que realizamos uma determinada venda para um cliente que pediu: 20 unidades do Produto A; 50 unidades do Produto B e 30 unidades do Produto C.

Preciso criar um UPDATE para descontar essas QUANTIDADES vendidas dos registros dos produtos A, B e C que contenham as MENORES DATAS de vencimento desta tabela TBL_COMPRASDET, pois no Controle PEPS os produtos de lotes com menores datas de vencimento devem sair antes dos demais. Ou seja, o sistema deverá descontar 20 unidades do Produto A que tem data de vencimento 20/12/2019; 50 unidades do Produto B com data de vencimento 18/01/2019 e 30 unidades do Produto C com data de vencimento 15/12/2019.

Percebam que não temos como utilizar um critério de datas simples. Alguém teria uma solução tradicional para este caso ou esta situação realmente demandaria uma codificação mais complexa?

Bom dia
A primeira leitura, deve ter tabela com quantidades de stock existentes
Crie função para trabalhar a venda e essa função irá ao lote mais antigo do produto e retira até chegar a zero e vai retirar do próximo lote o que faltar

Muito obrigado pela sua colaboração, Sr. Alexandre.

Meu questionamento está justamente neste trecho da sua resposta:

"[...] essa função irá ao lote mais antigo do produto [...]".

Como configurar o código do UPDATE para a consulta identificar os lotes mais antigos? Perceba que não poderíamos utilizar um critério de "entre datas"...

Aguardo retorno, e mais uma vez muito obrigado pelo seu apoio nesta questão.

Bom dia!

Se vc usar o DFirst(DataVenc) na data de vencimento do produto!

Expressão                                                   Resultados

Selecione dPrimeiro ("DateofSale", "ProductSales") como expr1 de ProductSales GROUP BY dPrimeiro ("DateofSale", "ProductSales");

Retorna o valor aleatório do campo "DateofSale" da tabela "ProductSales". Se a tabela tiver uma "chave primária", ela retornará o valor "FIRST" do "DateofSale" com base na ordem crescente da chave primária.

Fonte: support.office.com/pt-br/article/fun%C3%A7%C3%B5es-dfirst-dlast-adb06169-bf49-4f80-bf04-1e4b5a48a8a7

Abraço, Delson

Valiosíssima sua contribuição, Sr. Delson.

Eu já havia tentado construir o UPDATE com uma cláusula de agrupamento (GROUP BY), mas me parece que não é possível. As cláusulas GROUP BY são possíveis de utilização em consultas SELECT e DELETE, mas parece que não são permitidas em consultas UPDATE. Não temos como construir um UPDATE de um SELECT que contenha uma cláusula GROUP BY, o ACCESS mostra a mensagem que "o campo não é atualizável".

Eu aconselho a criar a função mas para tal precisa ter tabela com quantidades em stock
Onde está a tabela de stock para dar saida?

As compras de produtos estão sendo gravadas na tabela TBL_ESTOQUEAUX (principais campos: "Código do Registro", "Código da Compra", "Descrição do Produto", "Quantidade do Produto", "Data de Vencimento" e "Lote do Produto").

Somente para esclarecer, uma informação complementar, cada produto tem um cadastro único em uma outra tabela central, mas cada compra gera um novo registro de entrada do produto nesta tabela auxiliar que eu criei para este controle, e cada um desses registros possui uma data de vencimento do lote comprado. E então a soma das quantidades de todas as entradas do produto corresponde ao estoque deste produto.

É justamente nesta tabela que está sendo direcionada a consulta UPDATE. Mas não tenho conseguido criar a sintaxe da consulta para ela descontar as quantidades vendidas somente dos registros com menor data de vencimento.

O algorítimo seria o seguinte:

Atualize o campo Quantidade-Produto dos registros da tabela TBL_ESTOQUEAUX que contenham o menor valor do campo Data-Vencimento, onde os registros sejam correspondentes aos que estão na tabela TBL_VENDASDET*

* A tabela TBL_VENDASDET grava os registros de venda. A consulta UPDATE está sendo construída no código do botão "Confirmar Venda" do formulário desta tabela.

Boa tarde
Não vejo campo na tabela TBL_ESTOQUEAUX que identifique o código do produto. Será a descrição? Se sim, é desaconselhável ter textro como controlo de produto, sujeita facilmente a erros
O procedimento seria do género

sub BaixaProduto(strCodProd as string, Qt as integer)
'filtra registos do produto ordenados por data
'dá baixa do stock no registo e, se fôr insuficiente, passa ao registo seguinte
end sub

Indique nomes e tipos de campos da tabela envolvidos na operação

De fato a Tabela TBL_EstoqueAux grava os registros de produtos pelo seu código, e não pela descrição. Fiz esta postagem de forma genérica, mencionando o campo "Descrição do Produto" para o leitor da mensagem entender que se trata do campo de identificação do produto.

Eu gostaria de uma codificação para a consulta UPDATE que atenda ao seguinte algorítimo:

Atualize o campo "Quantidade-Produto" dos "registros de compras de produtos" da tabela TBL_ESTOQUEAUX que contenham o "MENOR" valor do campo "Data-Vencimento", onde os campos "Código-Produto" nesta tabela sejam correspondentes aos campos "Código-Produto" que estão na tabela TBL_VENDASDET*

* A tabela TBL_VENDASDET grava os registros de venda. A consulta UPDATE está sendo construída no código do botão "Confirmar Venda" do formulário desta tabela.

Bom dia

Normalize nomes (Código-Produto=CodigoProduto, Data-Vencimento=DataVencimento)
Deve tratar de abater em cada venda. Se fizer a partir da tabela vendas, precisa ter campo de controlo se já foi descontada a venda ou não
Para tratar em cada venda, utilize

Atualize o campo "Quantidade-Produto" dos "registros de compras de produtos" da tabela TBL_ESTOQUEAUX que contenham o "MENOR" valor do campo "Data-Vencimento", onde os campos "Código-Produto" nesta tabela sejam correspondentes aos campos "Código-Produto" que estão na tabela TBL_VENDASDET*

Código:

Sub BaixaProduto(intCodProd As Integer, Qt As Integer)
    '--------------------------------------------------------------'
    '  código criado por Alexandre Neves, do Fórum MaximoAccess  '
    '    utilize o código livremente mas mantenha os créditos    '
    '--------------------------------------------------------------'
    Dim Rst As dao.Recordset
   
    Set Rst = CurrentDb.OpenRecordset("SELECT * FROM TBL_ESTOQUEAUX WHERE Código-Produto=" & intCodProd & " ORDER BY Data-Vencimento")
    Do
    If Qt > Rst("Quantidade-Produto") Then
        Rst.Edit
        Qt = Qt - Rst("Quantidade-Produto")
        Rst("Quantidade-Produto") = 0
        Rst.Update
    Else
        Rst.Edit
        Rst("Quantidade-Produto") = Rst("Quantidade-Produto") - Qt
        Rst.Update
        Exit Do
    End If
    Rst.MoveNext
    Loop
End Sub

Perfeito, Alexandre. Irei testar o código nos próximos dias. Na próxima semana eu comunico os resultados. Muito obrigado!!!