Join between two tables in Access db does not work

Question

I have an Access database for testing which contains two tables, tbvendite and tbpagamenti. Into tbvendite I put every sell concerning the customers, specifying date, Idcliente, cliente, id operation and operation and the amount. Into tbpagamenti I write all payments made by customers that could be the sum of amount grouped by day of sell.

Conditions are that customer can buy in a day but he could not pay, so that he could pay previous suspended amount without making purchases. I should want extract a report for a customer (or a long report) but the query I thought about using does not produce the result I was hoping for.

SELECT tbvendite.data, tbvendite.idcliente, tbvendite.cliente, 
       Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS amount, 
       Nz(SS.importo,0) AS PAY
FROM tbvendite 
INNER JOIN (SELECT tbpagamenti.idcliente, 
            Sum(IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo)) AS Pay 
            FROM tbpagamenti 
            GROUP BY tbpagamenti.idcliente)  AS SS 
   ON tbvendite.idcliente = SS.idCliente
GROUP BY tbvendite.data, tbvendite.idcliente, tbvendite.cliente;

tbvendite

tbpagamenti

current results

Do you need to use a full outer join instead of inner? Provide expected results. given data provided. and label your existing screenshots so we know what they are showing. — xQbert, Apr 04 '22 at 20:50
I wrote label for each image, sorry. I tryed to change to full outer join but access show a syntax error message. — Jayelef, Apr 04 '22 at 21:00
I forgot access doesn't support full outer... Switch to left then run it. then right and run it and see if either one is getting closer to your need. If you need both results then you have to write a a Left, then right then union to combine the two results. and I'm still missing the labels/headers above the tables (visually) — xQbert, Apr 04 '22 at 21:03
Neither left nor right produce any results. Unfortunately, column 'Pay' contains only 0. — Jayelef, Apr 04 '22 at 21:07
doesn't your join need to include data not just idcliente if you want the pay to show on the day it was received? (What's expected results it will help clairify) I see since a payment could occur without a sale, date would be missing... one could exist without the other — xQbert, Apr 04 '22 at 21:12
ok I think teh union approach instead of join is needed since not all dates match on both sides. Or, you need a dates table which includes all dates from both. We could build it as a query using a union.. or just use a union and then aggregrate. — xQbert, Apr 04 '22 at 21:41
Yes, the query result should obviously include the date and what you say is correct, there could be a purchase without payment (as I explained above) and there could be the payment of a previous purchase without however a purchase on the same date. — Jayelef, Apr 04 '22 at 21:42
Actually I also tried a union query, but as you know the result is returned on different rows and this is something I wanted to avoid. But if I really can't set up the extraction with a join query I could try to process a union query and see what comes out. Do you have an idea how I can set it up? — Jayelef, Apr 04 '22 at 21:44
I've provided two approaches both however use a union at different levels. The issue is you want payments/sales occurring on the same date to show on the same line. and you need all dates. This by nature is a full outer join. you could look at online help to see how to simulate one in access or try using one of the below approaches. [Simulate Full outer join in access](https://stackoverflow.com/questions/24700881/simulate-full-outer-join-with-access-on-more-than-two-tables) — xQbert, Apr 04 '22 at 21:48

xQbert · Accepted Answer · 2022-04-05T11:58:57.440

1

One way to do this visually in access would be to

create a UNION view which uses both the views created for the tables
create a 2nd query with the desired aggregation .
run the 2nd query

So step

Combine your two tables into 1 using a union and save it as a query in the database named "qryDailySalesPay" for this example.
sum and aggregate the information by data, idcliente, cliente.

qryDailySalesPay:

SELECT tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente
      , iif(tbvendite.importo is null,0,tbvendite.importo) AS Amount
      , 0 AS PAY
FROM tbvendite
UNION ALL
SELECT tbpagamenti.data
      , tbpagamenti.idcliente
      , tbpagamenti.cliente 
      , 0 as amount
      , IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo) AS Pay 
 FROM tbpagamenti

qryDailySalesPaySummary:

SELECT data, ideliente, cliente, sum(amount) as SumAmount, sum(Pay) as SumPay
FROM qryDailySalesPay
GROUP BY data, idcliente, cliente;

MSFT doc on union

Alternate Approach using a derived "DatasALL" table. -- note; you could make this a qrySalesAmountDatas and replace the derived table.

Get all dates
get all sales which match those dates
get all payments whihc match those dates
ensure clients match between sales and payments
aggregate totals.

.

SELECT tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente
      , Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS SumAmount
      , Sum(IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo)) AS SumPay 
FROM (SELECT Distinct data FROM tbvendite 
      UNION
      SELECT DISTINCT data from tbpagamenti) AS DatasAll
LEFT JOIN tbvendite
 on DatasAll.data = tbvendite.data
LEFT JOIN tbpagamenti 
 on DatasAll.data = tbpagamenti.data
AND tbvendite.idcliente=tbpagamenti.cliente
GROUP BY tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente

edited Apr 05 '22 at 11:58

answered Apr 04 '22 at 21:19

xQbert

34,733
2
41
62

