find balance nos

[cliff]

Hi, I am new to access, I need help to solve the problem

My data is from range 1 to 31

sr cdate no1. no2 no.3 no.4 no.5

1 1/9/08 1 6 9 13 25
2 2/9/08 5 8 12 17 20
3 3/9/08 6 12 18 24 31
4 4/9/08 7 17 20 25 31
5 5/9/08 9 16 17 23 28


Now I want to list nos (2 to 31) are not selected\matched and display 26
nos are not selected, if consider only sr 1. I want also list out nos not
matched and display 21 nos are selected, if you consider sr 1 & 2.

further I want list unmatched nos and count such unmatched nos for range of
records like sr.1 to 3 or say 2 to 5 etc


please help to solve this


thanks million

cliff

 

[Duane Hookom]

I would start by normalizing the data to
sr cdate No Value
1 1/9/08 1 1
1 1/9/08 2 6
1 1/9/08 3 9
1 1/9/08 4 13
1 1/9/08 5 25
2 2/9/08 1 5
2 2/9/08 2 8
--- etc --
5 5/9/08 5 28

You should have much better/easier time of creating your lists.

 

[cliff]

hi duane, thanks help. I got till normalisation data. what to do next. I am
confused please help me to find out unmatched nos for particular range say sr
1 to 3, 2 to 5 etc. I want display nos as well as count such nos.


thanks
cliff

 

[Duane Hookom]

I would create a table of all numbers to use as a reference. You can then use
a join that includes all numbers from the numbers table. Display where the
[Value] field is null.