How do I update a subform fields from a single query?

[dudezzz]

How do I update subform fields from a single query?

I have a subform with two fields whose record source is a Query ("subquery")

Code in the query is as follows:

"SELECT Sum(capital) AS cq1
FROM capital_act WHERE (((capital_act.month)='jan' Or (capital_act.month)='feb' Or (capital_act.month)='mar') And ((capital_act.projectid)=forms!frmplan!projectid));"


This query updates the field cq1 in my subform. I want to add 3 other fields for the 2nd, 3rd and 4th quarters. For instance the second quarter field cq2 field would have the capital_act.month as 'apr' or 'may' or 'jun' and so on...


cq1 value gets updated fine. I dont know how to add the fields cq2, cq3, and cq4 on the same subform whose record source would be the same query ("subquery").

I added a second SQL statement in the same query ("subquery") for cq2 but access does not let me add it.

Can somone point me in the right direction please?

Thanks.

 

[RichO]

To begin with, you can simplify your WHERE clause using DatePart and specifying quarters...

SELECT Sum(capital) AS cq1
FROM capital_act WHERE DatePart("q", CVDate(capital_act.month & " 1")) = 1 And capital_act.projectid = forms!frmplan!projectid;

What you probably want to do is design 4 simple queries, one for each quarter. Then design a union query to piece them together and base your subform on the union query.

 

[dudezzz]

How do I update subform fields with a single query?

Thanks for the reply. I am a newbie in Access and not able to understand the simple nuances. Can you give me a step-by-step instructions on how I can go getting this done please? Sorry..I am catching up slowly with Access day-by-day. Thanks a ton!

 

[RichO]

I've attached a simple zipped DB in Access 2000.

There is a sample table with amounts and months and 5 queries, Query5 being the union of queries 1-4. Opening Query5 brings in the capital sums for each of the 4 quarters.

I named the month field Capital_Month. I believe you were using "Month" but it is not recommended to use reserved Access words as field names.

Good luck!

 

[dudezzz]

Using Form Wizard I tried to create a form on your example database above and I was only able to see cq1 from Query5 (UNION query). I dont see cq2, cq3 and cq4. Am I missing something?

If I get your example to work in a form, thats exactly what I need in my database. Any help would be appreciated. Thanks.

 

[RichO]

Well basically when you run this union query, the summed fields will all show the same "caption" even if you name them differently because they appear in the same column.

Does the query return 4 rows? If you are seeing 4 rows under "cq1" they are actually cq1-cq4 but the caption for the field is cq1, if that makes any sense to you.

If you want a separate labeling for each quarter add an unbound field to each query as you can see in the attached example.

 

[dudezzz]

Thanks Richo....I think I didnt communicate my problem correctly here. What I want to do is take your query from the original example database that you sent (1st one) and use that in a form to show cq1 through cq4.

Can you demonstrate your example using a form from the 1st database that you sent showing all the quarter values on the same form?

For instance, the form should look like:

cq1 cq2 cq3 cq4
100 200 300 400


Right now, when I create a form on query5 using your database, I need to click on the record navigator at the bottom to goto cq2, cq3, and cq4.

Sorry, if this is too trivial. I am learning.

 

[RichO]

If you were to change the form's default view property to "Continuous Forms" you should see all 4 quarters but it is shown vertically.

Seeing that you are only using 4 text boxes you can also achieve the desired results (horizontally) using the DSum function. An example of this is frmExample2 in the attached DB. Check out how DSum is used in the control source for each text box much like the criteria in the queries.

I think that might be what you're looking for.

 

[dudezzz]

I am almost there, but not quite yet I like your Dsum example better than Merging queries. I need to combine your Dsum example with an AND condition to sum only the items that corresponds to the "projectid" on this Table.

On the control source of cq1 on my subform, I inserted the expression (taking clue from one of your other recent postings):

=DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And projectid = " & Me.projectid)


When I open the Form (along with the subform), I get a #Name? Error on cq1.
Please note that projectid is also another field I included in my subform which comes up fine.

I wonder where I am going wrong here!! By the way, thanks for all your help so far Richo. Any help to get me to the final step would be greatly appreciated. Thank you...

 

[RichO]

The Me. prefix is for VBA only. Try this:

=DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And projectid = " & [Projectid])

 

[dudezzz]

It works!

Thank you. It works right now. [projectid] field is a TEXT field instead of a NUMBER field. The only change that I made to your formula was:

=DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And projectid = " & "'" & [Projectid] & "'")

I am going to try and make this formula return 0 if nothing is found using the NZ function now.


Thank you for helping me! I learned a lot today. Someday I will be able to return the favor to someone out here.

 

[RichO]

I've gotten alot of help here so I'm just passing on the help. I still have much more to learn about Access. Glad it works.