Do Autolookup Queries work with more than 2 linked tables?

[Guest]

My autolookup query works fine until I add a third linked table. Is there a
way around this? I need to add additional tables to this query to perform
lookup functions.

Thanks

 

[John Vinson]

My autolookup query works fine until I add a third linked table. Is there a
way around this? I need to add additional tables to this query to perform
lookup functions.

Thanks

What "doesn't work"? Could you perhaps post the SQL of the query?

John W. Vinson[MVP]

 

[Guest]

Actually, it works fine with three linked tables, but when I add a fourth the
records in the main table will not display so that I am able to edit them.
Is there a limit on the number of linked tables that may be added to an
autolookup query? I would prefer not using a subform. The SQL is as follows:

SELECT [FY2006-07].Priority1, Priority1.Rate AS Rate1, [FY2006-07].WPSInit1,
[FY2006-07].OutputType1, OutputType1.Adjust AS Adjust1, [FY2006-07].Code1,
Code1.Weight AS Weight1, [FY2006-07].Units1, [FY2006-07].Output1,
[FY2006-07].Priority2, [FY2006-07].WPSInit2, [FY2006-07].OutputType2,
[FY2006-07].Code2, [FY2006-07].Units2, [FY2006-07].Output2,
[FY2006-07].Priority3, [FY2006-07].WPSInit3, [FY2006-07].OutputType3,
[FY2006-07].Code3, [FY2006-07].Units3, [FY2006-07].Output3,
[FY2006-07].WPSInit4, [FY2006-07].OutputType4, [FY2006-07].Code4,
[FY2006-07].Units4, [FY2006-07].Output4, [FY2006-07].WPSInit5,
[FY2006-07].OutputType5, [FY2006-07].Code5, [FY2006-07].Units5,
[FY2006-07].Output5, [FY2006-07].WPSInit6, [FY2006-07].OutputType6,
[FY2006-07].Code6, [FY2006-07].Units6, [FY2006-07].Output6,
[FY2006-07].WPSInit7, [FY2006-07].OutputType7, [FY2006-07].Code7,
[FY2006-07].Units7, [FY2006-07].Output7, [FY2006-07].WPSInit8,
[FY2006-07].OutputType8, [FY2006-07].Code8, [FY2006-07].Units8,
[FY2006-07].Output8, [FY2006-07].Multiplier, [FY2006-07].Discount,
[FY2006-07].Specialist1, [FY2006-07].Start1, [FY2006-07].End1,
[FY2006-07].Specialist2, [FY2006-07].Start2, [FY2006-07].End2,
[FY2006-07].Specialist3, [FY2006-07].Start3, [FY2006-07].End3,
[FY2006-07].Specialist4, [FY2006-07].Start4, [FY2006-07].End4,
[FY2006-07].[Proofer 1], [FY2006-07].[Proofer Start 1], [FY2006-07].[Proofer
End 1], [FY2006-07].[Proofer 2], [FY2006-07].[Proofer Start 2],
[FY2006-07].[Proofer End 2], [FY2006-07].[Quality Check],
[FY2006-07].[Quality Check Start], [FY2006-07].[Quality Check End]
FROM Code1 INNER JOIN (OutputType1 INNER JOIN (Priority1 INNER JOIN
[FY2006-07] ON Priority1.Priority = [FY2006-07].Priority1) ON
OutputType1.OutputType = [FY2006-07].OutputType1) ON Code1.Code =
[FY2006-07].Code1;

Thanks for your help.

 

[John Vinson]

FROM Code1 INNER JOIN (OutputType1 INNER JOIN (Priority1 INNER JOIN
[FY2006-07] ON Priority1.Priority = [FY2006-07].Priority1) ON
OutputType1.OutputType = [FY2006-07].OutputType1) ON Code1.Code =
[FY2006-07].Code1;

This clause with the INNER JOINs means that only records with matching
values *already in* tables OutputType1 and Priority1 will be
displayed. That's what an inner (default type) join means - display
nothing at all from any table unless both (or all) tables have
matching data.

Given your query - *all* of your tables are severely denormalized. Any
table with fieldnames like Priority1, Priority2, Priority3 or
Specialist1/2/3 etc. is SIMPLY WRONG. If you have a one to many
relationship, model it *as a one to many relationship* with multiple
records, not multiple fields! I'd strongly suggest stepping back and
normalizing your table structure first - you'll have no end of trouble
with this spreadsheet masquerading as a relational table!

John W. Vinson[MVP]

 

[Guest]

Have structured the database several ways using the Access help menu and even
looked at some of the templates online, but do not know enough about Access
to get to where I need to go with this. Is there a way to structure this and
store all the data in one database, without using a subform? The online
template similar to what I am trying to accomplish uses a second table "Order
Detail" with a subform. I would prefer to keep all the data in one table if
possible. Appreciate your help.

FROM Code1 INNER JOIN (OutputType1 INNER JOIN (Priority1 INNER JOIN
[FY2006-07] ON Priority1.Priority = [FY2006-07].Priority1) ON
OutputType1.OutputType = [FY2006-07].OutputType1) ON Code1.Code =
[FY2006-07].Code1;

This clause with the INNER JOINs means that only records with matching
values *already in* tables OutputType1 and Priority1 will be
displayed. That's what an inner (default type) join means - display
nothing at all from any table unless both (or all) tables have
matching data.

Given your query - *all* of your tables are severely denormalized. Any
table with fieldnames like Priority1, Priority2, Priority3 or
Specialist1/2/3 etc. is SIMPLY WRONG. If you have a one to many
relationship, model it *as a one to many relationship* with multiple
records, not multiple fields! I'd strongly suggest stepping back and
normalizing your table structure first - you'll have no end of trouble
with this spreadsheet masquerading as a relational table!

John W. Vinson[MVP]

 

[John Vinson]

Is there a way to structure this and
store all the data in one database, without using a subform? The online
template similar to what I am trying to accomplish uses a second table "Order
Detail" with a subform. I would prefer to keep all the data in one table if
possible.

That's a bit like asking "How can I use Excel without using any
expressions in cells?"

You're using a relational database. Use it *as a relational database*!

Putting all your data in one table is simply misusing Access, and will
make your development much more difficult, rather than simpler.

Not using Subforms is simply bypassing one of the most powerful - and
simplest to use and implement - tools within Access.

If you insist on using the program but ignoring its defining features,
then yes - you'll have difficulties, and I for one really won't be
able to help you much, because I do not understand what you are trying
to accomplish.

John W. Vinson[MVP]

 

[Guest]

Thanks much. Will scrap this one and start over using subform. Thanks for
the advice. Will post again as I get further into the development.

 

[John Vinson]

Thanks much. Will scrap this one and start over using subform. Thanks for
the advice. Will post again as I get further into the development.

Do check out some of the links about database normalization, e.g. in
the Resources link at http://www.mvps.org/access or at Jeff Conrad's
site

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

You'll find that properly normalized tables are a bit more work to set
up initially - but will be MUCH easier to deal with as you proceed!

John W. Vinson[MVP]