Wrong calculations in my Calculated Expression field.

M

MYL

I have created an attendance record table and which has the names of people
running down one side and the dates running across the top. Each time a
person is present I enter a '1' and I leave it blank if he or she is absent.
Then I created a query with a column to show the total number of times each
person is present. I have used the Nz function already so I don't understand
why some of the calculations are still wrong. For example a person has been
present 6 times but the number given in the column is 15! I'm new to Access
so would be grateful for any advice anyone may have. Cheers!
 
D

Douglas J. Steele

Show the SQL of your query, and give details of what the tables look like.
 
M

MYL

What does SQL stand for? Sorry I'm quite new to Access!!
Don't know how else to describe the table - it just looks like a page out of
a school register.
I've been working on it a bit more now and have noticed that with my
original calculation I had left out the ,0 part - now the calculation is:

Totals: Nz([KNL 23/06/04],0)+Nz([KN&B 27/09/04],0)+Nz([KF&G
05/10/04],0)+Nz([SiB 12/10/04],0)+Nz([K&I 27/10/04],0)+Nz([CSiKM
10/11/04],0)+Nz([EKM 24/11/04],0)+Nz([FZt£100M 07/12/04],0)+Nz([UKtR&GC
15/12/04],0)+Nz([K&DSC 11/01/05],0)+Nz([EMD 26/01/05],0)+Nz([PKT
08/02/05],0)+Nz([OCU 23/02/05],0)+Nz([KvT 08/03/05],0)+Nz([DKttRP
22/03/05],0)+Nz([SiB 05/04/05],0)+Nz([ELfM 23/05/05],0)+Nz([I&E
01/06/05],0)+Nz([M&CK 14/07/05],0)

Now it works for all my records apart from 4 of them (which is
frustrating!), as I mentioned before, where the answer should be 6, it gives
15, and where the answer should be 2, it gives 11. What else could be wrong?
 
M

MYL

SQL view:

SELECT [Attendance Record].[Full Name], [Attendance Record].[KNL 23/06/04],
[Attendance Record].[KN&B 27/09/04], [Attendance Record].[KF&G 05/10/04],
[Attendance Record].[SiB 12/10/04], [Attendance Record].[K&I 27/10/04],
[Attendance Record].[CSiKM 10/11/04], [Attendance Record].[EKM 24/11/04],
[Attendance Record].[FZt£100M 07/12/04], [Attendance Record].[UKtR&GC
15/12/04], [Attendance Record].[K&DSC 11/01/05], [Attendance Record].[EMD
26/01/05], [Attendance Record].[PKT 08/02/05], [Attendance Record].[OCU
23/02/05], [Attendance Record].[KvT 08/03/05], [Attendance Record].[DKttRP
22/03/05], [Attendance Record].[SiB 05/04/05], [Attendance Record].[ELfM
23/05/05], [Attendance Record].[I&E 01/06/05], [Attendance Record].[M&CK
14/07/05], Nz([KNL 23/06/04],0)+Nz([KN&B 27/09/04],0)+Nz([KF&G
05/10/04],0)+Nz([SiB 12/10/04],0)+Nz([K&I 27/10/04],0)+Nz([CSiKM
10/11/04],0)+Nz([EKM 24/11/04],0)+Nz([FZt£100M 07/12/04],0)+Nz([UKtR&GC
15/12/04],0)+Nz([K&DSC 11/01/05],0)+Nz([EMD 26/01/05],0)+Nz([PKT
08/02/05],0)+Nz([OCU 23/02/05],0)+Nz([KvT 08/03/05],0)+Nz([DKttRP
22/03/05],0)+Nz([SiB 05/04/05],0)+Nz([ELfM 23/05/05],0)+Nz([I&E
01/06/05],0)+Nz([M&CK 14/07/05],0) AS Totals
FROM ([Company Enrolment] INNER JOIN [Delegate Registration] ON [Company
Enrolment].Company = [Delegate Registration].Company) INNER JOIN [Attendance
Record] ON [Delegate Registration].[Full Name] = [Attendance Record].[Full
Name]
WHERE ((([Attendance Record].Status)="Current Member"))
ORDER BY [Attendance Record].[Last Name];
 
J

John Vinson

What does SQL stand for? Sorry I'm quite new to Access!!
Don't know how else to describe the table - it just looks like a page out of
a school register.
Click to expand...

That's the core of your problem. You table structure is incorrect.

A paper form and a normalized database table are VERY DIFFERENT!

It appears that you have a "spreadsheet" design, with dates and other
information (KNL? KN&B?) stored in fieldnames. *THIS IS INCORRECT
DESIGN*. Data should be stored *in fields*, not in fieldnames!
Assuming that the KNL etc. are class names, then a much better table
design would have fields for StudentID, ClassCode, AttendanceDate.
You'ld add one *row* to this tall, thin table for each attendance,
rather than adding a new field every day.

