Register Help!!!!!

[darkguyver_2002]

I have a register database which has a table with a list of students all
14000 of them, this table contains the name, address, etc and also fields
named 'Wk1, Wk2, etc, straight through to Wk50. Each of these fields has a
mark in it, / = attended, o = adsent, A= authorised adsent.

I have a query running off this table which should show any students who have
missed 4 weeks or more. but its isnt working.

How do i get the query to check each field for a contiuning adsence, hope you
get what i mean.

thanks in advance.

 

[Duane Hookom]

You have dug yourself a hole with an un-normalized table structure. A
normalized table structure would have the attendance in a related table with
each /,0, or A in a record on its own. Your question could then be answered
with a totals query that calculates across records rather than fields.

Do you need to check all 14000 students? "missed 4 weeks or more" doesn't
say anything about /, o, or A. Is there any possibility of Nulls?

If you can't normalize, I would try create a first query that concatenates
all the weeks together with an expression like:
Wk1_50: [Wk1]&[Wk2]&[Wk3]&...[Wk50]
Then in another query based on this query you could try:
Unexcused: Len(Replace(Replace(Wk1_50,"/",""),"A",""))

I'm not sure how efficient this would be or even if it would work.

 

[darkguyver_2002]

I do have a field with them all the marks in the same field, its then broke
into seperate field later onin the table. I didnt actually create the
database and due to access rights i cannot rewrite the database only this one
table am working one, which as you can tell is poorly designed.

You have dug yourself a hole with an un-normalized table structure. A
normalized table structure would have the attendance in a related table with
each /,0, or A in a record on its own. Your question could then be answered
with a totals query that calculates across records rather than fields.

Do you need to check all 14000 students? "missed 4 weeks or more" doesn't
say anything about /, o, or A. Is there any possibility of Nulls?

If you can't normalize, I would try create a first query that concatenates
all the weeks together with an expression like:
Wk1_50: [Wk1]&[Wk2]&[Wk3]&...[Wk50]
Then in another query based on this query you could try:
Unexcused: Len(Replace(Replace(Wk1_50,"/",""),"A",""))

I'm not sure how efficient this would be or even if it would work.

I have a register database which has a table with a list of students all
14000 of them, this table contains the name, address, etc and also fields

[quoted text clipped - 10 lines]

thanks in advance.

 

[Duane Hookom]

Ok, it would have been appropriate to respond to my suggestion following "If
you can't normalize..."

Did you try my recommendation of finding the length of the expression where
specific values are replaced with zero-length-strings? Did you understand
the expression?

--
Duane Hookom
MS Access MVP
--

I do have a field with them all the marks in the same field, its then broke
into seperate field later onin the table. I didnt actually create the
database and due to access rights i cannot rewrite the database only this
one
table am working one, which as you can tell is poorly designed.

You have dug yourself a hole with an un-normalized table structure. A
normalized table structure would have the attendance in a related table
with
each /,0, or A in a record on its own. Your question could then be
answered
with a totals query that calculates across records rather than fields.

Do you need to check all 14000 students? "missed 4 weeks or more" doesn't
say anything about /, o, or A. Is there any possibility of Nulls?

If you can't normalize, I would try create a first query that concatenates
all the weeks together with an expression like:
Wk1_50: [Wk1]&[Wk2]&[Wk3]&...[Wk50]
Then in another query based on this query you could try:
Unexcused: Len(Replace(Replace(Wk1_50,"/",""),"A",""))

I'm not sure how efficient this would be or even if it would work.

I have a register database which has a table with a list of students all
14000 of them, this table contains the name, address, etc and also
fields

[quoted text clipped - 10 lines]

thanks in advance.