I have a problem with a query

J

John M. Vazquez

Hi all.

Here is my problem I need a genealogy search and output from my database. I
use a unique code for each entry and some are related to one or another.
What I want to do is get the grandfather father and son and if It goes
deeper than grandfather to do it. So hopefully I explained it correctly.

Thanks,

Exor
 
J

John M. Vazquez

I still have problems. I used the allenbrown example but I lose part of my
query when I run it. Can ne1 help me please. And everytime I add another
gen I lose more till I reach the 8th gen.

Please Help Me!!

Thanks

Yanen
 
S

SteveS

What do you mean "I lose part of my query when I run it"?

What happens when you reach the 8th gen?

What is your table structure? (field names?)
What does the query look like? (Switch to SQL view, copy and paste into a
reply.)

I don't know if I can help you, but you need to provide more information to
get more/better help.
 
J

John M. Vazquez

Well first I need to thank you for trying to help.

Ok I have over 250 items in my database. I make 8 generation down. And I do
the query and instead OF GETTING 250 I get maybe 8 items of course I get the
8 generations but only 8 items deep. Now if I erase two generation I get
like 30 items.

Part code which is pointed to related part code eight times.

Now the first field I sort is the record nom field then I have the part code
then part name then the second gen part code and name and so forth.

I don't know how to do the next part you ask.

Thanks,

Exor
 
S

SteveS

Ok, we need to go back a little. In your first post, you said "genealogy
search", so I thought you have a genealogy database.

Open your table in design view. How many fields are there?
And what are the names of the fields?
 
J

John M. Vazquez

Ok yes its a genealogy. Of customers I have in a network marketing company.

First like I said I have a customer code that points to a sponsor code. Then
I point the customer code again to the sponsor code. Ect for 8 generations.

I am not at work but I think its a total of 32 fields

The fields are record number then customer code, first name, last name,
level, gen1.customer code, gen1 first name, gen1 last name, gen1 level, ect
for total 8 generations

I have 250 customers each has a customer code and each customer should have
a sponsor code. This the sponsor code should relate back to a customer code
of the person that sponsored that particular customer.

The other problem I have I don't know how to do the query till it finds no
more customer that were sponsored.

Tomorrow I try and figure a way to send you what asked for.

Thanks,

John M. Vazquez
 
J

John M. Vazquez

Here is the sql you asked for:

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 #];

Did I do something wrong? I have tried everything to get it to print more
that 8 items. I have 250 items.

Thanks,

Yanen
 
S

SteveS

You need to select Left (Outer) Join instead of InnerJoin - option 2 when you
double click on the join line in the query grid. The line between the 2 table
should have an arrow pointing to the right table.

I changed "WFM Reps_2" to "L_2", "WFM Reps_3" to "L_3", etc ..... it is
easier to work with.

Using use special characters (like #) or spaces in object names is not a
good practice. Instead of "First Name", use "FirstName". It is just as easy
to read and you don't need "[]" around it.


This should give you what you are looking for. It is 10 levels (generations)
deep. Open the query (or a new query) in design view. Switch to SQL view.
Paste in the following SQL:

SELECT DISTINCT [WFM Reps].[Record #], [WFM Reps].[Code Number], [WFM
Reps].[First Name], [WFM Reps].[Last Name], [WFM Reps].Level, L_2.[Code
Number], L_2.[First Name], L_2.[Last Name], L_2.Level, L_3.[Code Number],
L_3.[First Name], L_3.[Last Name], L_3.Level, L_4.[Code Number], L_4.[First
Name], L_4.[Last Name], L_4.Level, L_5.[Code Number], L_5.[First Name],
L_5.[Last Name], L_5.Level, L_6.[Code Number], L_6.[First Name], L_6.[Last
Name], L_6.Level, L_7.[Code Number], L_7.[First Name], L_7.[Last Name],
L_7.Level, L_8.[Code Number], L_8.[First Name], L_8.[Last Name], L_8.Level,
L_9.[Code Number], L_9.[First Name], L_9.[Last Name], L_9.Level, L_10.[Code
Number], L_10.[First Name], L_10.[Last Name], L_10.Level
FROM [WFM Reps] LEFT JOIN (((((((([WFM Reps] AS L_2 LEFT JOIN [WFM Reps] AS
L_3 ON L_2.[Code Number] = L_3.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_4 ON
L_3.[Code Number] = L_4.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_5 ON
L_4.[Code Number] = L_5.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_6 ON
L_5.[Code Number] = L_6.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_7 ON
L_6.[Code Number] = L_7.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_8 ON
L_7.[Code Number] = L_8.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_9 ON
L_8.[Code Number] = L_9.[Sponsor Code]) LEFT JOIN [WFM Reps] AS L_10 ON
L_9.[Code Number] = L_10.[Sponsor Code]) ON [WFM Reps].[Code Number] =
L_2.[Sponsor Code]
ORDER BY [WFM Reps].[Code Number], L_2.[Code Number], L_3.[Code Number],
L_4.[Code Number], L_5.[Code Number], L_6.[Code Number], L_7.[Code Number],
L_8.[Code Number], L_9.[Code Number], L_10.[Code Number];



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


John M. Vazquez said:
Here is the sql you asked for:

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 #];

Did I do something wrong? I have tried everything to get it to print more
that 8 items. I have 250 items.

Thanks,

Yanen


Ok yes its a genealogy. Of customers I have in a network marketing company.

First like I said I have a customer code that points to a sponsor code. Then
I point the customer code again to the sponsor code. Ect for 8 generations.

I am not at work but I think its a total of 32 fields

The fields are record number then customer code, first name, last name,
level, gen1.customer code, gen1 first name, gen1 last name, gen1 level, ect
for total 8 generations

I have 250 customers each has a customer code and each customer should have
a sponsor code. This the sponsor code should relate back to a customer code
of the person that sponsored that particular customer.

The other problem I have I don't know how to do the query till it finds no
more customer that were sponsored.

Tomorrow I try and figure a way to send you what asked for.

Thanks,

John M. Vazquez
Click to expand...
Click to expand...
 
J

John M. Vazquez

Ok I found out that I did the joins wrong.

Thanks for the help

john


Here is the sql you asked for:

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 #];

Did I do something wrong? I have tried everything to get it to print more
that 8 items. I have 250 items.

Thanks,

Yanen


Ok yes its a genealogy. Of customers I have in a network marketing company.

First like I said I have a customer code that points to a sponsor code. Then
I point the customer code again to the sponsor code. Ect for 8 generations.

I am not at work but I think its a total of 32 fields

The fields are record number then customer code, first name, last name,
level, gen1.customer code, gen1 first name, gen1 last name, gen1 level, ect
for total 8 generations

I have 250 customers each has a customer code and each customer should have
a sponsor code. This the sponsor code should relate back to a customer code
of the person that sponsored that particular customer.

The other problem I have I don't know how to do the query till it finds no
more customer that were sponsored.

Tomorrow I try and figure a way to send you what asked for.

Thanks,

John M. Vazquez
Click to expand...
Click to expand...
 
Top