-1

I created the table like below

RegID - primary key
Confattid - foreign key
membchoiceid - foreign key and a multivalue field
tourId - foreign key and a multivalue field

and the rest similar like the query below:

all paid fields is in currency format.

I try to calculate all the fee fields to a new field name "total". Even I write the query, it doesn't show any errors, but it didn't show the result when I run the query.

my query:

SELECT ConferenceRegisterFee.RegID, 
       ConferenceRegisterFee.ConfStateRegFeePaid, 
       ConferenceRegisterFee.ConfSpouFeePaid, 
       ConferenceRegisterFee.ConfAttMembDuesPaid, 
       ConferenceRegisterFee.ConfLateFeePaid, 
       ConferenceRegisterFee.ConfRegFeePaid, 
       ConferenceRegisterFee.ConfAttMembDuesPaid, 
       [confstateregfeepaid]+[confspoufeepaid]+[confattmembduespaid]+[conflatefeepaid]+[confregfeepaid] AS total
FROM ConferenceRegisterFee
GROUP BY ConferenceRegisterFee.RegID, 
         ConferenceRegisterFee.ConfStateRegFeePaid, 
         ConferenceRegisterFee.ConfSpouFeePaid, 
         ConferenceRegisterFee.ConfAttMembDuesPaid, 
         ConferenceRegisterFee.ConfLateFeePaid, 
         ConferenceRegisterFee.ConfRegFeePaid, 
         ConferenceRegisterFee.ConfAttMembDuesPaid;

i'm new to access. Do you guys have any ideas to help me get through this?

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
N.Le
  • 1
  • 4
    Why you need Group By , you are not using any aggregate functions. – Shankar Nov 10 '15 at 18:24
  • "but it didn't show the result when I run the query" it shows you no results? Or does it just not show you the results you wanted? – JNevill Nov 10 '15 at 18:36
  • Thank you guys so much for your response. I'm gonna try to use the sum function like Shakar told me. And JNevill, the query is running, but it didn't show the results I wanted. I let you guys know when I run it tonight. Thanks again. – N.Le Nov 11 '15 at 21:50

1 Answers1

0

Thank you so much for your help. I can run the query already, because I forgot the NULL value.

My query: SELECT ConferenceRegisterFee.RegID, ConferenceRegisterFee.ConfStateRegFeePaid, ConferenceRegisterFee.ConfSpouFeePaid, ConferenceRegisterFee.ConfAttMembDuesPaid, ConferenceRegisterFee.ConfLateFeePaid, ConferenceRegisterFee.ConfRegFeePaid, NZ([ConferenceRegisterFee]![ConfStateRegFeePaid])+NZ([ConferenceRegisterFee]![ConfSpouFeePaid])+NZ([ConferenceRegisterFee]![ConfAttMembDuesPaid])+NZ([ConferenceRegisterFee]![ConfLateFeePaid])+NZ([ConferenceRegisterFee]![ConfRegFeePaid]) AS Total FROM ConferenceRegisterFee;

Thanks again

N.Le
  • 1