filtering question

[Guest]

Is my boss asking the impossible? I have a table with many columns of data.
I have four columns pertaining to weight; weight 1, weight 2, weight3, weight
4 and I need to filter out the weight in different increments: example weight
1 4001-7001, 7001-9999, <4001, and >9999, then count each one in the range
and make a report based on that.

Can anyone help me and to add to the challenge I am newbie in Access.

TIA
Marilyn

 

[Van T. Dinh]

IMHO, that's a reasonable request.

What makes it hard to do is that the Table seems to be unnormalised because
you have repeating groups of Fields (each group can consist of a single
Field). A normalised (i.e. correctly-structured) Table should not have
repeating groups.

Perhaps, you should normalise your Table(s) first. For basic info., see the
Microsoft Knowledge Base article:

http://support.microsoft.com/?id=209534

 

[Al Camp]

Marilyn,
Base your report on a query with the fields you need for your report.
Then create 4 new columns with calculated fields like this...
W1LessThan4001 : IIF(Weight1 < 4001, 1, 0)
...and
W14001To7001 : IIF(Weight1 >= and Weight1 < 7001, 1, 0)
...do this for all 4 categories

Place those four calculated fields in your report Detail, and then, in
the appropriate footer...
=Count(W1LessThan4001)
and
=Count(W14001To7001)
would yield the count of those weight categories.

Repeat this process for Weight2 thru Weight4

 

[Guest]

Try this:

Select Sum(iif(weight1<4001,1,0)) AS LessThan4001, Sum(iif((weight1>=4001)
AND (weight1<7001),1,0) AS Between4001and7001, Sum(iif((weight1>=7001) AND
(weight1<=9999),1,0) AS Between7001and9999, Sum(iif((weight1>9999),1,0) AS
GreaterThan9999
From [Your Table]

Mauricio Silva

 

[Joseph Meehan]

Is my boss asking the impossible? I have a table with many columns
of data. I have four columns pertaining to weight; weight 1, weight
2, weight3, weight 4 and I need to filter out the weight in different
increments: example weight 1 4001-7001, 7001-9999, <4001, and >9999,
then count each one in the range and make a report based on that.

Can anyone help me and to add to the challenge I am newbie in Access.

TIA
Marilyn

Please check Van's message about normalizing the data. It will not only
help with this problem, but likely eliminate others in the future.

 

[Guest]

Thank you, I will try this information and get back with you.
Marilyn

 

[Guest]

Thank you for your help, I will try it and get back with you.
marilyn

Try this:

Select Sum(iif(weight1<4001,1,0)) AS LessThan4001, Sum(iif((weight1>=4001)
AND (weight1<7001),1,0) AS Between4001and7001, Sum(iif((weight1>=7001) AND
(weight1<=9999),1,0) AS Between7001and9999, Sum(iif((weight1>9999),1,0) AS
GreaterThan9999
From [Your Table]

Mauricio Silva

Is my boss asking the impossible? I have a table with many columns of data.
I have four columns pertaining to weight; weight 1, weight 2, weight3, weight
4 and I need to filter out the weight in different increments: example weight
1 4001-7001, 7001-9999, <4001, and >9999, then count each one in the range
and make a report based on that.

Can anyone help me and to add to the challenge I am newbie in Access.

TIA
Marilyn

 

[Guest]

Thank you for the information, I will print it off and read it . It shoud
also help with future tables.
Marilyn