STOP and fix your table structure first. Your queries will become
increasingly complex, and maintenance of your table will become
impossible if you keep on with your current design!

John W. Vinson[MVP]
 
M

MYL

In fact the attendance record table was originally on a spreadsheet in Excel.
Do you mean that I should have the names running across the top (in the top
row) and the dates along the side (in the left column)? However, will this
design work if I have to continually add names as well as dates? You see, we
have new members joining the community on a regular basis as well as events
per month. KNL etc are abbreviations for the event names....
 
J

John Vinson

In fact the attendance record table was originally on a spreadsheet in Excel.
Do you mean that I should have the names running across the top (in the top
row) and the dates along the side (in the left column)? However, will this
design work if I have to continually add names as well as dates? You see, we
have new members joining the community on a regular basis as well as events
per month. KNL etc are abbreviations for the event names....
Click to expand...

That is a good design for a spreadsheet. But Access is not a
spreadsheet! It's a LOUSY design for a relational database.

Spreadsheets lend themselves by their nature to a rectangular grid,
often with values across the top and down the side, with values at the
intersections.

Databases are different: a Table IS NOT A SPREADSHEET. Instead, a
Table contains information about Entities - real-life things, persons,
or events. An Entity has attributes: properties or kinds of
information that you need to know about the entity. For instance, a
People entity would consist of persons; each person would have a
LastName property, a FirstName, maybe a DateOfBirth, contact
information, etc.

The rows are individual entities of the type; the columns are *types
of information*, or Attributes.

In an Attendance application, you would have THREE entities: People;
Events; Attendance. These would correspond to three tables:

People
PersonID Primary Key <some unique identifier for the person>
LastName
FirstName
<other personal info>

Events
EventID
EventDate
EventSubject
<other info about the event>

Attendance
EventID <what event was attended>
PersonID <who attended it>
<maybe other fields about this person's attendance at this event,
such as the role>

Rather than a single big grid, you would have *three* tall-skinny
tables. If twenty people attend the July 30th Summer Barbecue Party,
there'd be a single record in the Events table with an EventDate of
#7/30/2005#, and an EventSubject of "Summer Barbecue"; there would be
twenty rows in the Attendance table with values like

31 1224
31 1310 Chief Cook
31 1491
31 2205 Host
31 4176 Brought the beer
<etc>

assuming that the EventID of the barbecue was 31, and that these were
the PersonID's (linked to an Autonumber, perhaps) of some of the
attendees.


John W. Vinson[MVP]
 
M

MYL

Hi

I have created the 3 tables as suggested:

John Vinson said:
In an Attendance application, you would have THREE entities: People;
Events; Attendance. These would correspond to three tables:

People
PersonID Primary Key <some unique identifier for the person>
LastName
FirstName
<other personal info>

Events
EventID
EventDate
EventSubject
<other info about the event>

Attendance
EventID <what event was attended>
PersonID <who attended it>
<maybe other fields about this person's attendance at this event,
such as the role>
Click to expand...

Question 1: If my Event ID is AutoNumber in the Events Table does it also
have to be AutoNumber in the Attendance Table? Or can I use Long Integer?

Question 2: Is it now possible to run a query to see who has never attended
a single event? If so, how do I do it?

Any help would be appreciated!
 
D

Douglas J Steele

MYL said:
Question 1: If my Event ID is AutoNumber in the Events Table does it also
have to be AutoNumber in the Attendance Table? Or can I use Long Integer?
Click to expand...

In fact, you cannot use AutoNumber in the Attendance table: you must use
Long Integer.
Question 2: Is it now possible to run a query to see who has never attended
a single event? If so, how do I do it?
Click to expand...

The SQL would look something like

SELECT PersonID, LastName, FirstName
FROM People LEFT JOIN Attendance
ON People.PersonID = Attendance.PersonId
WHERE Attendance.PersonId Is Null

There's a Find Unmatched Query Wizard that will help you get this if you're
not comfortable writing SQL.
 
M

MYL

Hi Doug

I tried to adapt your SQL to fit in with my field and table names but it
doesn't seem to work ie I didn't use a PersonID, my primary key was the Full
Name and Company; instead of Attendance and People I used Event Attendance
and Delegate Registration.

I came up with:
SELECT Full Name, First Name, Last Name, Company FROM Delegate Registration
LEFT JOIN Event Attendance ON Delegate Registration.Full Name, Company =
Event Attendance.Full Name, Company WHERE Event Attendance.Full Name, Company
Is Null

I haven't learnt how to design a query in SQL view yet....and how do I find
the Find Unmatched Query Wizard?
 
D

Douglas J Steele

Your SQL is invalid due to spaces in the field and table names. Any field or
table name with spaces in it must be enclosed in square brackets. Your
syntax for the ON is incorrect as well: you need to use the AND operator.

