Stuck on best method to query this

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

Guest

The layout of my table: Company Details

Company Name, Phone Number, Fax Number, Company Class, Product Class, Class 2, Class 3, Class 4

The layout of my table: ProductFilterList

Product Class: (This column is linked to Product Class, Class 2, Class 3, Class 4 in the Company Details Table and displays using a COMBO BOX dropdown list)

The layout of my table: Company Class

Company Class: (This Column is linked to Company Class in the Company Details Table and displays using s COMBO BOX dropdown list)

I would like to setup a form so you can have the (Product Class, Class 2, Class 3, Class 4 fields as query fields on the main form when you choose a option from the dropdown list the companys that have that option in that field are displayed in a subform: Company Name, Phone Number, Fax Number but the form must requery if any of the 4 Product Class fields are changed.

E.g. Product Class = motors and nothing in any of the other 3 choices so in the subform customers 1,7,9,76 are displayed because they have motors in (Product Class) in the Company Details Table.

E.g. Product Class = motors Class 2 = Pulleys then cutomers 1,7,9,76,432,657 are displayed because they match the to fields that have been choosen
 
The classes should be broken out to a 2nd table.
i.e. tblCompany_Classes
CompanyClassID
CompanyID
Class

This way, you do not need to query 5 different fields for the same info.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

jim said:
The layout of my table: Company Details

Company Name, Phone Number, Fax Number, Company Class, Product Class, Class 2, Class 3, Class 4

The layout of my table: ProductFilterList

Product Class: (This column is linked to Product Class, Class 2, Class 3,
Click to expand...
Class 4 in the Company Details Table and displays using a COMBO BOX
dropdown list)
The layout of my table: Company Class

Company Class: (This Column is linked to Company Class in the Company
Click to expand...
Details Table and displays using s COMBO BOX dropdown list)
I would like to setup a form so you can have the (Product Class, Class 2,
Click to expand...
Class 3, Class 4 fields as query fields on the main form when you choose a
option from the dropdown list the companys that have that option in that
field are displayed in a subform: Company Name, Phone Number, Fax Number but
the form must requery if any of the 4 Product Class fields are changed.
E.g. Product Class = motors and nothing in any of the other 3 choices so
Click to expand...
in the subform customers 1,7,9,76 are displayed because they have motors in
(Product Class) in the Company Details Table.
E.g. Product Class = motors Class 2 = Pulleys then cutomers
Click to expand...
1,7,9,76,432,657 are displayed because they match the to fields that have
been choosen
 
Back
Top