displaying a one-to-many relationship in one table

[robert.waters]

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.

 

[Guest]

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

 

[robert.waters]

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
--
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?
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.

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!

 

[Guest]

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");

--
KARL DEWEY
Build a little - Test a little

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
--
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?
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.

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!