Splitting "sum" column into two columns, depending on value of another colum

[BassettProvidentia]

Splitting "sum" column into two columns, depending on value of another colum

Hi,

I have a "posting" table that contains records relating to single transactions in a double ended bookkeeping system. Each real world transaction has two postings, a positive one, and a negative one.

Buying a car:

£20,000 "credit" posting against the account "purchases"
£20,000 "debit" posting against the account "motor vehicles"

My posting table looks like this:

posting_id - int, primary key
account_id - in, foreign key
date - timestamp
sum - decimal
type - either "credit" or "debit"

I need to have a query where all postings will be listed, with each "credit" posting in one column and each "debit" in another. Each "credit" posting will have a NULL value in the "debit" column, and vice versa.

I have tried using an union query. As this seems like quite a common problem, does anyone have any suggestions?

Thanks!

 

[Galaxiom]

Re: Splitting "sum" column into two columns, depending on value of another colum

A Union query is for putting different fields into the same field of the query. The opposite of what you want.

I assume you want the pair of postings on the same row in the query. You really should have a field for a common value that indicates that they are a pair.

Instead of the PostingID as the key you might consider using this and the Credit/Debit field as a composite primary key. This will also ensure there can only be two entries for each number, one a debit and the other a credit.

Then you can simply self join the table on this field and select the fields as appropriate with an IIF based on the Credit or Debit field.

You should abandon using the string to indicate debit and credit and instead use a Boolean (Yes for Credit, No for Debit for example). This will be much, much faster than testing for matching strings in a very important part of your database.

Sum, Date and Type are all reserved words and you should change these field names.

You field type for the IDs should be Long as Integer is limited to somewhat less than 33,000.

 

[BassettProvidentia]

Re: Splitting "sum" column into two columns, depending on value of another colum

Hi GalaxiomAtHome,

Thanks for your reply.

I only really posted the fields I thought were necessary - for completeness though, here they are (taking into account your suggestions):

table: posting

posting_id - long (primary key; autonumber)
account_id - long (foreign key)
posting_date - timestamp
assettype_id - int (foreign key)
journal_id - long (foreign key)
posting_sum - decimal
posting_type - bool (true = cr, false = dr)

table: journal

journal_id - long (primary key; autonumber)
description - text

Each journal entry represents a real-life transaction, for example: "purchase car"

Each posting represents one entry. There could well be more than two per journal, but the sum of all credit postings per journal will always cancel out the sum of all debit postings to make zero.

For this query, I don't want the credit and debits to occupy the same row. I want each posting to have it's own row, but for the "posting_sum" field value for credits in a different field than for debits.

I will use the query for a form, where all postings (for an account) will be displayed in rows, with the credit figures in one column, and the debit ones in another, like on a bank statement.

Thanks for your help.

 

[Galaxiom]

Re: Splitting "sum" column into two columns, depending on value of another colum

Just need the IIF to separate the amounts into two columns.

Credits: IIF(posting_type,posting_sum,Null)
Debits: IIF(posting_type,Null,posting_sum)

 

[BassettProvidentia]

Re: Splitting "sum" column into two columns, depending on value of another colum

Thanks! That was exactly what I was after.

 

[ash1123]

Re: Splitting "sum" column into two columns, depending on value of another colum

BassettProvidentia pls use thanks button to thank, it will be highly appreciated.