| Feedback | Adding totals to a query dataset | Things to do with Queries | |
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 | |||
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:
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 |