Nested IF or IIF Formula Help

[MelonFuel]

Hi Guys

I need some help

I'm need some code either nested IF or IIF formula to add up the values if a check box has been ticked.

I may have 1 item or 20 items and the formula needs to add them up aswell. I really don't know if this is possible but this is what my colleague wants. I'm sorry for the brief description thats all he has really said. If you need more information let me know thanks. Any help would be appreciated.

Thanks

 

[RainLover]

You could use the Function DSum. Search Help for correct syntax.

Or create a query that only displays the records that you want. Then you can use the function "Sum".
Use Sum in the footer of your Form or Report.

 

[MelonFuel]

Yeah DSum but how do i put code for checkboxes because the user may also need to change the price but will this effect the nested if, IIF or the Dsum

Thanks

 

[RainLover]

Yeah DSum but how do i put code for checkboxes because the user may also need to change the price but will this effect the nested if, IIF or the Dsum

Thanks

Sorry, I do not understand your last post. Please explain in more detail.

 

[MelonFuel]

I need a nested if or IIF or Dsum in a query.

In a query i have 27 results and only 12 need to be added. i don't know how to go about this calculation. My colleague suggested checkboxes so if the checkbox is ticked on it will add the checkboxes that are ticked and the checkboxes that aren't ticked wont get added.

Hope thats enough if it isn't ill try and explain even deeper but he over complicates things.

Thanks.

 

[RainLover]

Melon

You need to read more in Access Help for DSum.

If that doesn't work go to Google.

Another choice is to design as much as possible then post your Database.

 

[pr2-eugin]

Melon, I am not completely sure what you are trying to do, RainLover suggestion seems to be fairly straight forward. Just lookup on the DSum/Sum for the matter.

If you do not know how to check if the checkbox is ticked or no.. you should use a simple IIF.. as IIF(checkboxFieldName=-1,fieldNameThatYouWantToInclude,0)

-1 means it is ticked.. So give it a try..

 

[Pat Hartman]

You have a spreadsheet rather than a table. You will never be able to work with this table in Access without lots of code. Relational database functions work vertically on rows. They NEVER work horizontally on columns. Either take this back to Excel to work with it or normalize it which will involve creating at least one more table with a row for each of the 27 checkboxes.

 

[MelonFuel]

Melon, I am not completely sure what you are trying to do, RainLover suggestion seems to be fairly straight forward. Just lookup on the DSum/Sum for the matter.

If you do not know how to check if the checkbox is ticked or no.. you should use a simple IIF.. as IIF(checkboxFieldName=-1,fieldNameThatYouWantToInclude,0)

-1 means it is ticked.. So give it a try..

Yeah rainlover's seems a good staring point but my colleague has some really crazy ideas and always seems to want to change it. Above it what i got from what he sent me. Apologies. I am going to try Rainlovers suggestion today aswell as yours and i will post back my results. Thanks

 

[MelonFuel]

You have a spreadsheet rather than a table. You will never be able to work with this table in Access without lots of code. Relational database functions work vertically on rows. They NEVER work horizontally on columns. Either take this back to Excel to work with it or normalize it which will involve creating at least one more table with a row for each of the 27 checkboxes.

I know its driving me up the wall. The current database is in an excel spreadsheet and i have been given the lovely task of converting it into access. All of the other parts have been converted and this is one of the last things todo but i cant get my head around it. I would love to leave it in excel, but they are trying to "accesserise" to whole business to save time on completing work and easy to update and make changes.

Thanks

 

[RainLover]

I don't know how Pat worked out that you were using a Spread Sheet type layout but as usual she is right.

Is this a once only change (Move to Access) nor are you going to continue to use a Spreadsheet.

 

[MelonFuel]

The spreadsheet will be scrapped when this is up and running

 

[RainLover]

The spreadsheet will be scrapped when this is up and running

Have you read up on Normalisation. If not you need to start NOW.

With Access your records go down not across. Look at other Databases like the Northwind Database. There are also many samples in our archives.

I would like to think that you have a genuire desire to learn something new and that you are not doing this just because the Boss says so.

 

[MelonFuel]

Have you read up on Normalisation. If not you need to start NOW.

With Access your records go down not across. Look at other Databases like the Northwind Database. There are also many samples in our archives.

I would like to think that you have a genuire desire to learn something new and that you are not doing this just because the Boss says so.

Yes i have read up on Normalisation. I do have a genuine desire to learn new things but at the current time i haven't got time to sit down and learn it in depth. Im at college 2 days 9 till 5 and work 4 days 7 till 4 plus driving to and from which is about an hour there and an hour back. I'm learning filemaker, javascript, java, photoshop, serif webplus its a alot to take in plus i go running each night because i sit at a desk all day.

I'm sorry that i don't know sometimes what you are talking about i wish i could understand and help people but access isn't my strong point. thats why, I'm having to learn off someone else to quickly know access i usually like to learn on my own

Thanks

 

[Simon_MT]

To progress you may need to upload a databse 2007 version and we can look at it.

Simon

 

[MelonFuel]

this is part of the excel spreadsheet but of of the data taken out.

The lilac blocks are what im trying to put in my access.

 

[RainLover]

Did you know that you can link to the Spreadsheet in the same way you link Tables.

If you do this then you can append records from the SS to a Table in Access.

By looking at your sample I cannot see an identifer to say which record in the SS is related to which record in another Table.

Simon's suggestion of posting your database is a good idea. However please use Access Version 2003. Not everyone has the latest version. Including me.

PS

Good to see you have a healthy work load. best of luck with the studies.

Hope to hear positive things from you soon.

 

[MelonFuel]

Yeah thanks alot mate hopefully in the near future i can help you out

Yeah i've heard that you can do that but how?? That sounds like the most logical way.

Is there an easy way to to stripe out data i dont think that it will be under 2mb but i can upload it in parts or mediafire and send you a link if that alrite with you.

 

[RainLover]

Do a Compact and repair.

Also zip the database.

Going off line. Bed time for me.

 

[MelonFuel]

On second thoughts would it be possible to use conditional formatting for what I'm trying to achieve where if the quantity changes to 21 the number in another number text field will turn to 285 and if its below 20 the number will be 225.