0

I have one table that shows all transactions with fields, chequeNum and accTransaction. It'll look something like this but a thousand more entries.

           **chequeNum     accTransaction**
                           withdrawal
                           withdrawal
                           withdrawal
               1                              
               2
               3
                           withdrawal

The second table is a table with only cheque transactions and the fields, chequeNum and accTransaction

           **chequeNum      accTransaction**
               3              John Deposit
               2              Jane Deposit
               1              Bob Deposit

The final outcome I want is:

           **chequeNum      accTransaction**
                            withdrawal
                            withdrawal
                            withdrawal
               1              Bob Deposit
               2              Jane Deposit
               3              John Deposit

Ultimately, i want to combine the two tables without altering the position of the transactions. There may also be instances where there isn't a cheque number in the table containing all cheque transactions to match the cheque number in the first table containing all transactions.

  • Just a nitpick there is nothing in your tables which can be used to sort, consequently there is no „order“. And if you use a third column you can have a very simple left join to solve it. – eckes Jan 27 at 10:07
0

Maybe you will be able to achieve this by using a UNION (query) and an APPEND query. Steps: 1 design a query (union) that will allow you to retrieve the data from the original tables, 2 create the new table, 3 design and run an "append" query that populates the new table.

Minimalistic example (Access 2016): 2 tables (Table1 and Table2), containing test data (data in id fields just arbitrary...)

enter image description here

1: Create a UNION query via the GUI. Include some SQL code.

  -- click ie use the GUI
  Create / Query design
  close the Show Tables dialogue box
  click Union

  -- type into the SQL query editor box 
  select chequeNum, accTransaction from Table1 where chequeNum is null
  union all
  select chequeNum, accTransaction from Table2
  ;

enter image description here

  -- click
  right-click the query tab, click save (Query1)
  close the Query tab

2: Create the new table.

  -- click
  Create / Table design

    id : AutoNumber (primary key)
    cheques: Short Text
    transactions: Short Text

  Save As Table3
  Close the tab (Table3)

enter image description here

3: Create the "append query".

  -- click
  Create / Query Design / [dialog box] Queries / Query1

  Fields: [double-click] chequeNum , accTransaction

  Design (ribbon): click Append / [dialog box] Append To: Table3
  Field selector / Append:
    Query1.chequeNum -> Table3.cheque
    Query1.accTransaction -> Table3.transaction

  Save As Query2

  Close Query2 (tab)

enter image description here enter image description here

Testing:

  Double-click Query2 
  -> Message: you are about to run an append query ...
  Click Yes
  -> Message: You are about to append ...
  Click Yes

  Double-click Table3 -> 6 rows have been INSERTED ("appended")

Execute the "append query". Table3: 6 rows have been appended.

  • Or you do a left join. – eckes Jan 27 at 10:06
  • @eckes - Last sentence (of the question): "There may also be instances where there isn't a cheque number in the table containing all cheque transactions to match the cheque number in the first table containing all transactions." How would we code the LEFT JOIN's ON (clause/search condition)? – stefan Jan 27 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.