We help IT Professionals succeed at work.

DataReport - DataEnvironment - SQL statement

Medium Priority
Last Modified: 2008-01-30
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.  
Watch Question

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

John Doe
3232 3rd Street
Select * from

1 <OwnershipIEN>
1 <Owner> Jim
1 <Horse> SuperFast

2<Owner> John
1<Horse> SuperFast
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.



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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.