displaying a one-to-many relationship in one table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robert.waters - 02 Aug 2007 20:51 GMT

How would I go about displaying a one-to-many relationship between two
tables in a query, using SQL?
I have a table of clients, and a table of injuries; one client can
have several injuries; I want a query that will result in one row for
each client, a few columns for client data, and a column for each
injury they have (I guess with a number of injury columns equal to the
number of injuries the client with the most injuries has)

If someone could just point me in the right direction, I would really
appreciate it.

Reply to this Message

KARL DEWEY - 02 Aug 2007 21:38 GMT

Try using a crosstab query. You will have to get fancy to combine data into
a single column such as date & injury type.

Here is an example of combining data for a crosstab column.

Customer Order ---
Posting Date    Material    Invoices    Units    CustomerID
01/01/2005    12234    2    2    4
01/02/2005    12334    2    4    4
01/01/2005    11134    4    12    5
01/12/2005    12334    3    14    4
01/14/2005    11134    1    12    5
01/12/2005    12334    5    14    4
Concatenate Totals ---
SELECT Format([Posting Date],"w") AS [Week of Entry], [Customer
Order].Material, Count([Invoices]) & " " & Sum([Units]) AS ABC
FROM [Customer Order]
GROUP BY Format([Posting Date],"w"), [Customer Order].Material;

Concatenate Totals_Crosstab ---
TRANSFORM First([Concatenate Totals].ABC) AS FirstOfABC
SELECT [Concatenate Totals].Material
FROM [Concatenate Totals]
GROUP BY [Concatenate Totals].Material
PIVOT [Concatenate Totals].[Week of Entry];

Material    1    4    6    7
11134            1 12    1 12
12234                1 2
12334    1 4    2 28

Signature

KARL DEWEY
Build a little - Test a little

> How would I go about displaying a one-to-many relationship between two
> tables in a query, using SQL?
[quoted text clipped - 6 lines]
> If someone could just point me in the right direction, I would really
> appreciate it.

Reply to this Message

robert.waters - 03 Aug 2007 07:40 GMT

On Aug 2, 4:38 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> Try using a crosstab query. You will have to get fancy to combine data into
> a single column such as date & injury type.
[quoted text clipped - 40 lines]
> > If someone could just point me in the right direction, I would really
> > appreciate it.

I've looked at examples of crosstab queries, but they all seem to deal
with domain aggregate functions and the like. I can't seem to apply
that logic you gave to my particular problem, for some reason. Can't
wrap my head around it. My problem seems so very simple, that the
most basic explanation of a crosstab query would use something similar
as an example. Alas, I cannot find that explanation!

Reply to this Message

KARL DEWEY - 27 Aug 2007 21:24 GMT

Sorry for late back. Try working on this ---
TRANSFORM Count(injuries.Type) AS CountOfType
SELECT Clients.ClientLastName, injuries.Type, Count(injuries.Type) AS [Total
Of Type]
FROM Clients LEFT JOIN injuries ON Clients.[Client ID] = injuries.[Client ID]
GROUP BY Clients.ClientLastName, injuries.Type
PIVOT Format([InjuryDate],"yyyy mm mmmm");

Signature

KARL DEWEY
Build a little - Test a little

> On Aug 2, 4:38 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 49 lines]
> most basic explanation of a crosstab query would use something similar
> as an example. Alas, I cannot find that explanation!

Reply to this Message