Este artigo explica como usar consultas de valores principais e consultas totais para encontrar as datas mais recentes ou mais antigas em um conjunto de registros. Isso pode ajudá-lo a responder a várias perguntas comerciais, como quando um cliente fez pela última vez um pedido ou quais cinco trimestres foram seus melhores para vendas, por cidade.
Neste artigo
Visão geral
Você pode classificar dados e revisar os itens mais bem classificados usando uma consulta de valores principais. Uma consulta de valor superior é uma consulta select que retorna um número especificado ou porcentagem de valores da parte superior dos resultados, por exemplo, as cinco páginas mais populares em um site. Você pode usar uma consulta de valores principais em relação a qualquer tipo de valores – eles não precisam ser números.
Se você quiser agrupar ou resumir seus dados antes de filiá-los, não é preciso usar uma consulta de valores principais. Por exemplo, suponha que você precise encontrar os números de vendas para uma determinada data para cada cidade na qual sua empresa opera. Nesse caso, as cidades se tornam categorias (você precisa encontrar os dados por cidade), para que você use uma consulta de totais.
Quando você usa uma consulta de valores principais para encontrar registros que contenham as datas mais recentes ou mais antigas em uma tabela ou grupo de registros, você pode responder a uma variedade de perguntas comerciais, como:
-
Quem tem feito mais vendas ultimamente?
-
Quando um cliente fez pela última vez um pedido?
-
Quando serão os próximos três aniversários na equipe?
Para fazer uma consulta de valor superior, comece criando uma consulta select. Em seguida, sort the data according to your question – whether you are looking for the top or the bottom. Se precisar agrupar ou resumir os dados, transforme a consulta select em uma consulta total. Em seguida, você pode usar uma função agregada, como Max ou Min, para retornar o valor mais alto ou mais baixo, ou First ou Last para retornar a data mais antiga ou mais recente.
Este artigo supõe que os valores de data que você usa tenham o tipo de dados Data/Hora. Se os valores de data estão em um campo Texto, .
Considere usar um filtro em vez de uma consulta de valores principais
Um filtro geralmente é melhor se você tiver uma data específica em mente. Para determinar se você deve criar uma consulta de valores principais ou aplicar um filtro, considere o seguinte:
-
Se você quiser retornar todos os registros em que a data corresponde, antes ou posterior de uma data específica, use um filtro. Por exemplo, para ver as datas de vendas entre abril e julho, você aplica um filtro.
-
Se você quiser retornar uma quantidade especificada de registros que tenham as datas mais recentes ou mais recentes em um campo e você não sabe os valores de data exatos, ou eles não importam, crie uma consulta de valores principais. Por exemplo, para ver os cinco melhores trimestres de vendas, use uma consulta de valores principais.
Para obter mais informações sobre como criar e usar filtros, consulte o artigo Aplicar um filtro para exibir os registros selecionados em um banco de dados do Access.
Preparar dados de exemplo para seguir junto com os exemplos
As etapas deste artigo usam os dados nas tabelas de exemplo a seguir.
A tabela Funcionários
Sobrenome |
Nome |
Endereço |
Cidade |
Egion CountryOrR |
Data de Nascimento |
Data de contratação |
Oliveira |
Manuel |
1 Main St. |
Nova York |
USA |
05-Feb-1968 |
10-Jun-1994 |
Heloo |
Waleed |
52 1st St. |
Boston |
USA |
22 de maio de 1957 |
22-Nov-1996 |
Gonçalo |
Guido |
3122 75th Ave. S.W. |
Seattle |
USA |
11-Nov-1960 |
11-Mar-2000 |
Rosca |
Jean Filipe |
1 Contoso Blvd. |
Londres |
UK |
22-Mar-1964 |
22-Jun-1998 |
Andrade |
Julian |
Calle Smith 2 |
Cidade do México |
México |
05-Jun-1972 |
05-Jan-2002 |
Hughes |
Christine |
3122 75th St. S. |
Seattle |
USA |
23-Jan-1970 |
23-Apr-1999 |
Riley |
Steve |
67 Big St. |
Tampa |
USA |
14-Apr-1964 |
14-Oct-2004 |
Birmby |
Dana |
2 Nosey Pkwy |
Rio de Janeiro |
USA |
29-Oct-1959 |
29-Mar-1997 |
A tabela EventType
TypeID |
Tipo de Evento |
1 |
Início do produto |
2 |
Função Corporativa |
3 |
Função Privada |
4 |
Arrecadador de Fundos |
5 |
Trade Show |
6 |
Palestra |
7 |
Concert |
8 |
Exibição |
9 |
Feira de Rua |
A tabela Clientes
CustomerID |
Empresa |
Contato |
1 |
Contoso, Ltd. Elemento gráfico |
Jonathan Haas |
2 |
Tailspin Toys |
Ellen Adams |
3 |
Fabrikam |
Carol Filipe |
4 |
Wingtip Toys |
Lúcio Iallo |
5 |
A. Datum |
Mandar Samant |
6 |
Empresa Aventura |
Brian Burke |
7 |
Design Institute |
Jaka Stele |
8 |
Escola de Arte Fina |
Milena Duomanova |
A tabela Eventos
IDDoEvento |
Tipo de Evento |
Cliente |
Data do Evento |
Andrade |
1 |
Início do produto |
Contoso, Ltd. |
4/14/2011 |
$10.000 |
2 |
Função Corporativa |
Tailspin Toys |
4/21/2011 |
$8.000 |
3 |
Trade Show |
Tailspin Toys |
01/05/2011 |
US$ 25.000 |
4 |
Exibição |
Instituto de Design Gráfico |
5/13/2011 |
$4.500 |
5 |
Trade Show |
Contoso, Ltd. |
5/14/2011 |
US$ 55.000 |
6 |
Concert |
Escola de Arte Fina |
5/23/2011 |
R$ 12.000 |
7 |
Início do produto |
A. Datum |
6/1/2011 |
$15.000 |
8 |
Início do produto |
Wingtip Toys |
6/18/2011 |
R$ 21.000 |
9 |
Arrecadador de Fundos |
Empresa Aventura |
6/22/2011 |
US$ 1.300 |
10 |
Palestra |
Instituto de Design Gráfico |
6/25/2011 |
US$ 2.450 |
11 |
Palestra |
Contoso, Ltd. |
04.07.11 |
US$ 3.800 |
12 |
Feira de Rua |
Instituto de Design Gráfico |
04.07.11 |
R$ 5.500 |
Observação: As etapas nesta seção pressuem que as tabelas Clientes e Tipo de Evento residem no lado "um" das relações um-para-muitos com a tabela Eventos. Nesse caso, a tabela Eventos compartilha os campos CustomerID e TypeID. As consultas totais descritas nas próximas seções não funcionarão sem essas relações.
Colar os dados de exemplo em planilhas do Excel
-
Inicie o Excel. Uma caixa de trabalho vazia é aberta.
-
Pressione SHIFT+F11 para inserir uma planilha (você precisará de quatro).
-
Copie os dados de cada tabela de exemplo em uma planilha vazia. Inclua os títulos de coluna (a primeira linha).
Criar tabelas de banco de dados a partir das planilhas
-
Selecione os dados da primeira planilha, incluindo os títulos da coluna.
-
Clique com o botão direito do mouse no Painel de Navegação e clique em Colar.
-
Clique em Sim para confirmar se a primeira linha contém títulos de coluna.
-
Repita as etapas 1 a 3 para cada uma das planilhas restantes.
Encontrar a data mais ou menos recente
As etapas nesta seção usam os dados de exemplo para ilustrar o processo de criação de uma consulta de valores principais.
Criar uma consulta de valores principais básicos
-
Na guia Criar, no grupo Consultas, clique em Design da Consulta.
-
Clique duas vezes na tabela Funcionários e clique em Fechar.
Se você usar os dados de exemplo, adicione a tabela Funcionários à consulta.
-
Adicione os campos que você deseja usar em sua consulta à grade de design. Você pode clicar duas vezes em cada campo ou arrastar e soltar cada campo em uma célula em branco na linha Field.
Se você usar a tabela de exemplo, adicione os campos Nome, Sobrenome e Data de Nascimento.
-
No campo que contém seus valores superior ou inferior (o campo Data de Nascimento, se você usar a tabela de exemplo), clique na linha Classificar e selecione Ascending ou Descending.
A ordem de classificação decrescente retorna a data mais recente e a ordem de classificação crescente retorna a data mais antiga.
Importante: Você deve definir um valor na linha Classificar somente para campos que contenham suas datas. Se você especificar uma ordem de classificação para outro campo, a consulta não retornará os resultados que você deseja.
-
Na guia Design, no grupo Ferramentas, clique na seta para baixo ao lado de Todos (a lista Valores Principais) e insira o número de registros que você deseja ver ou selecione uma opção na lista.
-
Clique em Executar para executar a consulta e exibir os resultados no exibição Folha de Dados.
-
Salve a consulta como NextBirthDays.
Você pode ver que esse tipo de consulta de valores principais pode responder a perguntas básicas, como quem é a pessoa mais antiga ou mais nova da empresa. As próximas etapas explicam como usar expressões e outros critérios para adicionar energia e flexibilidade à consulta. Os critérios mostrados na próxima etapa retornam os próximos três aniversários de funcionários.
Adicionar critérios à consulta
Estas etapas usam a consulta criada no procedimento anterior. Você pode seguir junto com uma consulta de valores principais diferentes, desde que ela contenha dados data/hora reais, não valores de texto.
Dica: Se você quiser entender melhor como essa consulta funciona, alternar entre o design e o exibição folha de dados em cada etapa. Se você quiser ver o código de consulta real, alternar para SQL exibição. Para alternar entre exibições, clique com o botão direito do mouse na guia na parte superior da consulta e clique no ponto de exibição que você deseja.
-
No Painel de Navegação, clique com o botão direito do mouse na consulta NextBirthDays e clique em Design View.
-
Na grade de design de consulta, na coluna à direita de BirthDate, insira o seguinte:
MonthBorn: DatePart("m",[BirthDate]).
Essa expressão extrai o mês de BirthDate usando a função DatePart. -
Na próxima coluna da grade de design de consulta, insira o seguinte:
DayOfMonthBorn: DatePart("d",[BirthDate])Essa expressão extrai o dia do mês de BirthDate usando a
função DatePart. -
Desmarcar as caixas de seleção na linha Mostrar para cada uma das duas expressões que você acabou de entrar.
-
Clique na linha Classificar para cada expressão e selecione Ascending.
-
Na linha Criteria da coluna Data de Nascimento, digite a seguinte expressão:
Month([Birth Date]) > Month(Date()) OR Month([Birth Date])= Month(Date()) AND Day([Birth Date])>Day(Date())Esta expressão faz
o seguinte:-
Month( [Birth Date]) > Month(Date()) especifica que a data de nascimento de cada funcionário cai em um mês futuro.
-
The Month([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date()) especifica que, se a data de nascimento ocorrer no mês atual, o aniversário cai sobre ou após o dia atual.
Em resumo, essa expressão exclui todos os registros em que o aniversário ocorre entre 1º de janeiro e a data atual.
Dica: Para obter mais exemplos de expressões de critérios de consulta, consulte o artigo Exemplos de critérios de consulta.
-
-
Na guia Design, no grupo Configuração de Consulta, digite 3 na caixa Retornar.
-
Na guia Design, no grupo Resultados, clique em Executar .
Observação: Em sua própria consulta usando seus próprios dados, às vezes você pode ver mais registros do que o especificado. Se seus dados contiver vários registros que compartilham um valor que está entre os valores principais, a consulta retornará todos esses registros, mesmo que isso signifique retornar mais registros do que você queria.
Encontre as datas mais ou menos recentes para grupos de registros
Você usa uma consulta de totais para encontrar as datas mais antigas ou mais recentes para registros que se enquadram em grupos, como eventos agrupados por cidade. Uma consulta total é uma consulta select que usa funções agregadas (como Group By, Mem, Max, Count, Firste Last) para calcular valores para cada campo de saída.
Inclua o campo que você deseja usar para categorias – para agrupar por – e o campo com valores que você deseja resumir. Se você incluir outros campos de saída – por assim dizer, os nomes dos clientes quando você estiver agrupando por tipo de evento – a consulta também usará esses campos para fazer grupos, alterando os resultados para que eles não respondam à sua pergunta original. Para rotular as linhas usando outros campos, crie uma consulta adicional que usa a consulta total como fonte e adicione os campos adicionais a essa consulta.
Dica: Criar consultas em etapas é uma estratégia muito eficaz para responder a perguntas mais avançadas. Se você estiver com problemas para obter uma consulta complicada para funcionar, considere se você pode rebaí-la em uma série de consultas mais simples.
Criar uma consulta de totais
Este procedimento usa a tabela de exemplo Eventos e a tabela de exemplo EventType para responder a esta pergunta:
Quando foi o evento mais recente de cada tipo de evento, excluindo os shows?
-
Na guia Criar, no grupo Consultas, clique em Design da Consulta.
-
Clique duas vezes nas tabelas Eventos e EventType.
Cada tabela aparece na seção superior do designer de consulta. -
Clique duas vezes no campo EventType da tabela EventType e no campo EventDate da tabela Eventos para adicionar os campos à grade de design de consulta.
-
Na grade de design de consulta, na linha Criteria do campo EventType, insira <>Concert.
Dica: Para obter mais exemplos de expressões de critérios, consulte o artigo Exemplos de critérios de consulta.
-
Na guia Design, no grupo Mostrar/Ocultar, clique em Totais.
-
Na grade de design de consulta, clique na linha Total do campo EventDate e clique em Max.
-
Na guia Design, no grupo Resultados, clique em Modo de Exibição e clique em Modo SQL.
-
Na janela SQL, no final da cláusula SELECT, logo após a palavra-chave AS, substitua MaxOfEventDate por MostRecent.
-
Salve a consulta como MostRecentEventByType.
Criar uma segunda consulta para adicionar mais dados
Este procedimento usa a consulta MostRecentEventByType do procedimento anterior para responder a esta pergunta:
Quem era o cliente no evento mais recente de cada tipo de evento?
-
Na guia Criar, no grupo Consultas, clique em Design da Consulta.
-
Na guia Consultas, clique duas vezes na consulta MostRecentEventByType.
-
Na guia Tabelas, clique duas vezes na tabela Eventos e na tabela Clientes.
-
No designer de consulta, clique duas vezes nos seguintes campos:
-
Na tabela Eventos, clique duas vezes em EventType.
-
Na consulta MostRecentEventByType, clique duas vezes em MostRecent.
-
Na tabela Clientes, clique duas vezes em Empresa.
-
-
Na grade de design de consulta, na linha Classificar da coluna EventType, selecione Ascending.
-
Na guia Design, no grupo Resultados, clique em Executar.