[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding fields using VBA in Access 2007

Posted on 2013-05-16
1
Medium Priority
?
499 Views
Last Modified: 2013-05-20
In my database there is a table called "tbl_Shrinkage" which has the LineNumber, YearMo, HDLR fields that I need to use to tell the vba code what to add.  If the "YearMo" and "HDLR" and the line number is between 1 and 6, I need to total up the Prod_Lbs, SK_LBS, BF_LBS and put it in a table called "tbl_PutDataInto" and in that table for the line number field update that to a 7.  Essentially the YearMO and the HDLR fields have to be the same ie. I want the totals for Prod_LBS, SK_LBS, BF_LBS  for line 1 thru 6 for 201201 (the month of Jan.) for HDLR 020 and put it into a separate table using VBA.
Testing.accdb
0
Comment
Question by:MTMonday
1 Comment
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39171434
I don't see why you would need code at all, but simply a query.

Start off building it step by step:

1. Construct a query that looks tbl_Shrinkage.  Make it a Totals query, grouping on YearMO and HDLR fields, a criteria on the line number, and total the Prod_Lbs, SK_LBS, BF_LBS fields.  Save the query.

2. Construct a Insert query to tbl_PutDataInto using query from #1 as a table, and insert the data into tbl_PutDataInto.

  You can execute query #2 from VBA or a macro.

Jim.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question