Solved Duplicate records in a select query (1 Viewer)

[zelarra821]

Hello. I attach the database with an example on which I am going to expose the problem, so that it is easier to understand the problem. The database is structured in two tables, a main one to enter the budgets, and another sub-table, to enter the concepts of the budgets. In addition, there are two other tables, according to an example that @MajP gave me and that I adapted for this database, so that the client can break down each concept of the budget and decide which one to save later in the budgets sub-table. To create a query that groups and sums the different VAT (IVA), in which three fields of the table Presupuestos are involved: the item itself (artículo), the assembly (montaje) and the transport (transporte), I have created three queries equals in which I calculate the tax base (base imponible) and the VAT, and then create a union query, so that later in the report I only have to add by the budget number. The problem is that in the article query it duplicates the records for me. At first glance, it seems that the TDesglose and TConceptDesglose tables could be eliminated, but they are necessary to calculate the tax base. I have tried to change the type of relationship, but either it gives me an error or it does not solve it, and I do not know what else I can do. Someone could help me? Thanks.

 

[June7]

You have TIVA table linking to two fields in TPresupuestos. This doesn't work. Need to include TIVA table in query twice and have each instance link to one field. Just like the setup in Relationships builder.

And then you have circular linking.

I don't understand your data well enough to suggest how to correct.

 

[zelarra821]

Let's see if I can help you with an example. The database belongs to a company dedicated to the installation of windows, glass, etc. Well, when they make a budget, in the concept (which goes in the sub-table), they put the window, for example, and they have the option, because that is how they have asked me, to break it down from that window, hence those two tables of TConceptDesglose and TDesglose. To give you an example, the breakdown could consist of, on the one hand, the window, and on the other hand, the blind. Now, the subject of VAT (IVA), in TPresupuestos, there are two VATs (IVA) for transport (transporte) and assembly (montaje) carry VAT (IVA), and they are associated with a code that is in the VAT table (TIVA) that I have hidden because I did not think it was necessary for the case. In the query, I have the VAT (IVA) related as in the configuration of the relations, only that I have added one more relation to the one in the TDesglose table because the article (artículo) also carries VAT (IVA). Thanks.

 

[zelarra821]

In the end I managed to solve it by myself. I have removed the formula to calculate the taxable base from the VAT query of the item, because I already had that taxable base and that VAT rate in the budget subtable itself, so I no longer needed the breakdown tables. I had to modify another query for a name, and voila, fixed. Thank you very much for your contributions.