How to have certain fields show/not show in a query depending on another field parameter

[TheJosephC]

Hi all,



I'm very new to Access but I'm ready to dive in.

Let me explain what I'm trying to do: I perform inspections of bicycle tires and need to report results from the inspections. Some bike tires need a bunch of parameters checked that others don't -- all the testing depends on the size and manufacturer. Because of the huge number of tires I get in, I want to create an access databases that will output a report telling me what tests I need to perform for the tire size and manufacturer. I also want the report to include the target values of my tire tests.

What I don't know how to do (at the moment) is the following:
1). How do I have fields show/not show in a query depending on another field parameter. E.g. if I'm testing a tire with a specific manufacturer and size I might not need to test tire pressure. However, another size and manufacturer I might. How do I make it show/not show depending if it has a value or not?

2). If I have tires that all start with CBxxxx (xxxx could be any length and letters/numbers), they all have the same shore durometer. Is there a way I can have access automatically display that durometer number in my report without adding thousands of CBxxxx products?

3). If a manufacturer removes a required test, I want to be able to easily remove that test from a "list", not update every tire that is produced from that manufacturer in a table. There could be thousands of tires and this would cause me a lot of trouble if I need to go through thousands of tires manually and remove a test from all of them.

Can you point me in the right direction with these questions? I really apologize for the lack of knowledge but we all started somewhere.

Thanks for the help!

[June7]

So you need to build tables that define the association of tires and tests. I envision 3 tables at minimum.

tblTires

tblTests

tblTireTests

A report would use as RecordSource a query that includes all three tables. Run report filtered to the desired tire.

Dynamically displaying test specifications for groups/classes of tires would be tricky but doable.


Now the question is do you want to store data for inspected tires?


Review http://www.rogersaccesslibrary.com/

[TheJosephC]

Thank you very much June7. Can you please explain what would be stored in tblTireTests as you outlined above? What do I need to research to learn how to dynamically display test specifications (as you mentioned above)?

[June7]

Did you review the tutorials in the referenced link? You need an understanding of relational database principles. That site is one of many that can help. Or get an introductory book.

tblTireTests would have records that associate tires and tests. Fields in the table would be at a minimum TireID and TestID. These would be foreign keys holding the primary key values from the other 2 tables.

As for the specifications, I don't think you are ready to get into that aspect.