DataReport - DataEnvironment - SQL statement

Hi - I have VB6 includes DataReport.  I use DataEnvironment (via Access) on my Datareport.  Everything displaying fine so far. I have an Invoice file linked to Horse file (child).  Then the Horse file linked to Ownership file (child), then finally an Owner file linked to my Ownership (all child commands).  In my report at the bottom, depending on the Owner's Percent of the horse (percent in ownership file), an invoice amount is printed.  The invoice subtotal is from the invoice and the percent is in the ownership file (as there may be more than one owner per horse).   I need to get the amount a particular owner would have to pay on the report.  (Invoice total * ownerpercent).  I've looked through vb help but cannot understand the SQL child command thingy.  I've found help here many times, but first time posting.  Thank you.  
ColleenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

23637269Commented:
You are looking for the SQL command Inner Join.

I always use an IEN (Internal Entry Number) when I create a table the first filed is the autoinc field.
I normally have a second field for ID number which will be what ever the client wants.  That way in the future if the ID Number changes no big deal it is basically info only and will not effect the structure of the database.
I will setup many tables with the goal of not duplicating info (Owner name address etc is in one table, and if you need the info just reference the IEN).  That way the structure of the databases are dynamic and can change and grow without limitations.  If you want I can explain further I don't want to get too far off point, but I wanted you to understand the structure of the example so that the SQL will be understandable.  :)

Table Owner    Table OwnerShip  Table Horse
OwnerIEN         OwnershipIEN       HorseIEN
OwnerID           OwnerIEN             HorseName
Owner              HorseIEN               DateBorn
Address           Percent                  Notes

1                                                    1
Jim10                                             SuperFast
Jim Aubrey                                    01-01-2005
123 Main St

2
73638546
John Doe
3232 3rd Street
Select * from
Etc..

Ownership
1 <OwnershipIEN>
1 <Owner> Jim
1 <Horse> SuperFast
50%

2<OwnerhipIEN>
2<Owner> John
1<Horse> SuperFast
10%
'''''''''''''''''''''''''''
The Ownership Table is dynamic and can grow allowing you to have as many owners as you want.  You can have one for services or anything else you want...

SELECT * From OwnerShip, Owner, Horse Where Owner.OwnerIEN = OwnerShip.OwnerIEN And Horse.HorseIEN = OwnerShip.HorseIEN

Now you have access to all the info: Owner, Address, Horse, Percent DateBorn, Date Pruchased, DateSold etc... for everyone connected to any horse.

Also lookup Inner Join as another way.

Thanks
Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ColleenAuthor Commented:
ty Roger.  I found the solution after many days of looking through various solutions.  What I needed to do was create a dummy sql that attached to my DataReport.  Then I just filled my invoice form (datareport) with labels instead of textboxes.  Then I generated my amounts in VB and passed them into my VB using the labels.  What I need was to create what the owner pays from 2 of the 4 tables, but still need the tables linked (joined) for all information to be printed on the report.  
My next lesson is to increase my point value when I post a question here, otherwise the experts don't want to waste time on a 250 point question.   My first time posting, so I didn't  know.  
Ty for commenting Roger.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.