How to join columns horizontally from multiple tables and then group by date.

[bubai]

In my database I save all the consignments details in Cons table. Supplier payment goes through 3 methods; Bank, Discount and Under. Every supplier's payment does not necessarily involve all three methods. That's why I have created 3 separate tables for separate types of payments. But I need to make a consolidated query whereby I will get 3 types of payments side by side along with payment date and supplier ID. End result should look like the pic below.

There is common date ie 08/08/2020 where I have both Bank and Discount payment to same supplier. So, those two transactions should appear in the same line ans in the pic. I have created 3 queries TrBank, TrDisc and TrUnd, but don't know how to join them.
I don't have a clue how to achieve this.
Database is attached for your kind inspection. x - Copy.zip

[ranman256]

create Q1 to pull your needed data.(date range,other criteria)
then using the Query Wizard, create a CROSSTAB query (using Q1 as source)
it will ask what do use for
row heading
column headings
values.

[bubai]

create Q1 to pull your needed data.(date range,other criteria)
then using the Query Wizard, create a CROSSTAB query (using Q1 as source)
it will ask what do use for
row heading
column headings
values.

Doesn't work.
I need to marge FK_SuppID and Date columns from 3 queries. Then grouping on the rows to eliminate duplicate values and do aggregate sum. CROSSTAB is not working that way.

[ArviLaanemets]

As start change your design. Instead of 3 tables for supplier payments you need one with an additional field for payment type.

[bubai]

As start change your design. Instead of 3 tables for supplier payments you need one with an additional field for payment type.

I can't do that.
Putting everything in one table will create huge wasted space within the records along the least used fields and will contradict to Normalization.

[ArviLaanemets]

I can't do that.
Putting everything in one table will create huge wasted space within the records along the least used fields and will contradict to Normalization.

??? !!!
An example
tblPayments:
PaymentID, SupplierID, PaymentDate
1 1 01/11/2019
2 1 02/09/2020
3 2 14/05/2019
4 2 08/08/2020
5 3 02/08/2020

tblPaymentRows:
PaymentRowID, PaymentID, PaymentType, PaymentAmount (PaymentType values are 1 for 'Bank Amt', 2 for 'Disc Amt', and 3 for 'Und Amt')
1 1 1 3000
2 2 1 1000
3 3 1 50000
4 4 1 40000
5 4 2 60000
7 5 2 50

Where is the wasted space?

[bubai]

??? !!!
An example
tblPayments:
PaymentID, SupplierID, PaymentDate
1 1 01/11/2019
2 1 02/09/2020
3 2 14/05/2019
4 2 08/08/2020
5 3 02/08/2020

tblPaymentRows:
PaymentRowID, PaymentID, PaymentType, PaymentAmount (PaymentType values are 1 for 'Bank Amt', 2 for 'Disc Amt', and 3 for 'Und Amt')
1 1 1 3000
2 2 1 1000
3 3 1 50000
4 4 1 40000
5 4 2 60000
7 5 2 50

Where is the wasted space?

But my PaySupplier_Bank table has two additional Yes/No options fields (PayInstruction, PayDone) which are not applicable to other two types of payment tables. That is another reason I have gone with this structure.

[ArviLaanemets]

Add a table like tblPaymentOptions:
(in case both options can be selected concurrently)
PaymentOptionID, PaymenRowID, PayInstruction, PayDone;
(or in case only one may be active at given time, or in case you want to see the state of both options)
PaymentOptionID, PaymenRowID, PaymentOption.

In entry form, payment options will be available only for one payment type.

[CJ_London]

Not sure where you get the idea that having columns rarely used wastes space. Access does not ‘reserve’ space for blank fields.Ok, yes/no fields store a 0 or -1 but that is a tiny amount of data and usually better to store a more informative value such as a date which will be null if false (not saying that is relevant here)

Normalisation is not about saving space it is about grouping the same types of objects together. Would you have two tables for people - one for those with first and last name and another for those with middle names?

It may be your payment types should be treated as different objects, but from your description, it doesn’t sound like it, and it makes your subsequent interrogation of the data more complex