Creating duplicate records

[mreference]

I am in the process of creating a query that lists details in a CSV file so that it can be uploaded to Oracle.

The way the CSV needs to be set up is to list details of the debit on one row, then duplicate it and show the credit on another. The only difference is the account numbers credited/debited and the value (lets say -£500 is so be debited, so the credit will be £500)

I have all the details when a record is input fro the user, however I only have one value and the account number for crediting/debiting is stored in the one record.

I hope I'm explaining myself ok.

What I need is for access to take a record that is input, and create a duplicate, but make the hoursworked a negative field so that when it is multiplied with the hourlyrate it creates a negative rate.

For example
Joe Bloggs inputs his time for a day, 4 hours which already has underlying data, account number to be debited and credited etc

This would give me a fee of £80 if the rate was £20 per hour

I want a duplicate record of this, but would turn the 4 hours to -4 hours, so when it is multiplied the rate would be -£80.

This would then give me the two lines I need for each record to be included in a report to be uploaded to Oracle.

Is this possible?

 

[DCrake]

First step is to create a query with the desired fields in it along with a flag to indicate whether a DR or CR.

Then create an identical query to the above and change the flag to CR. Then in your value field do as follows


Amount:[YourField]*-1

This will turn the debit into a credit

Finally create a union query using both of the above queries and sort by primary key. This will give you both queries with a debit followed by a credit for all records