query for field a based on contens field b

  • Thread starter Thread starter Dale Craig
  • Start date Start date
D

Dale Craig

I have: IIf([count compl]<>0,+([Book 1]+[Book 2]+[Book 3]
+[Book 4]+[Book 5] and goes on to use this result.
I want book 1 only if corresponding d1 = 2 - likewise
book 2 only if d2 = 2 etc. Last semester, I dumped data -
not the best way to go.
I have a very comprehesive program built by an out of
area volunteer - multiple tables, forms, Reports, 150 +
queries etc. I am reasonably fluent in DBASE 3+ but
minimual in ACCESS so far and can't tear the database
apart to rebuild. 95% is working fine. (in DBASE - I
would write program to copy file - convert fields to
character - replace with spaces and print report - again
not the best way buy very do-able.
Thank you.
 
Dear Dale:

Your situation is not very clear to me, but as a guess I think you
want to add Book1 only when some other column d1 = 2. That might be
written as:

IIf(d1 = 2, [Book 1], 0)

Place this in your existing formula in place of [Book 1], and do the
same thing for the other Books.

I'm afraid your structure looks badly non-normalized, so normalization
might be a good area of study for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
T ellison: Thank you - I will "Brave the lion's den" (on
a copy) and see what happens - what you have given me
looks like it is what I want. I have less than 3 months
working my Program - and while I specified it - I am in no
way ready to design it. Normalization would, as I
understand it, require major revisions as to the tables
and resulting forms/reports. _ have a dozen books - all
are "Access" (for Access read "Greek") to me so far.
Actually, I can design and modify forms, reports tables
and queries (Including update and append) to a certain
level. Thanks again.
Dale Craig, Counselor, MA Ed cnsl;Sherman Indian High
School; USAF Msgt(ret) School
 
Dear Dale:

"what you have given me looks like it is what I want."

I'm glad if this could help you.

"Normalization would, as I understand it, require major revisions as
to the tables and resulting forms/reports."

There's the rub. Getting it right to start with takes a little extra
study and effort. Fixing it afterward can be very painful indeed.
Sorry, but that's the way it has always been. After you build a
considerable application on top of a bad table design, all you have is
regrets.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


T ellison: Thank you - I will "Brave the lion's den" (on
a copy) and see what happens - what you have given me
looks like it is what I want. I have less than 3 months
working my Program - and while I specified it - I am in no
way ready to design it. Normalization would, as I
understand it, require major revisions as to the tables
and resulting forms/reports. _ have a dozen books - all
are "Access" (for Access read "Greek") to me so far.
Actually, I can design and modify forms, reports tables
and queries (Including update and append) to a certain
level. Thanks again.
Dale Craig, Counselor, MA Ed cnsl;Sherman Indian High
School; USAF Msgt(ret) School
-----Original Message-----
Dear Dale:

Your situation is not very clear to me, but as a guess I think you
want to add Book1 only when some other column d1 = 2. That might be
written as:

IIf(d1 = 2, [Book 1], 0)

Place this in your existing formula in place of [Book 1], and do the
same thing for the other Books.

I'm afraid your structure looks badly non-normalized, so normalization
might be a good area of study for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...
 
Back
Top