Multiple Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not sure how to create this query and it's probably pretty simple but I
can't seem to figure it out. I have 1 table that has all my "MaterialTypes".
Another table that has all my "Sizes" for each material type. How do I create
a query that will list all my materials and the sizes for each material type?
The next step is that I want to create a form that will allow me to choose
the material type and then choose the size within that specific material
type. When I choose the material type I only want the sizes to be shown as
choices for that material type. I'm assuming that both of these controls will
be combo boxes on the form. Can anyone help walk me through this?
 
If MaterialType to Sizes is a One to Many relationship...
Include both tables in your query, and join them with a Left Outer Join,
using the key field (probably MaterialType) from each table.
(Show all records in MaterialType, and only those in Sizes that match)

Type Size
Steel 4
Steel 8
Steel 10
Iron 3
Iron 5 etc....

Re: your second question...
Use 2 combo boxes, cboMaterialType and cboSize.
Populate cboMaterialType from the MaterialType table.
Populate cboSizes with a query just like the one above.
However, this time, we'll place the following criteria for the MaterialType
field...
=Forms!frmYourFormName!cboMaterialType
Use the AfterUpdate event of cboMaterialType to Requery cboSizes, so they
always be in "synch" if cboMaterialType should ever change.

The choices listed in cboSizes will be filtered by the MaterialType you
selected in cboMaterialType.
Ex. If you selected Steel, (using sample data above) only Sizes 4, 8, 10
should be available for selection.
 
Thanks for the help Al. One quick question before I start this. Does the
MaterialType need to be in both tables?
 
Absolutely! That's why you can't "associate" your Sizes data to your
MaterialType data in a query.

But, even better than using MaterialType, would be to create a unique
Material Type Identifier "key field" like [MaterialID] in the MaterialType
table. Make this field an Autonumber field.
Your Sizes table should also have a MaterialID field (LongInteger), and the
2 tables should be related via their MaterialID fields. Use Relationships
to set up Referential Integrity and Cascading and a Left Join between the
newly designed tables.

If you haven't done this, and have already entered data in both tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes would
allow you to enter a MaterialType, and multiple Sizes associated with that
Type.
 
I'm understanding it now. I'm ready to populate some data into my tables to
see how it works. Since there is a MaterialType in both tables do I enter the
data in both tables or just the MaterialType Table?

Al Camp said:
Absolutely! That's why you can't "associate" your Sizes data to your
MaterialType data in a query.

But, even better than using MaterialType, would be to create a unique
Material Type Identifier "key field" like [MaterialID] in the MaterialType
table. Make this field an Autonumber field.
Your Sizes table should also have a MaterialID field (LongInteger), and the
2 tables should be related via their MaterialID fields. Use Relationships
to set up Referential Integrity and Cascading and a Left Join between the
newly designed tables.

If you haven't done this, and have already entered data in both tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes would
allow you to enter a MaterialType, and multiple Sizes associated with that
Type.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Secret Squirrel said:
Thanks for the help Al. One quick question before I start this. Does the
MaterialType need to be in both tables?
Click to expand...
Click to expand...
 
I wrote...
Create a form as described above. Main form based on tblMaterialType,
Continuous subform based on tblSizes. Set the Parent/Child link to
MaterialType.
Enter a Type in the main, and any sizes associated with that Type in the
subform. (One to Many)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Secret Squirrel said:
I'm understanding it now. I'm ready to populate some data into my tables
to
see how it works. Since there is a MaterialType in both tables do I enter
the
data in both tables or just the MaterialType Table?

Al Camp said:
Absolutely! That's why you can't "associate" your Sizes data to your
MaterialType data in a query.

But, even better than using MaterialType, would be to create a unique
Material Type Identifier "key field" like [MaterialID] in the
MaterialType
table. Make this field an Autonumber field.
Your Sizes table should also have a MaterialID field (LongInteger), and
the
2 tables should be related via their MaterialID fields. Use
Relationships
to set up Referential Integrity and Cascading and a Left Join between the
newly designed tables.

