Sending a Summed Query Field to a Field in Specific Table

[Beaudry93]

Hello everyone,

This is simple enough, I am just not too familiar with SQL language.

Scenario:

I have a tblEquipmentHourLog where the daily hours from every piece of equipment are logged from a frmDailyTimesheet.

In tblEquipment, all the equipment info is stored which inlcudes a field called TotalHoursLogged.

I created a qryTotalHoursLogged based from the tblEquipmentHourLog where it groups and sums all the hours logged from every equimpent respectively.

Now, I just want to send back the summed hours into the TotalHoursLogged to all of the respective pieces of equipment in the tblEquipment.

[twinnyfo]

Beaudry93,

Here is an easy option:

You could create an Update Query in which tblEquipment and tblEquipmentHourLog have an Inner Join on the Equipment ID. Then update the tblEquipment.TotalHoursLogged to:

tblEquipment.TotalHoursLogged + tblEquipmentHourLog.HoursLogged

This should do the trick. Let us know if you need any help with it.

BTW, welcome to Bytes!

Hope this hepps!

[Beaudry93]

Hello Twinnyfo,

Thanks for the quick reply.

1) I see where your going with this by just adding it as you go but the only issue i have with an update query is it will always prompt an 'are you sure' every time you have to input an hour. (And with 30 some piece of equipment to input daily, the flow would be better if it could just send it automatically)

2) The description from your last comment is a little to vague for me;

i) 'Inner Join' - are you talking about a simple one-to-many relantioship?

ii) that line of text is suppost to go where? in the table, in the query...

Thanks,

[Beaudry93]

I figured out a solution with your idea, i just wrote the following VBA Code in the HoursLogged_AfterUpdate:

TotalHoursLogged = DLookup("TotalHoursLogged", "tblEquipmentRental", "EquipmentNumber='" & Me![EquipmentNumber] & "'") + Me!HoursLogged

[twinnyfo]

Beaudry,

In response to 1): your code to run the Update Query:

DoCmd.Set Warnings False
DoCmd.OpenQuery "qupdEquipmentTotalHours
DoCmd.SetWarnings True

This will take away the verifications.

Concerning 2): Please see the attachment as an example. In this case, I am changing the value of the Field tblACCOfficers.FullName to the value found in atblPromotions.FullName. I am doing this on all records in which the NameChange flag is True. At the same time, I am updating that NameChange flag to False (since this record is being updated to the new name. Notice how the two Tables are have an Inner Join on the SSN. This ensures that the officer with the SSN of "123-45-6789" in tblACCOfficers is matched with the same officer in atblPromotions. But, this update query will only update the officer's FullName if the NameChange flag is true.

NOW, I hope this makes sense.

Please don't hesitate to ask questions! I knew nothing at one point, but it was by asking questions and pushing myself that I learned how to make very robust Databases.

Let me know how this works.

[twinnyfo]

Your workaround will also suffice. As you can see, there are always more ways to skin a cat. It just dependds on when and where you want your updates to occur.

Cheers!