Counting in subform with conditions...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

This probably has a simple answer but... I cannot figure it out.

From my Mainform ([Project]) I have 5 fields (Dropdownboxes) where I can
specify 5 different types of Specialities ( [DS1] [DS2] [DS3] [DS4] [DS5] )
and for each of those I need to count how many records in the subform
([Bookings]) I have where the field corresponds. I want to be able to view
this in the mainform. The subform only contains 1 [DS] field per record.

I've tried it in many ways but probably not the right way as I end up with
#Error and #Name or nothing at all.
Anyone who can help ?

I am a novice and this is my first posting here, so I hope I was clear enough.

Thanks,
B



PS. Anyone who knows where to find a translator for expressions? I am
currently using swedish MS Access and am more used to the english version.
 
Ben,

This is made difficult because your tables are not normalized. There is a
natural one-to-many relationship between Projects and ProjectSpecialties, and
so should be in a separate table, and included as a subform on your main
form, linked by the ProjectID. A Specialties table can provide the choices
for entry:

Specialties
--------------------
SpecialtyID AutoNumber (PK)
Specialty

ProjectSpecialties
--------------------
ProjectSpecialtyID AutoNumber (PK)
ProjectID Integer (Foreign Key to Projects)
SpecialtyID Integer (Foreign Key to Specialties)

Once the data is structured correctly, use a Totals query, Grouped By
Specialty, with a Count of ProjectID to determine the number of each
specialty.

The following links cover the basics of database normalization. A Google
Search can provide additional reference.

http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Hope that helps.
Sprinks
 
Hi Sprinks,

Thanks for the advice and the links. I went through them a little bit and I
think that my tables are normalized.
I think that the problem for me, lies in that it's too many tables hehe...

Person
--------------------
Name ID
Name...
Speciality (combo)

Speciality
----------------
Speciality ID
Specialty

Booking
----------------
NameID
ProjectID
....

Project
--------------------
Project ID
SpecialtyID (5 times because for this project 5 different
specialities are allowed)

And here I then have a subform with bookings according to project. this
works perfectly and I have a total of how many bookings per project etc...
but now when, I try to look for the total per Speciality in this same
subform... no results. I tried with DCount and DSum in the expressions but no
success there.
And the goal is to have a textbox with the total per speciality next to the
total of bookings on my projects mainform.

I hope you can guide me some more, cause I really need to have this working.
I was thinking to create extra fields with

IIf([Specialityinbooking] = Forms!Project![Doctor Speciality1] ; 1; 0)

And then get a sum in the subform for this... and then have this 5 times...
But I hope to be able to do this without creating too many extra fields.

Thanks again,

Ben

=Forms!Project![Doctor Speciality]

--
Novice


Sprinks said:
Ben,

This is made difficult because your tables are not normalized. There is a
natural one-to-many relationship between Projects and ProjectSpecialties, and
so should be in a separate table, and included as a subform on your main
form, linked by the ProjectID. A Specialties table can provide the choices
for entry:

Specialties
--------------------
SpecialtyID AutoNumber (PK)
Specialty

ProjectSpecialties
--------------------
ProjectSpecialtyID AutoNumber (PK)
ProjectID Integer (Foreign Key to Projects)
SpecialtyID Integer (Foreign Key to Specialties)

Once the data is structured correctly, use a Totals query, Grouped By
Specialty, with a Count of ProjectID to determine the number of each
specialty.

The following links cover the basics of database normalization. A Google
Search can provide additional reference.

http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Hope that helps.
Sprinks

Ben said:
Hi all,

This probably has a simple answer but... I cannot figure it out.

From my Mainform ([Project]) I have 5 fields (Dropdownboxes) where I can
specify 5 different types of Specialities ( [DS1] [DS2] [DS3] [DS4] [DS5] )
and for each of those I need to count how many records in the subform
([Bookings]) I have where the field corresponds. I want to be able to view
this in the mainform. The subform only contains 1 [DS] field per record.

I've tried it in many ways but probably not the right way as I end up with
#Error and #Name or nothing at all.
Anyone who can help ?

I am a novice and this is my first posting here, so I hope I was clear enough.

Thanks,
B



PS. Anyone who knows where to find a translator for expressions? I am
currently using swedish MS Access and am more used to the english version.
Click to expand...
Click to expand...
 
Hehehe,...
Sprinks... I was a dumb little person and figured it out... I did it with
the last suggestion I made to myself there... and it works...
IIf([Specialityinbooking] = Forms!Project![Doctor Speciality1] ; 1; 0)

I had tried it before but *whisper: I forgot to create extra fields*
I apologize for the stupidity and thank you again for the answer which
helped clear my mind back to the basics.

Greetings from Sweden,

Ben

--
Novice


Sprinks said:
Ben,

This is made difficult because your tables are not normalized. There is a
natural one-to-many relationship between Projects and ProjectSpecialties, and
so should be in a separate table, and included as a subform on your main
form, linked by the ProjectID. A Specialties table can provide the choices
for entry:

Specialties
--------------------
SpecialtyID AutoNumber (PK)
Specialty

ProjectSpecialties
--------------------
ProjectSpecialtyID AutoNumber (PK)
ProjectID Integer (Foreign Key to Projects)
SpecialtyID Integer (Foreign Key to Specialties)

Once the data is structured correctly, use a Totals query, Grouped By
Specialty, with a Count of ProjectID to determine the number of each
specialty.

The following links cover the basics of database normalization. A Google
Search can provide additional reference.

http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Hope that helps.
Sprinks

Ben said:
Hi all,

This probably has a simple answer but... I cannot figure it out.

From my Mainform ([Project]) I have 5 fields (Dropdownboxes) where I can
specify 5 different types of Specialities ( [DS1] [DS2] [DS3] [DS4] [DS5] )
and for each of those I need to count how many records in the subform
([Bookings]) I have where the field corresponds. I want to be able to view
this in the mainform. The subform only contains 1 [DS] field per record.

I've tried it in many ways but probably not the right way as I end up with
#Error and #Name or nothing at all.
Anyone who can help ?

I am a novice and this is my first posting here, so I hope I was clear enough.

Thanks,
B



PS. Anyone who knows where to find a translator for expressions? I am
currently using swedish MS Access and am more used to the english version.
Click to expand...
Click to expand...
 
Back
Top