report query error

[sobeit via AccessMonster.com]

using 3 tables on a report

table1
fields..

1.code -datafield type is text

table2

1.noofhrs. -
2. remarks - datafield type is text

table3

1totalcost - datafield type is currency
2. remarks - datafield type is text


iv joined the code(table1) - remarks(table2) - remarks(table3)

the problem is i get the wrong sum up of noofhrs and totalcost on my report

is it that iv made a mistake on datafield type using as text or
is it using same field name on 2 diff tables
though iv tried all the possible join types

i am into dead end solving this please help

 

[Miranda]

It doesn't look like you need the third table at all. Since your total cost
is a calculated field, you should do this on the fly instead of storing the
number. If the remarks fields in the two tables are not the exact same
thing, then they really should have different names, but this shouldn't
affect your total cost. Anyway, where are you getting your rate to multiply
by the number of hours?

 

[sobeit via AccessMonster.com]

thank you miranda for your response

for more further details i would like to add

table 2 is direct labor noofhrs of our employee total hrs work for a project
table 3 is direct material totalcost of the materials used on the project

table 1 is the sales order with code for the particular project

iwould like to create report per project on the materials and labors consumed

i hope iv stated the details for the understanding of my question.

thank you again.

It doesn't look like you need the third table at all. Since your total cost
is a calculated field, you should do this on the fly instead of storing the
number. If the remarks fields in the two tables are not the exact same
thing, then they really should have different names, but this shouldn't
affect your total cost. Anyway, where are you getting your rate to multiply
by the number of hours?

using 3 tables on a report

[quoted text clipped - 22 lines]

i am into dead end solving this please help

 

[Miranda]

First, you will need to join your tables differently I think. If these are
the only fields that your tables contain, then you will need to add some. I
would create the following tables/fields:

tblProjectCodes – ProjectCode, ProjectDescription

tblMaterials – MaterialCode, MaterialUnit, MaterialDescription,
MaterialUnitCost (MaterialUnit being box, foot, each, etc. depending on how
the material is sold.)


tblEmployees – EmployeeNumber, EmployeeLastName, EmployeeFirstName,
EmployeeWage

tblProjectDetails – ProjectCode, MaterialCode, EmployeeNumber,
NumberOfMaterialUnits, DirectLaborHours

In your query, you would then pull your information from tblProjectDetails
and tblEmployees and tblMaterials and add calculations to figure
NumberOfMaterialUnits * MaterialUnitCost and EmployeeWage * DirectLaborHours
and label them something like Total Cost of Materials and Total Cost of Labor.

Hope this helps!