SELECT [Full Name], [First Name], [Last Name], Company
FROM [Delegate Registration] LEFT JOIN [Event Attendance]
ON [Delegate Registration].[Full Name] = [Event Attendance].[Full Name]
AND [Delegate Registration].Company = [Event Attendance].Company
WHERE [Event Attendance].[Full Name] IS NULL

The Find Unmatched Query Wizard is an option when you go to create a new
query.
 
J

John Vinson

my primary key was the Full Name and Company
Click to expand...

Just one warning:

When I used to work at Warner Lambert Co. I had two coworkers named
Dr. Lawrence David Wise, Ph.D., and his colleague Dr. Lawrence David
Wise, Ph.D.

A good candidate primary key should have three properties: it should
be unique; it should be stable (not subject to frequent, or ideally,
any change); and it should be compact.

Person's names and company names fail on all three counts.


John W. Vinson[MVP]
 
M

matunarikouki

MYL said:
Hi Doug

I tried to adapt your SQL to fit in with my field and table names but it
doesn't seem to work ie I didn't use a PersonID, my primary key was the
Full
Name and Company; instead of Attendance and People I used Event Attendance
and Delegate Registration.

I came up with:
SELECT Full Name, First Name, Last Name, Company FROM Delegate
Registration
LEFT JOIN Event Attendance ON Delegate Registration.Full Name, Company =
Event Attendance.Full Name, Company WHERE Event Attendance.Full Name,
Company
Is Null

I haven't learnt how to design a query in SQL view yet....and how do I
find
the Find Unmatched Query Wizard?
Click to expand...
 
M

MYL

Hi Doug

I found and used the Find Unmatched Query Wizard and came up with this,
slightly different to your version but is it still accurate??

SELECT [Delegate Registration].Joined, [Delegate Registration].[First Name],
[Delegate Registration].[Last Name], [Delegate Registration].[Full Name],
[Delegate Registration].Company, [Delegate Registration].Status
FROM [Delegate Registration] LEFT JOIN [Event Attendance] ON [Delegate
Registration].[Full Name] = [Event Attendance].[Full Name]
WHERE ((([Delegate Registration].Status)="Current Member") AND (([Event
Attendance].[Full Name]) Is Null))
ORDER BY [Delegate Registration].Joined, [Delegate Registration].[First
Name], [Delegate Registration].[Last Name];
 
M

MYL

Thanks for the tip!!

John Vinson said:
Just one warning:

When I used to work at Warner Lambert Co. I had two coworkers named
Dr. Lawrence David Wise, Ph.D., and his colleague Dr. Lawrence David
Wise, Ph.D.

A good candidate primary key should have three properties: it should
be unique; it should be stable (not subject to frequent, or ideally,
any change); and it should be compact.

Person's names and company names fail on all three counts.


John W. Vinson[MVP]
Click to expand...
 
D

Douglas J Steele

If it works, it's ok!

Seems to me you had something in your original post about only wanting
current members.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MYL said:
Hi Doug

I found and used the Find Unmatched Query Wizard and came up with this,
slightly different to your version but is it still accurate??

SELECT [Delegate Registration].Joined, [Delegate Registration].[First Name],
[Delegate Registration].[Last Name], [Delegate Registration].[Full Name],
[Delegate Registration].Company, [Delegate Registration].Status
FROM [Delegate Registration] LEFT JOIN [Event Attendance] ON [Delegate
Registration].[Full Name] = [Event Attendance].[Full Name]
WHERE ((([Delegate Registration].Status)="Current Member") AND (([Event
Attendance].[Full Name]) Is Null))
ORDER BY [Delegate Registration].Joined, [Delegate Registration].[First
Name], [Delegate Registration].[Last Name];

Douglas J Steele said:
Your SQL is invalid due to spaces in the field and table names. Any field or
table name with spaces in it must be enclosed in square brackets. Your
syntax for the ON is incorrect as well: you need to use the AND operator.

SELECT [Full Name], [First Name], [Last Name], Company
FROM [Delegate Registration] LEFT JOIN [Event Attendance]
ON [Delegate Registration].[Full Name] = [Event Attendance].[Full Name]
AND [Delegate Registration].Company = [Event Attendance].Company
WHERE [Event Attendance].[Full Name] IS NULL

The Find Unmatched Query Wizard is an option when you go to create a new
query.
Click to expand...
Click to expand...
 
D

Douglas J Steele

Sorry, yes. I was looking at the wrong query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MYL said:
Great!
Yes I only want current members - doesn't my SQL reflect that??!!
Click to expand...
 
M

MYL

I ran a Query on the 3 tables, Company Enrolment, Delegate Registration and
Event Attendance showing Event ID, Full Name, Company and Town. I want to
find out from which towns the attendees came from and how many from each
town. I have only managed to create a Report showing delegates grouped
according to towns. Is it possible to also total them up so that instead of
having to count 35 from a town for example, there is already a figure saying
35 at the bottom of the list?
 
Top