I need your help with something in MS Access. I have a table with debits and credits. how can i group rows by the same amount of debit and credit and append them in another table. I want to query to start matching rows appending in another table and deleting from original until there is no more...

How do u suggest i should do that...

I cant find a way...

I am not sure what you are trying to do here. You want to copy identical debit or credit amounts from one table to another, and then delete the amounts from the first table?

Why would you want to do that?

Use an Append query to move the amounts, and a Delete query to remove them.

<scratching head>

HTH


mmcdonal

Imagine i have a table with lots of lines with debits and credits. I managed to match the entries by amount and another field. Now i want to use the data to make the entries. See lines down...
    Date Org FACCT CSUB P/S SOBP Cntry Debits Credits
25-Dec-06 632120600 52156000 RN00788788 2400 USD
25-Dec-06 632120600 52156000 RN00788788 6000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 2400 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 6000 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD

as you can see to make the double entry i need to creat tables for each country code and then take the details necessary for each country..its complicated..

So your problem is an Order By issue. You want this?

Date Org FACCT CSUB P/S SOBP Cntry Debits Credits
25-Dec-06 632120600 52156000 RN00788788 2400 USD
25-Dec-06 632220600 52159020 RN00788788 2400 USD
25-Dec-06 632120600 52156000 RN00788788 6000 USD
25-Dec-06 632220600 52159020 RN00788788 6000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD
25-Dec-06 632120600 52156000 RN00788788 3000 USD
25-Dec-06 632220600 52159020 RN00788788 3000 USD

And then you want to move the pairs?

mmcdonal

Yes thats what i want to do.. and i want to move the pairs in a new table and giving the name of the first RN00788788eg number that comes across.
then i will end up with tables of the different entries.

Imagine i have a table with lots of lines with debits and credits and a project number which can be used to match the lines.

i want to group these Debits and credits together and create entries on for both entities. there are two scenarios here..

First scenario

One entity (country) and expense account. You can see that in the txt file am attaching the country codes in both debits and credits are the same.

Second Scenario

two entities(countries) and expense account. You can see that in debit and credits the country code is different.(intercompany charges)

eg.

Country A and B and expense account

In country A books

DR Country B account
CR Expense Account


In Country B Books

Dr Expense
CR Country A Account


i know this must be confusing but i have no idea how to proceed and am stuck. I would appreciate your help. I can attach sample of the data to understand what i mean, but where i cant find an attachment option.Maybe i can email it to you somehow..

Please email me the text file with raw data, and add a text file you made yourself that shows how you want the data to look post processing (maybe 10 records or so). Then we can work on the business rules and write some code.



mmcdonal

Sorry to ask but i dont have your email and when i go to your profile to email you i cant attach anything there..

this is my email.. [email protected]

if you dont want to put your email here send me an empty email and i will reply.. you will think am stupid but am new here:)