Using Multiple Check Boxes/Data Structure Issue

[hawk_2001 via AccessMonster.com]

Have a question that I can't seem to work through on my own.

I have a table of rates that can/will change from time to time. These rates
are based on weights and distances from the respective hub. For example, in
my table, I have a rate of $17.00 for a "1-50 lb." category (or row) for a
delivery that is "100 miles" (column) from the hub.

I have a form reflecting these numbers.

What I am looking for is to set up this database to allow our sales reps to
choose whatever columns (mileage ranges) they want and have only those
columns appear on a report.

Secondarily, I want to do the same with the weight ranges (rows).

When all is said and done, I want a sales rep to be able to be able to make a
custom report of the weight ranges and milage ranges that he/she wants.

I have tried using check boxes for each column -- using different properties
to turn the data on/off. But, this is proving to be really messy.

I am also trying to create a "normalized" database so that the data is not
repeated multiple times. My problem there is that the weight ranges and
mileage ranges can and do change as well.

I am looking for any suggestions to help get this customized reporting
together.

Thanks in advance for any help.

--Matt

 

[John W. Vinson]

Have a question that I can't seem to work through on my own.

I have a table of rates that can/will change from time to time. These rates
are based on weights and distances from the respective hub. For example, in
my table, I have a rate of $17.00 for a "1-50 lb." category (or row) for a
delivery that is "100 miles" (column) from the hub.

I have a form reflecting these numbers.

What I am looking for is to set up this database to allow our sales reps to
choose whatever columns (mileage ranges) they want and have only those
columns appear on a report.

Secondarily, I want to do the same with the weight ranges (rows).

When all is said and done, I want a sales rep to be able to be able to make a
custom report of the weight ranges and milage ranges that he/she wants.

I have tried using check boxes for each column -- using different properties
to turn the data on/off. But, this is proving to be really messy.

I am also trying to create a "normalized" database so that the data is not
repeated multiple times. My problem there is that the weight ranges and
mileage ranges can and do change as well.

I am looking for any suggestions to help get this customized reporting
together.

If the rate depends on two factors - the weight category and the distance -
then I'd suggest a three field table with fields for WeightCategory, Distance,
and Rate. You can use a Form with combo boxes to select the appropriate
category and distance, and base your report on a query using these combo boxes
as criteria to select the appropriate rate.

What you should NOT have is a table with multiple checkbox fields, or multiple
rate fields, in each record.

 

[hawk_2001 via AccessMonster.com]

Thanks for the help/suggestions. I have set up a three field table as you
suggested. I am well on my way to achieving my desired results. Thanks
again.

Have a question that I can't seem to work through on my own.

[quoted text clipped - 23 lines]

I am looking for any suggestions to help get this customized reporting
together.

If the rate depends on two factors - the weight category and the distance -
then I'd suggest a three field table with fields for WeightCategory, Distance,
and Rate. You can use a Form with combo boxes to select the appropriate
category and distance, and base your report on a query using these combo boxes
as criteria to select the appropriate rate.

What you should NOT have is a table with multiple checkbox fields, or multiple
rate fields, in each record.