Issue w/text box and formula

[mari_hitz]

Hi everybody,

Hope you are great. I have an issue with a database I am trying to build. Basically this database contains all the tasks that are performed in my team with the time that takes to perform that task. What I want for the same to do is when a task is ticked, the number that number that appears in amount of time to do it to be sum up in a text box that I had added. I have searched trough the net and I could not find what was wrong with it. Attached you can find, can you give a hand trying to understand what it is wrong?


Thanks!!!

 

[Mihail]

Maybe I can help you, but I can't open your DB.
I use A 2007 but is better to upload again in A 2003 version.

 

[Foe]

so, you want the text box at the top to sum all of the entries in the calculation1 column?

I created a query to do that math and then pointed your text box to it.

If this isn't some stripped down version you threw together to show your issue, I'd suggest looking into naming conventions for your tables, forms and controls.

*I didn't follow any conventions other than what was present. I just threw this together to show you how I did it.

 

[mari_hitz]

Hi everybody,

I am sorry I cannot provide this database that can be compatible with Access 2003 since it does not let me because it says that there are certain features that cannot be transposed into an earlier version.

Thanks Foe for the fix that you made, it really worked. However, I was wondering if the calculation can be made directly in the text box without using a query and a calculated column in the table. Just wondering...

In addition, I should do the calculation for each column, that means I will have to create as many queries as many columns I need to calculate?

Thanks in advance!

 

[Foe]

However, I was wondering if the calculation can be made directly in the text box without using a query and a calculated column in the table. Just wondering...

Is there a particular reason you're hesitant to use queries? It's kinda what they're there for.

In addition, I should do the calculation for each column, that means I will have to create as many queries as many columns I need to calculate?

The math could be done for each column inside the same query. You'd just have to point your text boxes to the appropriate part within the query. For instance, =DLookUp("SumOfCalculation1","tbl1 Query") is pointing at the sum of Calculation1, while =DLookUp("SumOfTransactional Time","tbl1 Query") would point to the sum of Transactional Time*.


It sounds (and looks) like you're trying really hard to treat this like a spreadsheet. If you just want a wall of information with some simple calculations, you might consider using Excel.


*spaces in field names may bite you in the ass someday. You're the only one that sees the field names (unless your form is just an open window to the table as you've done), so they don't need to be pretty for your users. Transactional Time would serve you better if it was named TransactionalTime

 

[mari_hitz]

Thanks Foe for your answer! You are correct, I am actually wrong trying to pass something that is on excel to access, but I don't know otherwise.
You see I have a spreadsheet in excel where I have a double entry table and I have several formulas, however, since the amount of data will be a lot I think is better to have an access database rather than a excel document that, despite the efforts, it only has good formulas.

The excel sheet basically what is does is, it counts with an "If" formula the numbers on the columns and then it multiplies that info and I obtain the amount of people that should be required to perform those tasks based on their estimated time to do them. I can use the querys, that is fine. I will see how I can achieve this in access. Thanks!

 

[JHB]

...However, I was wondering if the calculation can be made directly in the text box without using a query and a calculated column in the table. Just wondering...

In addition, I should do the calculation for each column, that means I will have to create as many queries as many columns I need to calculate?

If you create a sub form based on the table, instead of use the table as "sub form" it would be easier, then you can place (if needed hidden) controls in which the calculation can be done.

 

[mari_hitz]

Thanks everybody for your help and support, it really helps! I have one final question. If I have this list for several countries and the calculation needs to be perform individually for each country, how would be the best way to do it? I am trying to create a single table, with a field named "Country" that contains a dropdown of options based on another table where I have the countries listed. Then I am trying to create a query for each country, then I have a form for each country based on its respective query. I had tried to create a query containing only the calculation for an specific country with an expression and a Sum with an Iif included but, no matter what I do it returns me error either saying a field (calculation1) is not included or that it contains syntax error. Can you help me out on this?

Thanks!

 

[JHB]

Post you database with some sample data (zip it).
And also the name of the form/query in which you have the problem.

 

[mari_hitz]

Hi JHB,
Thanks for your reply!
Please find attached the database, the query in which I have the issue is "testquery". In addition I have added the excel that I have and that I trying to transpose to the access database. Please note that we are trying to do this in access because we have to do the same we did for the country "US" for another 19 countries. And if we do this in excel, we should do 19 sheets for each month, since we did not found the best way to do it.

I thought , based on my little knowledge of access, that it would be better to do it in access. I thought, I could create only one table, create a column named "Country" and then create queries based on the country and create the sums and the ifs. Maybe I was naif, but let me know if I am trying to do is possible or not.

 

[JHB]

Hi JHB,
Thanks for your reply!
Please find attached the database, the query in which I have the issue is "testquery". In addition I have added the excel that I have and that I trying to transpose to the access database. Please note that we are trying to do this in access because we have to do the same we did for the country "US" for another 19 countries. And if we do this in excel, we should do 19 sheets for each month, since we did not found the best way to do it.

I thought , based on my little knowledge of access, that it would be better to do it in access. I thought, I could create only one table, create a column named "Country" and then create queries based on the country and create the sums and the ifs. Maybe I was naif, but let me know if I am trying to do is possible or not.

It is possible, no doubt, but I know to little about from where you data is coming, if it is put into a Excel sheet then keep it there and do the calculation in Excel it is easier for you!
I don't think you need to create a new sheets every month if you register it with a date and with some small macros you can provide the same as you want to do in MS-Access.
If you wish to migrate your excel sheets, (inputting and reporting part), to a pure MS-Access application, then you need to create tables for keeping relevante data and link them together.
In MS-Access you store data vertically and not as in Excell, horizontally. Each empty field is waste of space, it is a good lead when you create tables.
I've fixed you query and have made an extra, which shows the data for the country selected in the combobox. Open the form in which you've the combobox, choose a country (USA or Brazil).

 

[mari_hitz]

Hi guys!

I wanted to thank all of you for helping me. I have managed myself to keep on going without asking too much questions. However I couldn't
I am sorry but I have an issue, based on the combobox I have on form "frm" I would like for the subform "qryUS" that is contained in the form "tbl1" to be updated based on the information that is selected in the combobox.
However, I couldn't make this happen. I have been reading in the net and I have tried with no success to add on the "afterupdate" code: Me!Child20.Form.Requery but it returns me error, it says that the object does not exist. Could you please give me a hand? I am attaching my database here.

 

[JHB]

Is it so you want it?

 

[mari_hitz]

Thanks JHB! You rock, that is exactly what I was looking for! Thanks!

 

[JHB]

You're welcome, luck with your project.
By the way, did you read my signature?