Updated to how Access does this: been 20+ years had to re-read some documentation. – xQbert Apr 04 '22 at 21:38
I can imagine! Well, I have tried the query above, but access show a message of syntax error (missing operator). I haven't tested the first one you wrote because honestly I haven't understood it yet. I try to look at it carefully. however, consider that I will use the query within a visual studio project, at the moment I am testing directly on the db access that will have to be used. But it wouldn't change anything – Jayelef Apr 04 '22 at 21:52
the 1st one requires building a query/view and saving it first. 2nd you query the view created and do the needed aggregation. – xQbert Apr 04 '22 at 21:54
Yes, I saw it. But you have named 1st query qryDailySalesPay then you recal it as 'FROM myUnionQuery'. Something escapes me? – Jayelef Apr 04 '22 at 21:58
Forget about it! I tested the first query. Access returns an error 'query not include Data as aggregation field – Jayelef Apr 04 '22 at 22:02
*Sigh* shouldn't be aggregating in the union correcting that... corrected.... sucks not having a version of access I could compile test with quickly :P – xQbert Apr 04 '22 at 22:03
2

well hopefully one of these two works for you. I know the concept is sound in mySQL, Maria DB, SQL server, Hana, DB2, Oracle.... all because an outer join doesn't exist in MSAccess... and I am having syntax nightmares in MSAccess. may have needed to add the "AS" operator on the aliased table.. in the 2nd query. Dormi bene, il migliore dei sogni – xQbert Apr 04 '22 at 22:08
1

Ehilaaa! It seems to work correctly! I had to change the two names Amount and Pay because they created a circular reference that access doesn't accept, but at first glance, it seems to work! And now? What will I have to do to be able to use it through VS2019? Leave this query stored in the database I suppose ... – Jayelef Apr 04 '22 at 22:11
Why not. It's just a view like any other rdbms and operates much like a table; aside from being able to update data using it. – xQbert Apr 04 '22 at 22:12
1

Thank you so much @xQbert. E il migliore dei sogni anche a te! During this week I will try using the real db with all data I need. I hope I don't have to write you about another problem again! – Jayelef Apr 04 '22 at 22:13
1

So, as I have noticed, whatever selection I want to make, I just need to apply the filters on the query that calls the union query. In fact I did a further test and it works perfectly with this variation: 'FROM qryDailySalesPay where val (qryDailySalesPay.idcliente) = 1' – Jayelef Apr 04 '22 at 22:21
As is the intent. When you build on a query, all the information in the query you build on should be available to the 2nd query. However only the fields you select in the 2nd query are available to those who use the 2nd query; and with the limits imposed within the 2nd query. I do worry a bit about performance larger datasets. I do worry about performance on larger datasets as the union will take time to execute. – xQbert Apr 05 '22 at 12:03
Yes, That's exactly what I thought. Probably on very large datasets you will notice a slowdown in the execution of the command, but I will evaluate it when I have data available. Furthermore, no one prevents me from limiting the exercise to a single year and creating multiple data sources. I do not know yet – Jayelef Apr 05 '22 at 13:33

score 0 · Answer 2 · answered Apr 04 '22 at 18:33

0

I think you need to replace Nz (SS.importo, 0) to SS.Pay "importo" does not belong in the subquery (SS)

Regards

answered Apr 04 '22 at 18:33

Thanks @FJTV, i have tried but with the replacement i got an error. Importo is the name of the field in the first table . Maybe I dont understand correctly? Something Like this? Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS amount, Nz(SS.PAY,0) AS PAY – Jayelef Apr 04 '22 at 19:36
`Nz(SS.importo,0) AS PAY` to `SS.Pay` you've already handled the nulls and sum in the subquery. – xQbert Apr 04 '22 at 21:09
Well, something seems to be changed. Now I have some data into the column Pay, however it needs insert SS.Pay in the grouping criteria and I don't think it's quite right – Jayelef Apr 04 '22 at 21:18
that is right. Pay doesn't need to be sumed, it already has been in teh sub query... or you can do `sum(ss.pay) as pay` and let the group by what it needs to. the problem is since you're not joining on data, pay will be the same for all records in cliente. but you can't join on data since both sides don't have the same dates. – xQbert Apr 04 '22 at 21:20
1

No, no ;-) this is the string Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS amount, SS.PAY... etc but this is the last line GROUP BY tbvendite.data, tbvendite.idcliente, tbvendite.cliente,ss.pay That's no good, because grouping also SS.Pay, access sum the amount for customers and give same total as global sum. I hope I was clear – Jayelef Apr 04 '22 at 21:24
yes, clear. Then this will not work since the JOIN you're using only includes idcliente. without joining on data, you would get same line and total for a customer. and if you added date, you'd lose some records also not good. That's why I wanted to use a full outer join to begin with and include data; but MS access doesn't support full outer join. – xQbert Apr 04 '22 at 21:28
exactly. I have already tried several queries to solve this problem but none of them work correctly. I also tried a similar query using three tables linked by customerid, and as long as I don't include the sum in the first query, they work fine. If I create the sum in the first table, disaster happens. I don't know how to get out of it. – Jayelef Apr 04 '22 at 21:38

Join between two tables in Access db does not work

2 Answers2