UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sum Function Problem, Access 2016    
 
   
faca
post Apr 7 2019, 11:52 AM
Post#1



Posts: 35
Joined: 21-May 18



Hello!

How can i make SUM of value that is calculated in form not in query? Beacuse i tried every option and i always get #error...

Thanks

: Database attached (ServiceParts form)

Attached File  Service.zip ( 273.99K )Number of downloads: 1
Go to the top of the page
 
RJD
post Apr 7 2019, 12:47 PM
Post#2


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: Looks like you have a couple problems with this.

1. I don't see where you have TaxRate in your form record source. So I saved the record source as a saved query (qryForForm), linked in the Materials table and added TaxRate to the selected fields - and made the query the form's record source.

2. You cannot use a control name in the sum calculation. You must use the source fields and do the calculations again in the Sum.

=Sum((([quantity]*[sellingprice]*[TaxRate])/100)+([quantity]*[sellingprice]))

See the attached revision to your db. See if this gives you what you want.

HTH
Joe
Attached File(s)
Attached File  Service_Rev1.zip ( 117.15K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 7 2019, 01:06 PM
Post#3



Posts: 35
Joined: 21-May 18



TaxRate is hidden in bottom left side (see attached image)




Attached File(s)
Attached File  Screenshot_1.png ( 23.44K )Number of downloads: 12
 
Go to the top of the page
 
RJD
post Apr 7 2019, 01:55 PM
Post#4


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Yes, but you are facing even more problems when you use those. These controls will only give you the values related to the record that has focus, and you can't use them in the Sum calculation they way you did them. Simply link in the Material table in a query and get the two values record-by-record. Then they will sum properly when you use the complete calculation.

Is there some reason you don't want to do it this way, which seems to work just fine, whereas your approach does not seem to work?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 7 2019, 02:11 PM
Post#5



Posts: 35
Joined: 21-May 18



The problem is that i actually didn't know how to do it .. this way was the only that worked sad.gif grin.gif

Anyway if you have any better option to do this show me please. I'm not that good at access but i'm slowly learning...

One more question can you tell me how can i set this ServicePart form in Service form so it will auto-resize when ill enter materials?

Also in ServiceParts form i have value OnCompany? (InvoiceType in Material table) but it doesn't display Yes/No option, why is that?



and thanks for your help notworthy.gif

Go to the top of the page
 
RJD
post Apr 7 2019, 02:48 PM
Post#6


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


QUOTE
The problem is that i actually didn't know how to do it .. this way was the only that worked.
Anyway if you have any better option to do this show me please. I'm not that good at access but i'm slowly learning...

Actually, that better way was shown in the previous revision to your db, with a query as the subform record source, which added the other table, and thus the tax rate field. See the latest revision attached.

QUOTE
One more question can you tell me how can i set this ServicePart form in Service form so it will auto-resize when ill enter materials?

I gave you more room for the materials list, so see if that solves your issue with this.

QUOTE
Also in ServiceParts form i have value OnCompany? (InvoiceType in Material table) but it doesn't display Yes/No option, why is that?

The reason for this is that although the cboMaterial row source includes nine fields, your column count was set to seven, with no indication of widths for the last two columns. Once I set that to nine and made the last two 0 width, the OnCompany displays properly as Column(8).

HTH
Joe
Attached File(s)
Attached File  Service_Rev2.zip ( 125.35K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 7 2019, 03:56 PM
Post#7



Posts: 35
Joined: 21-May 18



RJD thank you very much, you are amazing ! notworthy.gif

Last question:

I have old database that is made on same principle like the new one but with less functions (it has all services made in 2018)... In both i have report where i enter VIN or license plate number and than i get list of all previous services for specific car.

My question is.. is it possible to link old and new database together so i can open report in new database and enter VIN or license plate number and ill get list of services from new and also from old database?


once again thank you...
Go to the top of the page
 
RJD
post Apr 7 2019, 05:47 PM
Post#8


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad this is working for you. thumbup.gif

QUOTE
My question is.. is it possible to link old and new database together so i can open report in new database and enter VIN or license plate number and ill get list of services from new and also from old database?

The answer is probably/most likely "yes." If you are configured properly, you have a back-end database with tables and a front-end with everything else. You can link to the back-end tables of the old db from the new front-end, and then combine data as you wish - usually, as long as there is some common field, or a generally common set of fields. You could even use a UNION query to gather the new database records with the old database records, and this will combine them for reporting.

That's about as far as I can go without seeing the two databases and how you wish to combine the data.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 8 2019, 01:49 PM
Post#9



Posts: 35
Joined: 21-May 18



This is how database looks like smile.gif

Attached File(s)
Attached File  Service.zip ( 266.76K )Number of downloads: 6
 
Go to the top of the page
 
RJD
post Apr 8 2019, 07:43 PM
Post#10


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: We'll need both databases and what you wish to combine for reporting between the two. Seeing just the one current db is not enough to answer your question. And there should be some test data for both databases.

Have you tried what I suggested above?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 9 2019, 12:59 PM
Post#11



Posts: 35
Joined: 21-May 18



I didn't try to link databases yet to I'll try to do it on weekend when ill be free grin.gif (i already checked some forums and i saw few options how to do it)

I would like to please if you can check "ReturnMaterial" form... you will see that i have "PartNumber, PartName, DeliveryNumber" but last one "OnCompany" (In Material table i changed InvoiceType to OnCompany) is missing on both sides? Could you check where is the problem?

Thanks
Go to the top of the page
 
RJD
post Apr 9 2019, 06:07 PM
Post#12


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: This is the same problem you had previously. The left cbo does not have the last (5th) column indicated. Change the count to 5 and add a width for it. Same situation on the right cbo. You only accounted for 5 columns when there are 6. Change the column count to 6 and add a width for it.

Any time you change the underlying table or query that feeds the row source by adding a field, you must adjust the columns and widths accordingly. This does not occur automatically.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 14 2019, 03:17 AM
Post#13



Posts: 35
Joined: 21-May 18



Okay so i've tried few options but i can't get it work..

Old database doesn't have customer and vehicle table like the new one but both are in one table called customer

I've attached both now..

Attached File(s)
Attached File  Service.zip ( 760.55K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Apr 14 2019, 09:59 AM
Post#14


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: Well, you didn't say what options you tried and what indications you got - just that "i can't get it work". We don't know what that means.

Did you link the files? Did you try making UNION queries to bring similar fields together in one place? Did you try doing an append of selected old fields into their proper new tables?

It looks like you will have to "map" the old table fields to the new tables to get what you want. This will require some queries to create the correct field sets for appending. In copies of the dbs you should create queries to append the relevant fields of the old tables to the new db tables. Then see if this gives you what you want.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
faca
post Apr 15 2019, 01:33 PM
Post#15



Posts: 35
Joined: 21-May 18



It's ok .. he will just use button to open report in old database directly.

tell me something in new database in ServiceParts form i have option to search by PartNumber how can i make option to also search by PartCode?

Am i able to make this ServiceParts form in any other way?

Beacuse he also wants to enter material directly in ServiceParts form ? I don't know how can i do this since all fields are linked to cboMaterial and they cant be changed.

Example:

If customer needs wheels fixed .. he wants to enter in ServicePart form (Fixed wheels , price €€) without entering this to material table first and than selecting this.
This post has been edited by faca: Apr 15 2019, 01:54 PM
Go to the top of the page
 
RJD
post Apr 15 2019, 07:10 PM
Post#16


UtterAccess VIP
Posts: 9,574
Joined: 25-October 10
From: Gulf South USA


Hi: Sorry, but your design looks like it will preclude what you are asking about using the Part Code to search as well. Your current design uses a combobox to select the Part Number, which is okay, but then you use Column numbers from the combobox to populate most of the other fields. I admit I am confused by your design and, at this point, do not know how to advise you - other than a complete re-visit to your design. And I think you probably need to re-visit the design of the entire database, not just the form.

Perhaps someone else can delve into your db and suggest a better way to design it. And this will take an understanding of your business processes and what you are trying to accomplish.

Good luck with your project. I am sorry I do not have time to devote to this just now.

Regards,
Joe


--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th April 2019 - 02:54 PM