Find Values

S

s4

Hi,
I have a table named cnom which has fields cname,number and total
I also have a table with 10 (for explaination) fields, p1sX (X being 1-5)
which is a name selected from cname in cnom and
ctl1_diff_x (x again 1-5) which specifies a time but is in text format. This
table also has a date field(key).
I'm trying to create a report that shows the total for each cname in cnom
between two specified dates. I've been trying to 0 total in cnom then add on
the amount in ctl1_diff_x to total in the record where cnom is whatever is
in p1sx. This is really tricky because any name could be in any position
(1-5).

The form sort of looks like this:
p1s1 p1s2 p1s3 p1s4 p1s5
1_diff_1 1_diff_2 1_diff_3 1_diff_4 1_diff_5

So if 'Jeff''s name is in p1s1 I want to add 1_diff_1 to his total in cnom,
but I want to do this for a week at a time, so the next day he might be in
p1s2 so I'd need to add the corresponding in.

Anyway, I thought that once the total had been accumulated from the 7
records in the second table I could just use a report to display each cname
and total.

I'm getting really confused, can anyone help or suggest an easier way or
something?

Thanks very much!!
 
J

John W. Vinson

Hi,
I have a table named cnom which has fields cname,number and total
I also have a table with 10 (for explaination) fields, p1sX (X being 1-5)
which is a name selected from cname in cnom and
ctl1_diff_x (x again 1-5) which specifies a time but is in text format. This
table also has a date field(key).
I'm trying to create a report that shows the total for each cname in cnom
between two specified dates. I've been trying to 0 total in cnom then add on
the amount in ctl1_diff_x to total in the record where cnom is whatever is
in p1sx. This is really tricky because any name could be in any position
(1-5).

The form sort of looks like this:
p1s1 p1s2 p1s3 p1s4 p1s5
1_diff_1 1_diff_2 1_diff_3 1_diff_4 1_diff_5

So if 'Jeff''s name is in p1s1 I want to add 1_diff_1 to his total in cnom,
but I want to do this for a week at a time, so the next day he might be in
p1s2 so I'd need to add the corresponding in.

Anyway, I thought that once the total had been accumulated from the 7
records in the second table I could just use a report to display each cname
and total.

I'm getting really confused, can anyone help or suggest an easier way or
something?

Thanks very much!!
Click to expand...

The biggest problem is that you table design *is wrong*. You're "committing
spreadsheet upon a database", with ten repeating fields where you syould have
five (or three, or six, or however many) *records* in a related table. If
you're storing time durations (the above isn't clear) then I'd really suggest
that you store them as a Number - e.g. a Long Integer count of seconds,
minutes, or hours, whichever granularity is appropriate for your need. This
will make totalling much easier: text strings don't add!

John W. Vinson [MVP]
 
Top