By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Field Totals??

P: n/a
I have a very simple table:

fields
PupilID Unit1 Unit2 Unit3.....Unitn TotalAchieved
001 4-5-04 12-9-03 2

For the Unitxxx fields I input the date the pupil achieved her Unit.
This is set as a Text field, rather than a Datefield. I gather
DateFields could play havic with such calculations.

All I would love to achieve is to have the total NUMBER of Units
achieved in the last filed, TotalAchieved.

I simply cannot work out how to do this. I'd be most grateful for some
inspiration.
thaks,
sharon
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi Sharon,

Please rethink your table design and then getting the total number of units
passed will be easier. Here's how I'd design it.

*tblPupils*
PupilID (PK)
FName
LName

*tblUnits*
UnitID (PK)
UnitName

*tblPupilUnits*
PupilID (FK) from tblPupils
UnitID (FK) from tblUnits
StartDate
DateAchieved

The 2 foreign keys in tblPupilUnits combined make the primary key and this
way prevents one pupil doing the same unit twice but allows for more units
to entered without having to add more fields to the table. DON'T store a
calculated value in a field in a table.

Here's the SQL behind a query to count the number of units completed per
pupil using the above table structure.
SELECT [FName] & " " & [LName] AS Pupil, Count(tblPupilUnits.DateAchieved)
AS Total
FROM tblPupils INNER JOIN tblPupilUnits ON tblPupils.PupilID =
tblPupilUnits.PupilID
GROUP BY [FName] & " " & [LName]
HAVING (((Count(tblPupilUnits.DateAchieved)) Is Not Null));

If you're wanting to view the number of units achieved on a form, the form
should be based on using tblPupils as the record source and it should have a
sub-form using tblPupilUnits as its record source. In the Form Footer of the
sub-form add an unbound text box and for its control source enter:
=Count([DateAchieved]) and name the text box txtAchievedCount. On the main
form add an unbound text box and in its control source enter:
=[YourSubFormName].Form!txtAchievedCount.

Hope this helps
Stewart
"Hans" <go**@magichans.net> wrote in message
news:66**************************@posting.google.c om...
I have a very simple table:

fields
PupilID Unit1 Unit2 Unit3.....Unitn TotalAchieved
001 4-5-04 12-9-03 2

For the Unitxxx fields I input the date the pupil achieved her Unit.
This is set as a Text field, rather than a Datefield. I gather
DateFields could play havic with such calculations.

All I would love to achieve is to have the total NUMBER of Units
achieved in the last filed, TotalAchieved.

I simply cannot work out how to do this. I'd be most grateful for some
inspiration.
thaks,
sharon

Nov 12 '05 #2

P: n/a

Thanks for your help Stewart. I have to learn and think differently
about designs. Clever stuff.

I have entered the info you gave. It seems to work exept for the last
part:
On the main form add an unbound text box and in its control source
enter:
=[YourSubFormName].Form!txtAchievedCount.

This gives me an Error message in the text box. I placed it in the
footer and the detail but it makes no difference. I entered:
=[sub1].Form!txtAchievedCount

and get Error. what am I doing wrong?

Thanks Sharon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a

Stewart, I discovered that I made a mistake but it still does not work

Now I get "#Name" instead or "error":
=[Sub1].Totals!txtAchievedCount (totals= the name of my main form with
the sub Sub1)

What am I mucking up?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a

Sorry for all these postings Stewart, I found the problem!
I failed to name a text box correctly.

Many thanks
S.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.