If you haven't done this, and have already entered data in both tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes
would
allow you to enter a MaterialType, and multiple Sizes associated with
that
Type.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Secret Squirrel said:
Thanks for the help Al. One quick question before I start this. Does
the
MaterialType need to be in both tables?

:

If MaterialType to Sizes is a One to Many relationship...
Include both tables in your query, and join them with a Left Outer
Join,
using the key field (probably MaterialType) from each table.
(Show all records in MaterialType, and only those in Sizes that match)

Type Size
Steel 4
Steel 8
Steel 10
Iron 3
Iron 5 etc....

Re: your second question...
Use 2 combo boxes, cboMaterialType and cboSize.
Populate cboMaterialType from the MaterialType table.
Populate cboSizes with a query just like the one above.
However, this time, we'll place the following criteria for the
MaterialType
field...
=Forms!frmYourFormName!cboMaterialType
Use the AfterUpdate event of cboMaterialType to Requery cboSizes, so
they
always be in "synch" if cboMaterialType should ever change.

The choices listed in cboSizes will be filtered by the MaterialType
you
selected in cboMaterialType.
Ex. If you selected Steel, (using sample data above) only Sizes 4, 8,
10
should be available for selection.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

message I'm not sure how to create this query and it's probably pretty
simple
but
I
can't seem to figure it out. I have 1 table that has all my
"MaterialTypes".
Another table that has all my "Sizes" for each material type. How do
I
create
a query that will list all my materials and the sizes for each
material
type?
The next step is that I want to create a form that will allow me to
choose
the material type and then choose the size within that specific
material
type. When I choose the material type I only want the sizes to be
shown
as
choices for that material type. I'm assuming that both of these
controls
will
be combo boxes on the form. Can anyone help walk me through this?
Click to expand...
Click to expand...
Click to expand...
 
Ok got it. When I create the subform do I only put the size field on it or
should I also add the materialtype field to it as well?

Also, I'm having a little trouble setting up the left join query.

Al Camp said:
I wrote...
Create a form as described above. Main form based on tblMaterialType,
Continuous subform based on tblSizes. Set the Parent/Child link to
MaterialType.
Enter a Type in the main, and any sizes associated with that Type in the
subform. (One to Many)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Secret Squirrel said:
I'm understanding it now. I'm ready to populate some data into my tables
to
see how it works. Since there is a MaterialType in both tables do I enter
the
data in both tables or just the MaterialType Table?

Al Camp said:
Absolutely! That's why you can't "associate" your Sizes data to your
MaterialType data in a query.

But, even better than using MaterialType, would be to create a unique
Material Type Identifier "key field" like [MaterialID] in the
MaterialType
table. Make this field an Autonumber field.
Your Sizes table should also have a MaterialID field (LongInteger), and
the
2 tables should be related via their MaterialID fields. Use
Relationships
to set up Referential Integrity and Cascading and a Left Join between the
newly designed tables.

If you haven't done this, and have already entered data in both tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes
would
allow you to enter a MaterialType, and multiple Sizes associated with
that
Type.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

message Thanks for the help Al. One quick question before I start this. Does
the
MaterialType need to be in both tables?

:

If MaterialType to Sizes is a One to Many relationship...
Include both tables in your query, and join them with a Left Outer
Join,
using the key field (probably MaterialType) from each table.
(Show all records in MaterialType, and only those in Sizes that match)

Type Size
Steel 4
Steel 8
Steel 10
Iron 3
Iron 5 etc....

Re: your second question...
Use 2 combo boxes, cboMaterialType and cboSize.
Populate cboMaterialType from the MaterialType table.
Populate cboSizes with a query just like the one above.
However, this time, we'll place the following criteria for the
MaterialType
field...
=Forms!frmYourFormName!cboMaterialType
Use the AfterUpdate event of cboMaterialType to Requery cboSizes, so
they
always be in "synch" if cboMaterialType should ever change.

The choices listed in cboSizes will be filtered by the MaterialType
you
selected in cboMaterialType.
Ex. If you selected Steel, (using sample data above) only Sizes 4, 8,
10
should be available for selection.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

