I have a query that I need help on please

[John M. Vazquez]

Ok I would appreciates any help I can get.

What I need is this query to be dynamic. I need it to keep adding this (
[WFM Reps_x].[Code Number], [WFM Reps_x].[First
Name], [WFM Reps_x].[Last Name], [WFM Reps_x].level ) until it has no
more reps to add

Below is the query:



SELECT [WFM Reps].[Record #], [WFM Reps].[Code Number], [WFM Reps].[First
Name], [WFM Reps].[Last Name], [WFM Reps].Level, [WFM Reps_1].[Code Number],
[WFM Reps_1].[First Name], [WFM Reps_1].[Last Name], [WFM Reps_1].Level,
[WFM Reps_2].[Code Number], [WFM Reps_2].[First Name], [WFM Reps_2].[Last
Name], [WFM Reps_2].Level, [WFM Reps_3].[Code Number], [WFM Reps_3].[First
Name], [WFM Reps_3].[Last Name], [WFM Reps_3].Level, [WFM Reps_4].[Code
Number], [WFM Reps_4].[First Name], [WFM Reps_4].[Last Name], [WFM
Reps_4].Level, [WFM Reps_5].[Code Number], [WFM Reps_5].[First Name], [WFM
Reps_5].[Last Name], [WFM Reps_5].Level, [WFM Reps_6].[Code Number], [WFM
Reps_6].[First Name], [WFM Reps_6].[Last Name], [WFM Reps_6].Level
FROM (((([WFM Reps] INNER JOIN [WFM Reps] AS [WFM Reps_1] ON [WFM
Reps].[Code Number] = [WFM Reps_1].[Sponsor Code]) INNER JOIN [WFM Reps] AS
[WFM Reps_2] ON [WFM Reps_1].[Code Number] = [WFM Reps_2].[Sponsor Code])
INNER JOIN [WFM Reps] AS [WFM Reps_3] ON [WFM Reps_2].[Code Number] = [WFM
Reps_3].[Sponsor Code]) INNER JOIN [WFM Reps] AS [WFM Reps_4] ON [WFM
Reps_3].[Code Number] = [WFM Reps_4].[Sponsor Code]) INNER JOIN ([WFM Reps]
AS [WFM Reps_5] INNER JOIN [WFM Reps] AS [WFM Reps_6] ON [WFM Reps_5].[Code
Number] = [WFM Reps_6].[Sponsor Code]) ON [WFM Reps_4].[Code Number] = [WFM
Reps_5].[Sponsor Code]
ORDER BY [WFM Reps].[Record #];

Thanks,

John M. Vazquez

 

[John M. Vazquez]

Ok I have looked at Allen Browne s web site and found an article on the sql
trees. This article helps with this problem but I have now read the article
4 times and I am still trying to understand it. I understand some but well
not quite there. Maybe 10 more reads will help but if ne1 can help me
understand this more please do. Here is the article

http://www.dbmsmag.com/9603d06.html

Thanks,

john

 

[John Vinson]

What I need is this query to be dynamic. I need it to keep adding this (
[WFM Reps_x].[Code Number], [WFM Reps_x].[First
Name], [WFM Reps_x].[Last Name], [WFM Reps_x].level ) until it has no
more reps to add

Your problem is that your table design is incorrectly normalized. You
have a one (WFM) to many (Reps) relationship; the proper structure
would have one record PER REP, rather than proliferating fields.

Your query will be very, very difficult because your table structure
is a spreadsheet, not a properly normalized table.

John W. Vinson[MVP]