locked
Merging two table in a query that are not related to each other and use it for a report RRS feed

  • Question

  • I have a table that its name is "tblCashReceipt" , I use it for entering the cash that we receive in our company during work. Another table that its name is "tblPayCash" is used for entering the money that we pay during the working day they have separate forms for entering data. I want to make a report that shows the amounts received and paid during a time interval (for example one day or week) but I cannot. Because they are two separate tables and there is no meaningful field between them. When I add fields from two table to query design, there is no data in result. How can I solve the problem?

     

    Karim Vaziri Regards,

    Friday, September 16, 2016 7:28 PM

Answers

  • You could use a Union query for this. A Union query can only be edited in SQL view, not in Design view.

    • On the Create tab of the ribbon, click Query design.
    • Close the Show Table dialog without selecting a table.
    • Select SQL from the View button on the left hand side of the ribbon.
    • Edit the SQL to look like this:

    SELECT [Date], Amount, AccountID, InsurancePolicyID, Description, Signature, CertifyDate, 
    "Received" AS TransactionType FROM tblCashReceipt
    UNION SELECT [Date], Amount, AccountID, InsurancePolicyID, Description, Signature, CertifyDate, 
    "Paid" AS TransactionType FROM tblPayCash

    Save the query and use it as Record Source for your report.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kvaziri Friday, September 16, 2016 8:14 PM
    Friday, September 16, 2016 7:54 PM
  • Actually, you don’t really need a “join”, but a “union”.

    What you can do is build a union query. It will essentially view both tables as ONE merged table. You then base your report on that query. And you can even apply a CerfityDate criteria to limit the report to a given date value or range – all records from both tables will thus appear in the report.

    The only “trick” part is the query designer does not allow graphical design of a union query, you have to build it manually.

    However, being lazy, here, here’s how to do this without having to type everything in.

    Create a new query – add table tblCashReceipt.

    Now, add all of the columns to the query grid – important ONLY add columns that are common to the two tables (so add everything but the last locked column). You can even double click on each field name and note how the field is placed in the query grid for you.

    Save the query (it will be junk and tossed out later).

    Now, do the same as above with the second table – create that query, and then save that query.

    Now create a NEW BLANK query (cancel the add table stuff).

    Switch to SQL view.

    Now, open up the first query, sql view, and cut + paste in the sql from that first query into the new blank query.

    You have something like this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson;

    Remote the “;” and add this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson

    UNION ALL

    Now open up the second query, and again sql view, and cut + paste in the second query

    You have something like this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson

    UNION ALL

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson2

    I removed the “;” in both cases – likely you do the same.

    Now save this query. In above I have two tables (tPerson, and tPerson2).

    At this point you can now run/test this query. It will be query that combines both tables. You base the report on this query – and you can even pass/filter that report by a given date range.

    Regards

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    • Marked as answer by kvaziri Friday, September 16, 2016 8:21 PM
    Friday, September 16, 2016 8:07 PM

All replies

  • You could use a Union query for this. A Union query can only be edited in SQL view, not in Design view.

    • On the Create tab of the ribbon, click Query design.
    • Close the Show Table dialog without selecting a table.
    • Select SQL from the View button on the left hand side of the ribbon.
    • Edit the SQL to look like this:

    SELECT [Date], Amount, AccountID, InsurancePolicyID, Description, Signature, CertifyDate, 
    "Received" AS TransactionType FROM tblCashReceipt
    UNION SELECT [Date], Amount, AccountID, InsurancePolicyID, Description, Signature, CertifyDate, 
    "Paid" AS TransactionType FROM tblPayCash

    Save the query and use it as Record Source for your report.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kvaziri Friday, September 16, 2016 8:14 PM
    Friday, September 16, 2016 7:54 PM
  • Actually, you don’t really need a “join”, but a “union”.

    What you can do is build a union query. It will essentially view both tables as ONE merged table. You then base your report on that query. And you can even apply a CerfityDate criteria to limit the report to a given date value or range – all records from both tables will thus appear in the report.

    The only “trick” part is the query designer does not allow graphical design of a union query, you have to build it manually.

    However, being lazy, here, here’s how to do this without having to type everything in.

    Create a new query – add table tblCashReceipt.

    Now, add all of the columns to the query grid – important ONLY add columns that are common to the two tables (so add everything but the last locked column). You can even double click on each field name and note how the field is placed in the query grid for you.

    Save the query (it will be junk and tossed out later).

    Now, do the same as above with the second table – create that query, and then save that query.

    Now create a NEW BLANK query (cancel the add table stuff).

    Switch to SQL view.

    Now, open up the first query, sql view, and cut + paste in the sql from that first query into the new blank query.

    You have something like this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson;

    Remote the “;” and add this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson

    UNION ALL

    Now open up the second query, and again sql view, and cut + paste in the second query

    You have something like this:

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson

    UNION ALL

    SELECT tPerson.Person_ID, tPerson.Person_Name

    FROM tPerson2

    I removed the “;” in both cases – likely you do the same.

    Now save this query. In above I have two tables (tPerson, and tPerson2).

    At this point you can now run/test this query. It will be query that combines both tables. You base the report on this query – and you can even pass/filter that report by a given date range.

    Regards

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    kallal@msn.com

    • Marked as answer by kvaziri Friday, September 16, 2016 8:21 PM
    Friday, September 16, 2016 8:07 PM
  • Dear Hans,

    It is perfect. I surprised when I used it. I need this type of query a lot in financial cases.

    Thanks a lot


    Karim Vaziri Regards,

    Friday, September 16, 2016 8:14 PM