MeadInKent
| Feedback | Adding totals to a query dataset | Things to do with Queries |

Dividing a record into several rows in a query

It is possible to take an Access database record and split it into two or more separate records in a query. This could be useful for a finance journal if both the credit and the debit sides of a transaction are derived from a single record. Storing the details in one record can make comparing and checking the fields much easier.

The following example uses some accounting records which contain values to be transferred between different account codes.


Journal_Data: Table ox
RefNum Narrative Credit_Code Debit_Code Value
1 Printing invoice mis-coded PRIN-12300 PRIN-43200 450
2 Reallocation of drugs DRUG-44300 DRUG-12600 48
3 Reallocation of discount MEDI-50000 MEDI-13200 670
 

To achieve this it is necessary to create another Access table. Add a new table to your database, name it 'Temporary_Table' and give it a single numeric field called 'TempField1'. Add two records containing the values '1' and '2'.


Temporary_Table
TempField1
1
2
 

Now add a new query containing both the main table of data (Journal_Data) and also the Temporary_Table. The tables cannot be linked.


Journal_Transactions: Select Query (Design) ox
  tbl   tbl
Field: My_Narr: [narrative] & " (... MyAccountCode: IIf([tempfie... MyDebitOrCredit: IIf([te... Value
Table:     Journal_Data
Sort:  
Show: Y Y Y Y
Criteria:      
Or:        
 

Apart from the Value, all of the fields need to be user-defined:

Access 2010 Step by Step

 
UK more ...   USA more ...

The two records in the unlinked Temporary_Table cause the query to run twice and the IIF() functions (Immediate IF) cause it to switch between Credits and Debits as the TempField1 value alternates.


Journal_Transactions: Select Query ox
My_Narr MyAccountCode MyDebitOrCredit Value
Printing invoice mis-coded (001) PRIN-12300 credit 450
Printing invoice mis-coded (001) PRIN-43200 debit 450
Reallocation of drugs (002) DRUG-44300 credit 48
Reallocation of drugs (002) DRUG-12600 debit 48
Reallocation of discount (003) MEDI-50000 credit 670
Reallocation of discount (003) MEDI-13200 debit 670
 


file: accqueryjournal.htm Page last updated Mar14 MeadInKent.co.uk 2014