Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 22nd, 2007, 06:07 AM
Authorized User
 
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Finding identical values

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...
 
Old January 22nd, 2007, 08:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 23rd, 2007, 01:50 AM
Authorized User
 
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..




 
Old January 23rd, 2007, 08:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 29th, 2007, 06:26 AM
Authorized User
 
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old January 30th, 2007, 03:57 AM
Authorized User
 
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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..



 
Old January 30th, 2007, 08:28 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 30th, 2007, 08:43 AM
Authorized User
 
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.. voskoue@hotmail.com

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:)






Similar Threads
Thread Thread Starter Forum Replies Last Post
Identical results in Drop Down List 5ilverado ASP.NET 1.0 and 1.1 Professional 0 July 3rd, 2006 12:26 AM
Finding Values and counting scattered through data Scootterp Access VBA 13 March 20th, 2006 03:54 PM
Finding values from duplicated nodes swwallace XSLT 1 March 2nd, 2006 05:21 AM
query for identical field and echo... flyguylol Beginning PHP 2 January 15th, 2006 05:48 PM
multiple definitions with identical signatures Galina Crystal Reports 0 February 14th, 2004 05:39 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.