saving query info in a table via a form

[corear]

I have a new case tracking system. The cases table has these fields:

CaseID (Primary Key)
StatusID (looked up in another table - Open, Pending, or Closed)
Status Comment (memo field)
Date&Time (when casse was opened)
ProjectID (looked up in another table - various projects are listed there)
CustomerID (looked up in another table - list of names and other info for each)
Request (memo field)
Result (memo field)
TotalTime (decimal)

I then have another table that keeps track of the steps taken to resolve the cases:

StepTakenID (Primary Key)
CaseID (linked to Cases table)
StepDate&Time (when step was initiated moving forward with the case)
Step (memo field)

I then have another table that keeps track of the resources needed for each step:

ResourceUsedID (Primary Key)
StepTakenID (linked to StepsTaken table)
ResourceID (looked up in another table - list of names and other info for each)
TimeSpent (decimal)
Comments (memo)

====================

Okay, I can run a query that sums up the TimeSpent for all the resources used for all the steps taken for each case

SELECT tblCases.CaseID AS tblCases_CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID AS tblStepsTaken_StepTakenID, tblStepsTaken.CaseID AS tblStepsTaken_CaseID, tblResourcesUsed.StepTakenID AS tblResourcesUsed_StepTakenID, Sum(tblResourcesUsed.TimeSpent) AS SumOfTimeSpent
FROM (tblCases INNER JOIN tblStepsTaken ON tblCases.CaseID = tblStepsTaken.CaseID) INNER JOIN tblResourcesUsed ON tblStepsTaken.StepTakenID = tblResourcesUsed.StepTakenID
GROUP BY tblCases.CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID, tblStepsTaken.CaseID, tblResourcesUsed.StepTakenID;

Notice it Sums the TimeSpent fields for each case and give a SumOfTimeSpent...and that works.

====================

Okay, I have a form that displays information for each case (I can flip through the cases). I cannot seem to populate the TotalTimeSpent field on my form. I would also like to store this value in the TotalTime field in my Cases table (I know this is abnormal, but I still want to do that).

Thanks for any help!!!
chris.orear at ed.gov

[MMcCarthy]

It's probable that your query is not editable. If you want to check then run the query outside of the form. If you cannot add a new record then the query is not editable. You will have to approach this differently if you need to edit the data.

I have a new case tracking system. The cases table has these fields:

CaseID (Primary Key)
StatusID (looked up in another table - Open, Pending, or Closed)
Status Comment (memo field)
Date&Time (when casse was opened)
ProjectID (looked up in another table - various projects are listed there)
CustomerID (looked up in another table - list of names and other info for each)
Request (memo field)
Result (memo field)
TotalTime (decimal)

I then have another table that keeps track of the steps taken to resolve the cases:

StepTakenID (Primary Key)
CaseID (linked to Cases table)
StepDate&Time (when step was initiated moving forward with the case)
Step (memo field)

I then have another table that keeps track of the resources needed for each step:

ResourceUsedID (Primary Key)
StepTakenID (linked to StepsTaken table)
ResourceID (looked up in another table - list of names and other info for each)
TimeSpent (decimal)
Comments (memo)

====================

Okay, I can run a query that sums up the TimeSpent for all the resources used for all the steps taken for each case

SELECT tblCases.CaseID AS tblCases_CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID AS tblStepsTaken_StepTakenID, tblStepsTaken.CaseID AS tblStepsTaken_CaseID, tblResourcesUsed.StepTakenID AS tblResourcesUsed_StepTakenID, Sum(tblResourcesUsed.TimeSpent) AS SumOfTimeSpent
FROM (tblCases INNER JOIN tblStepsTaken ON tblCases.CaseID = tblStepsTaken.CaseID) INNER JOIN tblResourcesUsed ON tblStepsTaken.StepTakenID = tblResourcesUsed.StepTakenID
GROUP BY tblCases.CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID, tblStepsTaken.CaseID, tblResourcesUsed.StepTakenID;

Notice it Sums the TimeSpent fields for each case and give a SumOfTimeSpent...and that works.

====================

Okay, I have a form that displays information for each case (I can flip through the cases). I cannot seem to populate the TotalTimeSpent field on my form. I would also like to store this value in the TotalTime field in my Cases table (I know this is abnormal, but I still want to do that).

Thanks for any help!!!
chris.orear at ed.gov

[corear]

I ended up adding a button to my form to do the calculations when clicked.

1. create another query to SUM all the total times

2. create a form to use data from the new query and fill in the CASEID based on the alreay-open form

3. create a macro to open the form, use the SetValue to sync up the main form with the tomtal time on the new form, and close the new form

==========

So, I have a query that sums up the resource times for each step

I then have another query that comes and sums those for each case.

I used the form wizard to tell it to use data from an existing form to fill in the CaseID.

[PEB]

Hi,

Your TotalTimeSpent maybe is a sum of some fields isn't it?

You can Show it using formula in it's control source

=[Field1]+[Field2]+[Field3]

And to save this value in the database

On Form Current Event choose Event procedure
and type:

Me[TotalTimeSpent]=me![Field1]+me![Field2]+Me![Field3]

:)

I have a new case tracking system. The cases table has these fields:



CaseID (Primary Key)
StatusID (looked up in another table - Open, Pending, or Closed)
Status Comment (memo field)
Date&Time (when casse was opened)
ProjectID (looked up in another table - various projects are listed there)
CustomerID (looked up in another table - list of names and other info for each)
Request (memo field)
Result (memo field)
TotalTime (decimal)

I then have another table that keeps track of the steps taken to resolve the cases:

StepTakenID (Primary Key)
CaseID (linked to Cases table)
StepDate&Time (when step was initiated moving forward with the case)
Step (memo field)

I then have another table that keeps track of the resources needed for each step:

ResourceUsedID (Primary Key)
StepTakenID (linked to StepsTaken table)
ResourceID (looked up in another table - list of names and other info for each)
TimeSpent (decimal)
Comments (memo)

====================

Okay, I can run a query that sums up the TimeSpent for all the resources used for all the steps taken for each case

SELECT tblCases.CaseID AS tblCases_CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID AS tblStepsTaken_StepTakenID, tblStepsTaken.CaseID AS tblStepsTaken_CaseID, tblResourcesUsed.StepTakenID AS tblResourcesUsed_StepTakenID, Sum(tblResourcesUsed.TimeSpent) AS SumOfTimeSpent
FROM (tblCases INNER JOIN tblStepsTaken ON tblCases.CaseID = tblStepsTaken.CaseID) INNER JOIN tblResourcesUsed ON tblStepsTaken.StepTakenID = tblResourcesUsed.StepTakenID
GROUP BY tblCases.CaseID, tblCases.TotalTime, tblStepsTaken.StepTakenID, tblStepsTaken.CaseID, tblResourcesUsed.StepTakenID;

Notice it Sums the TimeSpent fields for each case and give a SumOfTimeSpent...and that works.

====================

Okay, I have a form that displays information for each case (I can flip through the cases). I cannot seem to populate the TotalTimeSpent field on my form. I would also like to store this value in the TotalTime field in my Cases table (I know this is abnormal, but I still want to do that).

Thanks for any help!!!
chris.orear at ed.gov