Pedigree help

[HÃ¥kon]

I've got some problems creating a pedigree query. The thing is that I want a report on one animal containing some info about that specifig animal including 3 generations. To create this report I'm trying to create a query containing all this information, but only end up with an error saying the query is to complex. In the query I need to cross reference the Animaltbl to Breedertbl, Addresstbl and a colourtbl.
I've gotten as far as to connect the colourtbl to all 4 generations (15 animals total), but as soon as I try to connect the breedertbl I get the error when running the query.
I've tried creating a query just looking up the animals and the colours, and then creating a new query to add the breeder, address and colour info, but this didn't work either (too complex). Could anyone please help me? I'm rather new to access, so please be very specific.

Thanks for your time.

 

[Allen Browne]

You're doing pretty well to get the 4 generations.
Presumably something like this example:
http://allenbrowne.com/ser-06.html

You did try using outer joins to the additional tables?

If it is not possible to add more tables, you may be able to use DLookup()
on the report to get the colour info. Or you might decide to use the colour
name as the primary key for colourtbl instead of an AutoNumber, so that you
do not need to do another lookup against each of the 15 tables.

Another option is to use the Format event of the Detail section of the
report to OpenRecordset with a query statement into the Breedertbl and
Addresstbl, and drop the info into unbound text boxes on the report.

Hope that helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've got some problems creating a pedigree query. The thing is that I want

a report on one animal containing some info about that specifig animal
including 3 generations. To create this report I'm trying to create a query
containing all this information, but only end up with an error saying the
query is to complex. In the query I need to cross reference the Animaltbl to
Breedertbl, Addresstbl and a colourtbl.

I've gotten as far as to connect the colourtbl to all 4 generations (15

animals total), but as soon as I try to connect the breedertbl I get the
error when running the query.

I've tried creating a query just looking up the animals and the colours,

and then creating a new query to add the breeder, address and colour info,
but this didn't work either (too complex). Could anyone please help me? I'm
rather new to access, so please be very specific.

 

[HÃ¥kon]

No I haven't tried Outer Join. I've read some about it, but can't say I'm abselutely sure how it works. I've tried to edit the relationships in the query design, but unable to select outer join. Do I have to add it manually in the SQL command line? Could you please explain how Outer Join works and how to use it?

Btw. your site was most helpfull. Did a search in google before I started the pedigree and found your site.

Thanks

 

[Allen Browne]

To change the join to an outer join, double-click the line joining the 2
tables in query design, and choose option 2 or 3 (depending whether you want
a left or right join).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

No I haven't tried Outer Join. I've read some about it, but can't say I'm

abselutely sure how it works. I've tried to edit the relationships in the
query design, but unable to select outer join. Do I have to add it manually
in the SQL command line? Could you please explain how Outer Join works and
how to use it?

Btw. your site was most helpfull. Did a search in google before I started

the pedigree and found your site.

 

[HÃ¥kon]

Ok, in that case I've tried it. It says Left or Right join in the SQL? Anyway, it's still to complex
I'm able to add the colour tables to all the 15 animals in the query, but not breeder and adress in addition (on 7 of the animals). Perhaps 2 seperate queries could work

Could you please explain the use of DLookup()? I didn't quiet get it......:

Thanks