NESTED IF's and COUNTIF's in ACCESS'97 & attached EXCEL'97

  • Thread starter Thread starter Iain Halder
  • Start date Start date
I

Iain Halder

Hello,

I have written an ACCESS '97 database and in using it have felt the
need to improve it further.

Can anyone advise on this problem which is in two parts?

I need to be able to input something into one field and depending on
what I put in one or two other fields should change and update
automatically.

For example in field one I should be able to put a post/zip code then
in other fields in the same database I should see a catchment area for
a client, a ward and a community team being listed.

I have tried doing the IF ... THEN ... ELSE thing but it does not work
at all and need advice from anyone who can help me here.

The second part is that in an EXCEL '97 spreadsheet I need to be able
to do a comparative count. How many times do item one AND item two
occur from a series of many other options?

I tried putting two countif's together but all it did was add the
total of the two fields together and not how many times item one and
two coincided together at the same time.

This one has me stuck and thanks in advance anybody who can be of
help!!!

Iain Halder
Rescued Cats & Kittens Needing Homes
 
Iain

1. Is it possible to make a separate table, that lists all the relevant
post codes, along with their corresponding catchment areas, wards, and
community teams? If so, that would be the way to go, I reckon. It is
not really correct to have the catchment area, ward, and community team
fields in your client table, as this is derivable data based on the post
code. You can base your client form on a query, which includes the
client table and also the postcodes table, joined (probably left join)
on the postcode field. That way, you can include the catchment area,
ward, and community team fields from the postcode table into the query,
and therefore represent them on the form, and when you enterr a postcode
on the form, the corresponding catchment area, ward, and community team
will automatically be shown. However, with this approach, I would set
the Enabled property of the catchment area, ward, and community team
controls on the form to No and their Locked property to Yes, as this is
not the place for these values in what is essentially a lookup table to
be editable.

2. As for the counting of records according to criteria, this is very
easy to do with a Query in Access, but I don't know about using Excel
for this, so hopefully someone else will pipe in on this one.
 
Thanks! I will give this a shot!

Iain Halder

Iain

1. Is it possible to make a separate table, that lists all the relevant
post codes, along with their corresponding catchment areas, wards, and
community teams? If so, that would be the way to go, I reckon. It is
not really correct to have the catchment area, ward, and community team
fields in your client table, as this is derivable data based on the post
code. You can base your client form on a query, which includes the
client table and also the postcodes table, joined (probably left join)
on the postcode field. That way, you can include the catchment area,
ward, and community team fields from the postcode table into the query,
and therefore represent them on the form, and when you enterr a postcode
on the form, the corresponding catchment area, ward, and community team
will automatically be shown. However, with this approach, I would set
the Enabled property of the catchment area, ward, and community team
controls on the form to No and their Locked property to Yes, as this is
not the place for these values in what is essentially a lookup table to
be editable.

2. As for the counting of records according to criteria, this is very
easy to do with a Query in Access, but I don't know about using Excel
for this, so hopefully someone else will pipe in on this one.
Click to expand...

Rescued Cats & Kittens Needing Homes
 
Back
Top