Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-24-2008, 02:47 AM   #1
anilvaghela
Registered User
 
Join Date: Sep 2008
Location: London
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
anilvaghela is on a distinguished road
Cool VBA Required to append different rows

hello...

I have a database which consists of 5 tables....

each table has the same fields but they are all linked.

Table 5 - Is joined by using the parent_id field which is linked to the unique_id on Table 4

And so on.... till Table 1.

Table 1 has data at level 1 on a hierarchy. So you will have for example 5 records in here. Which have its unique_id.

Table 2 will have for example 30 records which are grouped into the 5 records in Table 1. This is linked by the parent_id on Table 2 which is joined to unique_id on Table 1.

And so on for Table 3 which is joined to Table 2 and Table 4 joined to Table 3 and Table 5 joined to Table 4.

This is done and works fine.

But I want to be able to APPEND 1 of the 5 records first from Table 1 into a Main Table. Then go to Table 2 and APPEND only the FIRST Record which matches the unique_id from the record we appended from Table 1.

Then go to Table 3, 4 and then 5.

Then loop and go back to Table 2 not Table 1 as there maybe more records under Table 2 which maybe linked to the first record we appended from Table 1 and continue the same process... BUT IF not then Return to Table 1.... and so on!!!

.................................................. .......


It would be a great help if someone can help me as I am truly stuck!

Thanks!

:-)

anilvaghela is offline   Reply With Quote
Old 09-24-2008, 03:00 AM   #2
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,466
Thanks: 38
Thanked 77 Times in 73 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: VBA Required to append different rows

Your database design could be a lot easier. You only need two tables.

Suppose you create a car. A car has an engine, an engine has a dynamo a dynamo has bolts and screws so does the engine, so does the car.

You need one table to store the Parts. such as bolts, screws, engine, wheels and so on.
you need one table to build your car: Product

In the Parts table you have the following fields: ID, description, ...
In the Products table you have the following fields: ParentID, ChildID

Now you can create your queries.

HTH
Guus2005 is offline   Reply With Quote
Old 09-24-2008, 04:16 AM   #3
anilvaghela
Registered User
 
Join Date: Sep 2008
Location: London
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
anilvaghela is on a distinguished road
Re: VBA Required to append different rows

Thanks for that....

But how in queries to i determine what i want to do..... which is basically take the first part and find other parts from level 2,3,4 and 5 and then append them ....then loop through it again and append another group......

anilvaghela is offline   Reply With Quote
Old 09-24-2008, 05:13 AM   #4
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,466
Thanks: 38
Thanked 77 Times in 73 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: VBA Required to append different rows

You will have to be creative on this one. You have to create a recursive function

pseudocode:
Code:
sub RecursiveTreeview(id)
  open a recordset based on the id
  does this parent have any children
     RecursiveTreeview(childID)
end sub
HTH
Guus2005 is offline   Reply With Quote
Old 09-24-2008, 06:00 AM   #5
anilvaghela
Registered User
 
Join Date: Sep 2008
Location: London
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
anilvaghela is on a distinguished road
Re: VBA Required to append different rows

Thanks.... i guess i can get my skates on from this point.....

Thanks again!!

anilvaghela is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query Key Violation sudo Queries 4 07-10-2011 03:09 AM
Access VBA Function - is number? required mudster01 Modules & VBA 1 08-29-2006 01:13 AM
Error you are about to append 0 rows patentinv Queries 1 09-25-2005 06:34 AM
Append (multiple rows) Query Ste4en Queries 2 03-20-2005 11:22 AM
2 Append query problems Sam Summers Queries 2 02-27-2003 02:27 PM




All times are GMT -8. The time now is 12:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World