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]=[Allow]![TotalHours] & [Leave]![TotalHours] & [ShiftHours]![TotalHours] where the Timecard ID is the lookup for the tables Allow, Leave and ShiftHours

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
webpayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
webpay,

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,AllowTotalHours+LeaveTotalHours+ShiftTotalHours AS AllTotalHours
FROM YourTable;

JeffCoachman

0
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample.
Examine all objects carefully
Access-EEQ-24433893QueryWithCalc.mdb
0
webpayAuthor Commented:
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
SELECT TimeCard.TimeCardNo, Allow.TotalHours, Leave.TotalHours, ShiftHours.TotalHours, [Allow]![TotalHours]+[Leave]![TotalHours]+[ShiftHours]![TotalHours] AS Allhours
FROM Allow, Leave, ShiftHours, TimeCard;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

matthewsslCommented:
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])+Val([Leave]![TotalHours])+Val([ShiftHours]![TotalHours])
AS Allhours
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks Matt.
;-)

webpay,
Can I ask why these fields are defined as Text (strings)?
As you can see, this makes it harder to perform mathematical calculations.

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthewsslCommented:
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.
0
webpayAuthor Commented:
Thanks for pointing out the obvious...!!! Will check this a bit more carfully next time
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
matthewsslCommented:
U da genius

sm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.