message I'm not sure how to create this query and it's probably pretty
simple
but
I
can't seem to figure it out. I have 1 table that has all my
"MaterialTypes".
Another table that has all my "Sizes" for each material type. How do
I
create
a query that will list all my materials and the sizes for each
material
type?
The next step is that I want to create a form that will allow me to
choose
the material type and then choose the size within that specific
material
type. When I choose the material type I only want the sizes to be
shown
as
choices for that material type. I'm assuming that both of these
controls
will
be combo boxes on the form. Can anyone help walk me through this?
Click to expand...
Click to expand...
Click to expand...
 
Yes.
Since your using that field to "relate" the tables each table must
contain that field. Same type, same size in each table.

Again though... you should consider my suggestion about using a new
MaterialTypeID field to link the tables. Your call...
tblMaterialType tblSizes
ID Type ID Size
1 Steel 1 4
2 Iron 1 8
1 10
2 3
2 5
And, a bit of studying up on "tables/designing", "database design" and
"relationships" in Access Help will help you to better understand the
basics.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Secret Squirrel said:
Ok got it. When I create the subform do I only put the size field on it or
should I also add the materialtype field to it as well?

Also, I'm having a little trouble setting up the left join query.

Al Camp said:
I wrote...
If you haven't done this, and have already entered data in both
tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes
would
allow you to enter a MaterialType, and multiple Sizes associated with
that
Type.
Click to expand...

Create a form as described above. Main form based on tblMaterialType,
Continuous subform based on tblSizes. Set the Parent/Child link to
MaterialType.
Enter a Type in the main, and any sizes associated with that Type in
the
subform. (One to Many)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Secret Squirrel said:
I'm understanding it now. I'm ready to populate some data into my
tables
to
see how it works. Since there is a MaterialType in both tables do I
enter
the
data in both tables or just the MaterialType Table?

:

Absolutely! That's why you can't "associate" your Sizes data to your
MaterialType data in a query.

But, even better than using MaterialType, would be to create a unique
Material Type Identifier "key field" like [MaterialID] in the
MaterialType
table. Make this field an Autonumber field.
Your Sizes table should also have a MaterialID field (LongInteger),
and
the
2 tables should be related via their MaterialID fields. Use
Relationships
to set up Referential Integrity and Cascading and a Left Join between
the
newly designed tables.

If you haven't done this, and have already entered data in both
tables, I
would delete all the entries in both tables, follow the table redesign
above, and set up the relationships.
Now... a main form based on MaterialType with a subform based on Sizes
would
allow you to enter a MaterialType, and multiple Sizes associated with
that
Type.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

message Thanks for the help Al. One quick question before I start this. Does
the
MaterialType need to be in both tables?

:

If MaterialType to Sizes is a One to Many relationship...
Include both tables in your query, and join them with a Left Outer
Join,
using the key field (probably MaterialType) from each table.
(Show all records in MaterialType, and only those in Sizes that
match)

Type Size
Steel 4
Steel 8
Steel 10
Iron 3
Iron 5 etc....

Re: your second question...
Use 2 combo boxes, cboMaterialType and cboSize.
Populate cboMaterialType from the MaterialType table.
Populate cboSizes with a query just like the one above.
However, this time, we'll place the following criteria for the
MaterialType
field...
=Forms!frmYourFormName!cboMaterialType
Use the AfterUpdate event of cboMaterialType to Requery cboSizes,
so
they
always be in "synch" if cboMaterialType should ever change.

The choices listed in cboSizes will be filtered by the MaterialType
you
selected in cboMaterialType.
Ex. If you selected Steel, (using sample data above) only Sizes 4,
8,
10
should be available for selection.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

in
message I'm not sure how to create this query and it's probably pretty
simple
but
I
can't seem to figure it out. I have 1 table that has all my
"MaterialTypes".
Another table that has all my "Sizes" for each material type. How
do
I
create
a query that will list all my materials and the sizes for each
material
type?
The next step is that I want to create a form that will allow me
to
choose
the material type and then choose the size within that specific
material
type. When I choose the material type I only want the sizes to be
shown
as
choices for that material type. I'm assuming that both of these
controls
will
be combo boxes on the form. Can anyone help walk me through this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top