Valda got it right.  Even Crystal Reports uses report grouping (as would you
if you hand coded the report).  The group concept in a report sets up a
trigger like function. Each record's grouping fields are compared to the
those from the prior record, if the new record's values differ, then a
"group Summary Band" is writen with the old values followed by a new "Group
Header Band" with the new record's data then the detail within the group is
evaluated...

If this is not working for you, then you have not properly identified the
group fields to the report generator or your tables are not nested in a
hierarchical manner.

Your case of 2 nested tables, but unrelated tables, causes additional
headaches as the nesting is not a simple hierarchy.  If you are really
coding MS Access reports, you should consider using sub-reports.  Sub
reports can be fired off for each main table record.  In this case you pass
the Linking key from the primary table (TechID) to each of sub reports one
after the other.  If a sub report finds data, it reports it out, then
returns to the main report.  This involves 3 queries: 1 for the main table,
and 1 each for the 2 sub-reports.  Just remember that the sub-table queries
require a where clause something like: WHERE Table.TechID 
@parameter_TechID

Crystal Reports uses the same technique when the table nesting is not
hierarchial.


----- Original Message -----
From: "Vlada Bromberg" <Vlada@d...>
To: "professional vb" <pro_vb@p...>
Sent: Thursday, September 19, 2002 8:14 AM
Subject: [pro_vb] RE: SQL Question


> Matt,
> Did you try to use DataEnvironment and DataReport? You can Group your data
> by
> Emp and put the field in the Group Header. All the rest fields put in
> Details section.
> Vlada.
>
> -----Original Message-----
> From: Matt M. Morgan [mailto:mattmorgan@p...]
> Sent: Thursday, September 19, 2002 11:03 AM
> To: professional vb
> Subject: [pro_vb] SQL Question
>
>
> All,
>
> I realize that the appropriate forum for this is around the block, but
> I've so many friends here....!
>
> Anyway, here's the deal:
>
> I have 3 tables: Employee, EmployeeSkills and EmployeeContacts.
>
> There is a 1 to many relationship between employee and employeeskills.
> There is a 1 to many relationship between employee and employeecontacts.
>
> All three tables share a common key, "TechID".
>
> What I want is a query that will give me the data in the employee table,
> along with all the associated records in the other two tables for each
> tech ID.
>
> I keep ending up with multiple (the same) employee records in my query.
> For example, I get:
>
> Emp      Skill   Contact
> -------------------------
> Joe blow Plumber Francine
> Joe blow Plumber Tim
> Joe blow Welder  Fred
>
> What I want is:
>
> Emp      Skill   Contact
> -------------------------
> Joe blow Plumber Francine
>          Plumber Tim
>          Welder  Fred
>
>
> I'm using the VB report tool to try do this. I could use Crystal to
> solve the problem, but I've tried Crystal, and although it is much nicer
> from a functionality perspective than the integrated reporter, there are
> other things about it that I don't like.
>
> Ayy ideas on how to write the query? More detail needed from me? Please
> let me know....
>
> Thanks
> Matt (VI1)
>
>
>
>
>
> ---
> Visual C# - A Guide for VB6 Developers
> This book will make it easy to transfer your skills
> from Visual Basic 6 to C#, the language of choice
> of the .NET Framework.
> http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059
>
>
>
> ---
> Visual C# - A Guide for VB6 Developers
> This book will make it easy to transfer your skills
> from Visual Basic 6 to C#, the language of choice
> of the .NET Framework.
> http://www.wrox.com/ACON11.asp?ISBN=1861007175&p2p0059
>