Show all Criteria.

[Karl]

Hello,
I am trying to create a report from a query.
I want to create a report that will list all my products by category, and
then all the corresponding components and subcomponents of the each product
(plus some additional info about the components and subcomponents (i.e.
quantity, make, model #). Each one of these is a seperate table all linear
linked as follows:
Categories >>Products>>Components>>Sub Components

As my query stands it only shows the category, product and compents that
also have subcompenents (some of my components don't have sub-components).
However I have some components that do not contain any sub-components so
these components are not shown in the current query. I want my query to show
both components that do not have sub-compoents and all those components that
do have sub-compents. But I also want the sub-compents that exist to show up
as well.
I am hoping might be a sql statement I could add to the criteria of the
component field that would cause it to show all the components even the ones
without sub-components (without removing the sub-components).

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

Thanks in Advance,
Karl

 

[Joshua A. Booker]

Karl,

In Query design right-click and edit the join to subcomp table and specify
'show all from components and only those from sub-comp...etc.'

HTH,
Josh

 

[Karl]

Hello Joshua,

Thanks for the help it worked like a charm. I have a similar questions in a
different situtation. Would it be possible to link my components table to a
maintenance table using compoent id and also link my sub components table to
the maintenance table using sub-component id. This creates a relationship
that is no longer strictly linear (if i make it linear it works fine but then
i have to create a seperate maintenance table for sub componets and
components and I was hoping to be able to use the same maintenance table for
both components and subcompents).

Here is the SQL
SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Component Name], [Sub Component Table].[Sub Component
Name], [Maintenence Info].[Maintenance Required]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) INNER JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Sub component ID] =
[Maintenence Info].[Sub Component ID]) AND ([Components table].[Equipment ID]
= [Maintenence Info].[Equipment ID]);

thank you,
Karl

Karl,

In Query design right-click and edit the join to subcomp table and specify
'show all from components and only those from sub-comp...etc.'

HTH,
Josh

Hello,
I am trying to create a report from a query.
I want to create a report that will list all my products by category, and
then all the corresponding components and subcomponents of the each product
(plus some additional info about the components and subcomponents (i.e.
quantity, make, model #). Each one of these is a seperate table all linear
linked as follows:
Categories >>Products>>Components>>Sub Components

As my query stands it only shows the category, product and compents that
also have subcompenents (some of my components don't have sub-components).
However I have some components that do not contain any sub-components so
these components are not shown in the current query. I want my query to show
both components that do not have sub-compoents and all those components that
do have sub-compents. But I also want the sub-compents that exist to show up
as well.
I am hoping might be a sql statement I could add to the criteria of the
component field that would cause it to show all the components even the ones
without sub-components (without removing the sub-components).

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

Thanks in Advance,
Karl

 

[Joshua A. Booker]

Karl,

Yes it would be best to have a single maintenance table which has both
CompID and SubCompID. CompID would be required but SubCompID optional.

That way you can query for all the maintanance by component which will
include the maintenance of it's related subcomps.

This assumes you don't have any subcomps without matching comps in the comps
table.

HTH,
Josh

Hello Joshua,

Thanks for the help it worked like a charm. I have a similar questions in a
different situtation. Would it be possible to link my components table to a
maintenance table using compoent id and also link my sub components table to
the maintenance table using sub-component id. This creates a relationship
that is no longer strictly linear (if i make it linear it works fine but then
i have to create a seperate maintenance table for sub componets and
components and I was hoping to be able to use the same maintenance table for
both components and subcompents).

Here is the SQL
SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Component Name], [Sub Component Table].[Sub Component
Name], [Maintenence Info].[Maintenance Required]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) INNER JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Sub component ID] =
[Maintenence Info].[Sub Component ID]) AND ([Components table].[Equipment ID]
= [Maintenence Info].[Equipment ID]);

thank you,
Karl

Karl,

In Query design right-click and edit the join to subcomp table and specify
'show all from components and only those from sub-comp...etc.'

HTH,
Josh

