By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,606 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,606 IT Pros & Developers. It's quick & easy.

Sending a Summed Query Field to a Field in Specific Table

P: 14
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.
Jun 10 '14 #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,483
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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Jun 10 '14 #2

P: 14
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,
Jun 10 '14 #3

P: 14
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
Jun 10 '14 #4

twinnyfo
Expert Mod 2.5K+
P: 3,483
Beaudry,

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

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Set Warnings False
  2. DoCmd.OpenQuery "qupdEquipmentTotalHours
  3. 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.
Attached Images
File Type: jpg Update Query.jpg (53.5 KB, 102 views)
Jun 10 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,483
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!
Jun 10 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.