webpay
asked on
Sum fields based on ID from multiple tables
Hi,
So far I haven't had the need for adding multiple fields together from various tables but the time has come and I'm not getting the answer required.
Here's the example:
[TimeCard]![TotalHours]=[A
I beleive I need to put the expression in the Row Source of [TimeCard]![TotalHours] so the field will auto sum when a TimecardID is entered.
These tables are part of a timecard import process that will eventually turn into an XML import. I have attached the XML output in case I am going about this the wrong way.
Your help would be greatly appreciated...
Timecards.txt
So far I haven't had the need for adding multiple fields together from various tables but the time has come and I'm not getting the answer required.
Here's the example:
[TimeCard]![TotalHours]=[A
I beleive I need to put the expression in the Row Source of [TimeCard]![TotalHours] so the field will auto sum when a TimecardID is entered.
These tables are part of a timecard import process that will eventually turn into an XML import. I have attached the XML output in case I am going about this the wrong way.
Your help would be greatly appreciated...
Timecards.txt
ASKER
Hi,
Getting there, so using my tables, my code concatenates the Totals from each table now.
Allow.TotalHours + Leave.TotalHours + ShiftHours.TotalHours = Timecards.TotalHours
6+7+6=should equal 19 however, it says "676".
Is it the brackets? My code attached.
Thanks, Michelle
Getting there, so using my tables, my code concatenates the Totals from each table now.
Allow.TotalHours + Leave.TotalHours + ShiftHours.TotalHours = Timecards.TotalHours
6+7+6=should equal 19 however, it says "676".
Is it the brackets? My code attached.
Thanks, Michelle
SELECT TimeCard.TimeCardNo, Allow.TotalHours, Leave.TotalHours, ShiftHours.TotalHours, [Allow]![TotalHours]+[Leave]![TotalHours]+[ShiftHours]![TotalHours] AS Allhours
FROM Allow, Leave, ShiftHours, TimeCard;
It looks like you have defined your hours fields as text, so the "+" function concatenates them by default.
use the "Val" function to convert them to numbers in your query and it should work out.
e.g.
Val([Allow]![TotalHours])+
AS Allhours
use the "Val" function to convert them to numbers in your query and it should work out.
e.g.
Val([Allow]![TotalHours])+
AS Allhours
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you don't define them manually when you design the table you gets what they gives you.
You can open the table in design mode and change it if you want. You'll just need to make sure that it doesn't have unintended consequences for other portions of your application.
Glad to help.
You can open the table in design mode and change it if you want. You'll just need to make sure that it doesn't have unintended consequences for other portions of your application.
Glad to help.
ASKER
Thanks for pointing out the obvious...!!! Will check this a bit more carfully next time
Great,
But a split of the points would have been fine as well as.
You can click the "Request Attention" Button, if you would like to change this.
;-)
JeffCoachman
But a split of the points would have been fine as well as.
You can click the "Request Attention" Button, if you would like to change this.
;-)
JeffCoachman
U da genius
sm
sm
What you are showing with the "&" symbol is "concatenation", not "Addition".
For eaxmple:
1&2&3=123
1+2+3=6
As far as I can see this will be a query.
In this query you will have a calculated field (not a "stored" Value) that is called something like: AllTotalHours
This query will be the source for your form.
Something like this:
SELECT Field1,Field2,AllowTotalHo
FROM YourTable;
JeffCoachman