MS Access Query using criteria

0
votes

I'm new on MS access and I'm strugling in accomplish a query to find out which partners has not paid in a particular selected month/year.

I have 2 tables, one I have all the partners name (Sócios) and the second, the table (Registro de Pagamento) registered all the payments for all partners including the month & year of the payment.

I don't if the way I'm trying is the best.but , firstly, I created a Form to allow user to select the month (Combo box: TBoxMes) and the year (Combo box: TBoxAno) of his interest to find out which partners has not paid on selected period.

My idea then was to created a query (named as Selecionar_Mes_Cobranca) including partner's name and also the month and the year of each payment done from Registro de Pagamento table, I also have set the relation between the 2 tables as display all register from Socios table as the picture below is shown.

enter image description here

however, on the criteria field for the month and the year, I was trying to include the following expression <>[Forms]![Selecionar_Mes_Cobranca]![TBoxMes] into the Month field and the <>[Forms]![Selecionar_Mes_Cobranca]![TBoxAno] into the Year Field. The idea was to return only the partners name which has no register from the selected Month and Year from user, however, it has not worked and I would like some help to understand why and how to accomplish that.

Thanks in advance

2
ms-access

2 Answers

1
votes

using the '<>' will not provide you the results you are looking for. It will result in getting the payments that were NOT done in the specified months.

You have better change the JOIN cause and use the Codigo field, instead of the name. This will yeld better performance. I assume the Codigo (code of the socio) in Registro de pagamento is a foreign key and therefore has an index, also can not be a null value.
It would really be a good idea to revise your db structure in order to have foreign keys assigned to numeric indexed fields.
Suggestions: keep your field names the same across the database. This will make your life easier in the long run. Also avoid using spaces in them. You can use uppercase notation to help reading them (e.g. MesDoBoleto).

Then what you need is a NOT IN operator. That will extract the Socios who are NOT IN Registro de Pagamento in the specified months: You need something like this:

SELECT [Socio Nome],Codigo FROM Socios NOT IN (SELECT Codigo FROM [Registro de Pagamento] WHERE [Mes do Boleto]=[Forms]![Selecionar_Mes_Cobranca]![TBoxMes] AND [Ano do Boleto]=[Forms]![Selecionar_Mes_Cobranca]![TBoxAno]);

Please note that I have not used the accents in the field names, adjust accordingly.

Mind that IN, EXISTS, NOT IN and NOT EXISTS have their own quirks. Have a good read about them before use. You can find one example here: Consider Using NOT EXISTS instead of NOT IN

1
votes

I add here the code in case you cannot or don't want to change your table fields:

SELECT [SocioNome] FROM Socios NOT IN (SELECT [NomeSocio] FROM [RegistroDePagamento] WHERE [MesDoBoleto]=[Forms]![Selecionar_Mes_Cobranca]![TBoxMes] AND [AnoDoBoleto]=[Forms]![Selecionar_Mes_Cobranca]![TBoxAno]);


Please check that the field names in the query are as you set them.
Note that you may expect a poor performance from such a structure. Even more so if [SocioNome] and [NomeSocio] are not indexed.