Query that returns the first record of each client depending on a filter (1 Viewer)

[ghnogueira]

Hello!

I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.

Consider the table below:

Process		Contact date		Salesman	Client	Client
Key							Key
999		04/28/2014 11:39:00	SALESMAN 1	111	CLIENT 1
888		04/28/2014 10:13:00	SALESMAN 1	222	CLIENT 2
555		04/22/2014 14:29:00	SALESMAN 2	333	CLIENT 3
666		04/16/2014 12:14:00	SALESMAN 3	444	CLIENT 4
555		04/16/2014 11:47:00	SALESMAN 2	333	CLIENT 3

If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).

If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).

If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).

I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.

What should I do?

Thanks!!!

 

[pr2-eugin]

Hello ghnogueira, Welcome to AWF

You need to use the concept of SubQueries.

 

[ghnogueira]

Hello ghnogueira, Welcome to AWF

You need to use the concept of SubQueries.

Hey pr2-eugin! Thank you for the welcome

I tried using the code on the "top n results", but it didn't really change my results, to be honest. I set top show the top 1 result, but it still showed all the results from CLIENT 3, for example.

I'd like to point out there's no primary key on that table so far. I don't know whether that's a must or not for that code to work.

 

[jdraw]

Please show us the SQL you used; your table structure; and exactly what you want as result.

 

[ghnogueira]

Please show us the SQL you used; your table structure; and exactly what you want as result.

Sure. The desired results are on my initial post. Do you think I need to put them down as a table or can you get it from the text I wrote?


This is the original SQL, just grabbing data from the table

SELECT Dados.[Código do processo], Dados.[Data do contato], Dados.Vendedor, Dados.[Código do cliente], Dados.Cliente
FROM Dados;

This was my first attempt, but one that failed due to the reasons explained on the original post

SELECT Dados.[Código do processo], First(Dados.[Data do contato]) AS [PrimeiroDeData do contato], First(Dados.Vendedor) AS PrimeiroDeVendedor, First(Dados.[Código do cliente]) AS [PrimeiroDeCódigo do cliente], First(Dados.Cliente) AS PrimeiroDeCliente
FROM Dados
GROUP BY Dados.[Código do processo]
HAVING (((First(Dados.[Data do contato]))<=#2/22/2014 16:5:0#));

This is what I made up with the concept from that subquery tutorial (top "n" records per group)

SELECT Dados.[Código do processo], Dados.[Data do contato], Dados.Vendedor, Dados.[Código do cliente], Dados.Cliente
FROM Dados
WHERE (((Dados.[Código do processo]) In (SELECT TOP 1 [Código do processo]
FROM Dados AS Dupe
WHERE Dupe.[Código do processo] = Dados.[Código do processo]
ORDER BY Dupe.[Data do contato] DESC, Dupe.[Código do cliente])))
ORDER BY Dados.[Código do processo];

 

[sumox]

DID YOU TRIED FIRST FUNCTION
First (SupplierID)
Try it , and then revert. It is for returning First Recordset of as per Expression
given

or you can try Duplicate Query wizard for the same, as it shall reveal
the same results i think, i suppose .

Pls. check n let me know
Experts Pls advice on my comment/post

 

[sumox]

upload your Sample DB with Sample Data
so anyone can check n post the needful code

 

[ghnogueira]

Thanks for the help sumox!!!

Here's how the proper code looks like:

SELECT First(tb_Dados.[Grupo]) AS Grupo, tb_Dados.[Número de conta], tb_Dados.[Código do processo], Max(tb_Dados.[Data do contato]) AS [Data do contato], First(tb_Dados.Vendedor) AS Vendedor, First(tb_Dados.[Código do cliente]) AS [Código do cliente], First(tb_Dados.Cliente) AS Cliente
FROM tb_Dados
WHERE tb_Dados.[Data do contato] >= 41730 AND tb_Dados.[Data do contato] <= 41759
GROUP BY [Número de conta], [Código do processo]
ORDER BY [Número de conta], Max(tb_Dados.[Data do contato]) DESC , First(tb_Dados.Vendedor);

 

[sumox]

ok. seen.

so, now what's the problem. which egg u required not fetched by ACCQuery

 

[ghnogueira]

Sorry, I didn't get your last sentence...