Help adding amounts from 3 tables

[mrfixit1170]

I have a query(TestQry1) that combines results from 2 tables together(tbl_AVUM_Scored_Data)and(
tbl_SME_AVUM_Edit), It has IETM_ID and edit information.

Attachment 5058

SELECT tbl_AVUM_Scored_Data.Record_ID, tbl_AVUM_Scored_Data.EI_ID, tbl_AVUM_Scored_Data.Event_Date, 

tbl_AVUM_Scored_Data.Event_No, tbl_AVUM_Scored_Data.Sys_Code, tbl_AVUM_Scored_Data.IETM_ID, 

tbl_SME_AVUM_Edit.MOS_ID, tbl_SME_AVUM_Edit.Time
FROM tbl_AVUM_Scored_Data LEFT JOIN tbl_SME_AVUM_Edit ON tbl_AVUM_Scored_Data.Record_ID = 

tbl_SME_AVUM_Edit.Record_ID
GROUP BY tbl_AVUM_Scored_Data.Record_ID, tbl_AVUM_Scored_Data.EI_ID, tbl_AVUM_Scored_Data.Event_Date, 

tbl_AVUM_Scored_Data.Event_No, tbl_AVUM_Scored_Data.Sys_Code, tbl_AVUM_Scored_Data.IETM_ID, 

tbl_SME_AVUM_Edit.MOS_ID, tbl_SME_AVUM_Edit.Time;

tbl_AVUM_Scored_Data
Record_ID
EI_ID
Event_Date
Event_No
Sys_Code
IETM_ID
TimeStamp

tbl_SME_AVUM_Edit
Record_ID
Maint_Funct
MOS_ID
Time
Comments
Date Added

I use another query(qryTest2) to pull mos and time data from another table(tbl_List_SiteVisit2) and combine the

results from the first query(TestQry1) to add everything together.


Attachment 5060

qryTest2

SELECT TestQry1.EI_ID, TestQry1.Event_Date, TestQry1.Event_No, TestQry1.Sys_Code, tbl_List_SiteVisit2.MOS_ID, 

Sum(Int(10*([tbl_List_SiteVisit2].[Quantity]*[tbl_List_SiteVisit2].[Time]))/10) AS ManHours
FROM TestQry1 LEFT JOIN tbl_List_SiteVisit2 ON TestQry1.IETM_ID=tbl_List_SiteVisit2.IETM_ID
GROUP BY TestQry1.EI_ID, TestQry1.Event_Date, TestQry1.Event_No, TestQry1.Sys_Code, tbl_List_SiteVisit2.MOS_ID;

tbl_List_SiteVisit2
ID
IETM_ID
Maint_Funct
MOS_ID
Quantity
Time
TimeStamp

Attachment 5059

My issue is the calulation for qrytest2 works fine except when I have to add the edit information, it gets

recorded in my tbl_SME_AVUM_Edit, however it does not get added to the final mos time calculation that is displayed in the AVUM Totals Listbox which is based off of the qryTest2.

Any thoughts...I will give more information for clarity if needed.

[June7]

Think I was following OK until the second to last statement. Why are you saving calculated values? How are you saving the values you say are recorded? What does a listbox have to do with these calcs?

[mrfixit1170]

The fields are not listboxes, they are subforms of the queries placed on the Scoring page. The values are not saved they are just calculated when the forms are re-queried. My mistake....

[June7]

Okay, but I still don't understand what you need to have happen. Do you want to provide project for analysis?

[mrfixit1170]

June7,
I forgot to Thank you for your input on the last post.
Anyway, the mechanics for adding the MOS and Time to the Task is in place. However if the analyst needs to edit time I have a table that collects the Edits but I can not figure out how to add it into the qryTest2 to reflect the totals time per MOS? (tbl_SiteVisit2) plus (tbl_SME_AVUM_Edit)

The time is calculated normally when no edits are need by pulling Time from tbl_SiteVisit2 and Grouping by MOS.

I hope this helps! Thanks again for the input.

[mrfixit1170]

June7,
I'm not sure if I answered your question in my last post, so here it is (I hope),
I need to have reflected (in AVUM Totals) the total times from all 3 tables by MOS with to include what the analyst edit to the tasks.
So if a task has MOS 15R and the standard time is 1.5 in AVUM Totals the analyst should see 15R.....1.5. If the analyst see more time to add to the task, say + 15R......1.5, then after the edit in the AVUM Totals here should see 15R....3.0.

I hope this makes sense? Thanks for the help.

[June7]

Seems that saving the analyst edits and requerying should accomplish refreshing the calcs but guess I will have to review the project if you want me to advise further. Attach it to a post.

[mrfixit1170]

Having trouble uploading dbase can only get it shrunk down to 2.8mb in a zip, can I send it another way. I have attached instructions for the interface but I don't know if it will help you?

[June7]

Did you run Compact & Repair first? Make copy, delete most data, leave enough records to test. Delete objects unrelated to the issue. Still too big? Upload to a fileshare site such as box.net and post link to the file.

Might be just a matter of requerying the form after a record is saved. Trick is figuring out what event to accomplish this in.

A relational database concept is 'enter raw data, do calcs in reports'. That is why doing aggregate calcs realtime in forms is tricky. The concept is to enter data and not care about totals calcs but to produce a report to do this data manipulation.

[mrfixit1170]

June7,
I actually did figure out a fix for the query calc. I ended up creating a query that formatted the same column information for my 2 tables. Then I used a union query to bring all of my data together. I then used the results of the union query to build a sub form to to display the totals. It was a round about way to go but it seems to work.
I understand it is 'bad practice" to see real time calc's in a form, however this is a requirement for the project and I can't get away from it.
Thanks again for everyone who advised and offered input. I am done with this crazy project.

[June7]

Did cross my mind might need a union in there somewhere but needed to analyse project. Glad you got it figured out.