Data Enviroment SQL

[whittam]

I am trying to use the Data Enviroment to design an SQL which I can then use to make a report
The problem I am having is that I need to design two seperate SQL's and then combine them to get the appropriate info.
I was trying to use a hierachy but I'm not sure how to use it or if I can base a report on the parent object

At this stage the two child SQL's which give the data to combine are as follows

ClientQ: Select Distinct Client.CFName, Client.CLName, Agentrep.AFName, AgentRep.ALName, AgentRep.AgentID from agentrep, client where agentrep.agentid = client.agentid

HouseQ: Select House.HStreet, House.HSuburb, House.HPostCode, OInspect.ODate, OInspect.AgentID from House, OINspect where OInspect.ODate between #Date1# and #Date2#

Has anyone got any suggestions?????

[davidrobin]

I don't know how to use the data environment to its full extent but I do know you can embed select queries inside each other so you can do this:

SELECT (SELECT ... FROM ... WHERE ... ) ... FROM ... WHERE ...

Not sure whether this would help you.

[P.S.W.]

I've had some success using the hierarchy feature of the Data Environment, and with it you can easily combine fields from 2 different SQL queries into 1 data report (provided that they have a parent-child relationship).

Add the two SQL queries as separate command objects; then select the child query, right click, & display the Property Pages. Check out the "Relation" tab - it's fairly easy to understand. Once you've related the 2 commands, when you create the data report you will need to create one with a "group" header & footer. You can place the fields from the parent command into this "group section", and the child command's fields will go into the "detail" section.

Hope this helps!

[whittam]

That helps alot, but the other problem was I got as far as creating the relation between the parent/child tables but there were no fields in the drop down list for the parent side of the relationship and I was unsure of what fields to put in there.

Can you give me an idea???

[P.S.W.]

Well, assuming you checked the little box for "Relate to a Parent Command Object", and then selected the appropriate Command from the Parent Command drop-down list, you should be getting fields in that "Parent Fields" list. I've never had this _not_ happen, so I'm not sure what the problem could be there. Check to see if your commands have a "+" node next to them that expands to show all the fields in the command. If not, something may be awry with the setup of the command.

As for what fields to put in the relationship, all you need to do is select the primary key from the Parent command and its corresponding foreign key from the Child command, then hit the "Add" button to make it official (For example, select the field "ClientID" from both commands). This sets-up the parent-child relationship.

Hope that helps a little more!

[whittam]

Thanks it did help, but now I'm wondering why the data report won't let me base the report on two seperate commands. Is this normal and is there anyway around it????