Convery values to negative in a query

[PC]

Hi,

I am creating an fixed width text file from data extracted from an access
table. This FW text file will be used to import data into our Accounts
package. In order for the import to work the values have to balance to zero
(i.e. debits and credits etc)

My question is what is the best way to create a query (or table from a
query) that displays the results of a select query on line 1 and the
negative of this result on line 2 which can then be exported to a file.

For example:
Contents of [tblInvoices]

Name Reference Amount
Bloggs Goods In ?100


Required Query Result

Bloggs Goods In $100 D
Bloggs Goods In -$100 C

I hope I've explained this clearly. If not let me know.

Thanks in advance.

...pc

 

[John Spencer (MVP)]

How about a Union Query

SELECT [Name], Reference, Amount, "D" as DebitCredit
FROM [tblInvoices]
UNION ALL
SELECT [Name], Reference, Amount*-1, "C"
FROM [tblInvoices]
ORDER BY [Name], Reference, DebitCredit Desc

 

[PC]

Thanks for the reply John,

This is nearly what I'm looking for. The result of this query creates a list
of all the Debits followed by all the credits (ddddcccc). What I'm hoping is
for the query to return Debits on 1 line and credits on the next (dcdcdcdc)

Any ideas?

...pc

How about a Union Query

SELECT [Name], Reference, Amount, "D" as DebitCredit
FROM [tblInvoices]
UNION ALL
SELECT [Name], Reference, Amount*-1, "C"
FROM [tblInvoices]
ORDER BY [Name], Reference, DebitCredit Desc

Hi,

I am creating an fixed width text file from data extracted from an access
table. This FW text file will be used to import data into our Accounts
package. In order for the import to work the values have to balance to zero
(i.e. debits and credits etc)

My question is what is the best way to create a query (or table from a
query) that displays the results of a select query on line 1 and the
negative of this result on line 2 which can then be exported to a file.

For example:
Contents of [tblInvoices]

Name Reference Amount
Bloggs Goods In ?100

Required Query Result

Bloggs Goods In $100 D
Bloggs Goods In -$100 C

I hope I've explained this clearly. If not let me know.

Thanks in advance.

..pc

 

[PC]

Scratch that John, left out the "Order by"

Thanks Again.

Thanks for the reply John,

This is nearly what I'm looking for. The result of this query creates a list
of all the Debits followed by all the credits (ddddcccc). What I'm hoping is
for the query to return Debits on 1 line and credits on the next (dcdcdcdc)

Any ideas?

..pc

How about a Union Query

SELECT [Name], Reference, Amount, "D" as DebitCredit
FROM [tblInvoices]
UNION ALL
SELECT [Name], Reference, Amount*-1, "C"
FROM [tblInvoices]
ORDER BY [Name], Reference, DebitCredit Desc

Hi,

I am creating an fixed width text file from data extracted from an access
table. This FW text file will be used to import data into our Accounts
package. In order for the import to work the values have to balance to zero
(i.e. debits and credits etc)

My question is what is the best way to create a query (or table from a
query) that displays the results of a select query on line 1 and the
negative of this result on line 2 which can then be exported to a file.

For example:
Contents of [tblInvoices]

Name Reference Amount
Bloggs Goods In ?100

Required Query Result

Bloggs Goods In $100 D
Bloggs Goods In -$100 C

I hope I've explained this clearly. If not let me know.

Thanks in advance.

..pc