Sql error

[bobykuriakose12]

Dear sir,

i created one project its a students record.i have one table and 18 fields.

i create 8 Fields for amount entry and they have 8 date entry.





eg: installment 1-->amount--->date1(date=12/6/2013)
installment 2--->amount--->date2(date=13/6/2013)
. installment 3--->amount--->date3(date=13/6/2013)
.
.
installment 8--->amount-->date8(date=12/6/2013)

I WANT-- if i enter date 12/6/2013 on search option will get sum of installment1 and installment8

if i enter date 13/6/2013 on search option will get sum of installment3 and installment2



what is the query for this?

[June7]

Do you understand how Access works with date values? Review http://allenbrowne.com/ser-36.html

Use a GROUP BY (Totals) query with filter criteria. Access Help has guidelines. The SQL could be like:

SELECT Sum([amount]) AS SumAmt FROM tablename HAVING [datefield]=#12/6/2013#;

[bobykuriakose12]

thank you for your information. i am opened the website http://allenbrowne.com/ser-36.html but i am not understanding.if i trying to your query----code is

sql = "SELECT sum([instamt1]) FROM ponnu HAVING [instdate1]=#" & dtpmyon.Value & " # " successfully i get sum of instamt1 on instdate . ........but i want to sum of instamt1 and instamt2 on instdate1 and instdate2.

i trying this code but error,

sql = "SELECT sum([instamt1][instamt2]) FROM ponnu HAVING [instdate1][instdate2]=#" & dtpmyon.Value & " # "

can you please verify this code and what are changes are required.

regards
boby

[June7]

I just reread original post and realized this is apparently not a normalized data structure. It will require a VBA function or UNION query. A UNION query will essentially reorganize the data into a normalized sructure as it should have been to begin with. Then use the UNION query as data source for the summation and filtering you want. There is no wizard or designer for UNION, must type into the SQL View editor of query designer.

SELECT StudentID, "Install1" AS InstallNo, Date1 AS InstDate, Amount1 AS Amount FROM tablename
UNION SELECT StudentID, "Install2", Date2, Amount2 FROM tablename
UNION SELECT StudentID, "Install3", Date3, Amount3 FROM tablename
UNION SELECT StudentID, "Install4", Date4, Amount4 FROM tablename
UNION SELECT StudentID, "Install5", Date5, Amount5 FROM tablename
UNION SELECT StudentID, "Install6", Date6, Amount6 FROM tablename
UNION SELECT StudentID, "Install7", Date7, Amount7 FROM tablename
UNION SELECT StudentID, "Install8", Date8, Amount8 FROM tablename;

I have found that VBA does not like to open a recordset from a UNION query. So if this is why you are building SQL statement in code, you might encounter same issue. Why are you building SQL in VBA?

Until you get the data structure fixed, these types of data manipulation will continue to be problemmatic.

You are showing dates as dd/mm/yyyy. Access convention is mm/dd/yyyy. Recommend you carefully study the link I provided.

[bobykuriakose12]

sorry sir,

i am trying this code its fail.can you please give me another example for sum of two or more fields

[June7]

It is simple to add multiple fields. Do you always want to add installment1 and installment8? Do you always want to add installment2 and installment3?

SELECT installment1 + installment8 AS Sum1and8, installment2 + installment3 AS Sum2and3 FROM tablename;

However if you want to do it based on dynamic conditions - could conditions change and you want to instead add installment2 and installment8 - that is not easy.

Why does the UNION fail - error message, wrong results, nothing happens? Show the query you attempted.

[bobykuriakose12]

sorry sir, i try so many methods but its fail.i think my question wrong because i also attach screen shorts.can you please check the screen shorts.

my question: if i enter 13/June/2013 on 'search field' (in screen short-picture), so system check the 'entry field' (in screen short-picture) and check the 13/June/2013 on date field and sum of the values,( like. screen short. amt1+amt3+amt5+amt8)..

if i enter 15/June/2013 on search field get the sum of amt4+amt7


can you please check the screen short and my comment...

regards
boby

[June7]

That image doesn't change any of the advice already provided.

Additional note, a UNION query can be used as data source for data manipulation but it is not an editable query - cannot use it for data entry.

Also, you need to understand how to deal with the date structure. dd/mm/yyyy is not Access convention and could cause issues with filter/sort. Carefully study the Allen Browne link about using international date structure.

[bobykuriakose12]

sorry i am not understand what you mean,because i am student.i have little knowledge about sql,can you please provide a query regarding my screen short.

regards
boby

[June7]

I already did that in post 4.

Modify the UNION example to use your table and field names.

As already stated, your data structure is not optimal and will cause you a lot of frustration. I have explained best I can what you must do to work with the data as is. If it is too difficult then should get the data properly structured and these difficulties will be eliminated.