Calculated fields from normalization tables

[Ann Simmons]

I have this table
Subjects
In this table:-

Description

In this description:-
I have
Chemistry
Physics

Question- how can I formulate a query to add the two Subjects to give a TOTAL.

TOTAL = (CHEMISTRY+ PHYSICS)

 

[GPGeorge]

Are these the names of two fields, or two records in a table. You want the total of the two subjects for each record? What is the subject of the table?

 

[Ann Simmons]

Are these the names of two fields, or two records in a table. You want the total of the two subjects for each record? What is the subject of the table?

Two Records

 

[GPGeorge]

Two Records

Building on Pat's SQL, I think you could probably do this with a criteria.

SELECT StudentName, Sum([Subject]) as SumChemistryAndPhysics
FROM tblStudentSubjects
WHERE Subject In("Chemistry","Physics")
GROUP BY StudentName

 

[Mike Krailo]

Question- how can I formulate a query to add the two Subjects to give a TOTAL.

This question doesn't make any sense, unless what you are really asking is how do you concatenate the two subjects together. The question has been asked many times and I believe DBGuy has a code snippet SimpleCSV on his site that creates a comma separated lists of Subjects.

If you just want to count how many subjects you have, then try...

SELECT DISTINCT Count(Subjects.Description) AS CountOfDescription
FROM Subjects;

 

[Ann Simmons]

Thanks everyone

 

[Ann Simmons]

I like this forum. I have learned alot. Be blessed everyone

 

[Mike Krailo]

This does not give you a count of unique descriptions. It gives you a count of rows where the description is not null. SQL Server has an expression that lets you count unique occurrences but not Access.

Pat I'm not sure what the point of making a table of subjects with null values would do for anyone. But whatever you say...

 

[Mike Krailo]

No but when I read it, it seemed like you were saying that Access couldn't count a list of unique values (subjects) because there might be Nulls in there. I do see where I kind of got that twisted around now (whoops!). It's just a totals aggregate query based on the Description field, which according to our wonderful post #1 looks like that's where the subject names are located to me. Your guess is as good as mine, that's just the way I read it. It's definitely not the best choice of words. Probably SubjectName would have been better.

 

[Ann Simmons]

I don't see where I said anything about making a table of subjects with null values. I also mentioned that the request didn't make any sense because you can't do arithmetic on text values which is what the fields the OP asked to sum contained.

I wanted a hint and I got it from @GPGeorge actually I had 3 related tables tblStudents, tblSubjects and tbltermsubjects. In tbltermsubjects that is where there was numeric values mark to be specific. When I used @GPGeorge idea and applied it in tbltermsubjects it worked perfectly. I thank this forum.

NB:- REFER TO #5. It worked perfectly. Thanks.

 

[Mike Krailo]

Well Ann it's great that George was able to read between the lines and help you. The rest of us mere mortals need a little more information first to be helpful. Thank you for finally giving us more of what you were after and clearing things up.

 

[Ann Simmons]

Well Ann it's great that George was able to read between the lines and help you. The rest of us mere mortals need a little more information first to be helpful. Thank you for finally giving us more of what you were after and clearing things up.

You are not mere mortals. You also helped in one way or another. I appreciate it so much. Be blessed.

 

[GPGeorge]

If anything, I was able to read between the lines of this and a hundred other similar questions on forums over the years.....

 

[Ann Simmons]

If anything, I was able to read between the lines of this and a hundred other similar questions on forums over the years.....

Thanks so much. You assisted me a great deal. Be blessed. I learned something new from you. Thanks once again.