Hello,
I am trying to create a report from a query.
I want to create a report that will list all my products by category, and
then all the corresponding components and subcomponents of the each product
(plus some additional info about the components and subcomponents (i.e.
quantity, make, model #). Each one of these is a seperate table all linear
linked as follows:
Categories >>Products>>Components>>Sub Components

As my query stands it only shows the category, product and compents that
also have subcompenents (some of my components don't have sub-components).
However I have some components that do not contain any sub-components so
these components are not shown in the current query. I want my query

to
show

both components that do not have sub-compoents and all those

components
that

do have sub-compents. But I also want the sub-compents that exist to

show
up

as well.
I am hoping might be a sql statement I could add to the criteria of the
component field that would cause it to show all the components even

the
ones

without sub-components (without removing the sub-components).

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

Thanks in Advance,
Karl

 

[Karl]

Josh,
How would I add the maintenance information for subcomponents if its not
connected by SubCompID? I understand it would be connected through the
equipment id but I don't see how I could seperate out the maintenance for
component by sub compenent.

Right now I have a form where I choose category and product from combo
boxes, then I have a subform with compenent information that has three
subforms (sub components, maintenance info, and purchase info). All of these
are connected to the component information through compenent id. So with this
set up I can't add maintenance or purchase information about subcomponents
because it is only connected by component id. Therefore to connect by sub
compenent i tried to link sub compoent to the purchase and maintenance
subforms through a master/child link of subcompent id. But i also created a
relationship between these as well in the tables. So when i query it doesn't
work.

You are correct I want to query for component maintenance and have it
include maintenance of subcomponents as well but I'm not sure i understand
how to do this.

Thanks,
Karl

Karl,

Yes it would be best to have a single maintenance table which has both
CompID and SubCompID. CompID would be required but SubCompID optional.

That way you can query for all the maintanance by component which will
include the maintenance of it's related subcomps.

This assumes you don't have any subcomps without matching comps in the comps
table.

HTH,
Josh

Hello Joshua,

Thanks for the help it worked like a charm. I have a similar questions in a
different situtation. Would it be possible to link my components table to a
maintenance table using compoent id and also link my sub components table to
the maintenance table using sub-component id. This creates a relationship
that is no longer strictly linear (if i make it linear it works fine but then
i have to create a seperate maintenance table for sub componets and
components and I was hoping to be able to use the same maintenance table for
both components and subcompents).

Here is the SQL
SELECT Categories.Category, [Sub Categories Table].SubCategoryName,
[Components table].[Component Name], [Sub Component Table].[Sub Component
Name], [Maintenence Info].[Maintenance Required]
FROM (((Categories INNER JOIN [Sub Categories Table] ON
Categories.CategoryID = [Sub Categories Table].CategoryID) INNER JOIN
[Components table] ON [Sub Categories Table].SubCategoryID = [Components
table].ProductID) INNER JOIN [Sub Component Table] ON [Components
table].[Equipment ID] = [Sub Component Table].[Equipment ID]) INNER JOIN
[Maintenence Info] ON ([Sub Component Table].[Sub component ID] =
[Maintenence Info].[Sub Component ID]) AND ([Components table].[Equipment ID]
= [Maintenence Info].[Equipment ID]);

thank you,
Karl

Karl,

In Query design right-click and edit the join to subcomp table and specify
'show all from components and only those from sub-comp...etc.'

HTH,
Josh

Hello,
I am trying to create a report from a query.
I want to create a report that will list all my products by category, and
then all the corresponding components and subcomponents of the each
product
(plus some additional info about the components and subcomponents (i.e.
quantity, make, model #). Each one of these is a seperate table all linear
linked as follows:
Categories >>Products>>Components>>Sub Components

As my query stands it only shows the category, product and compents that
also have subcompenents (some of my components don't have sub-components).
However I have some components that do not contain any sub-components so
these components are not shown in the current query. I want my query to
show
both components that do not have sub-compoents and all those components
that
do have sub-compents. But I also want the sub-compents that exist to show
up
as well.
I am hoping might be a sql statement I could add to the criteria of the
component field that would cause it to show all the components even the
ones
without sub-components (without removing the sub-components).

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

Thanks in Advance,
Karl