Totalling a field based on results of fields from a query

[Guest]

I'm very new to Access and VBA. I've been reading and reading, but I can't
seem to figure this one out. Any help you can provide would be appreciated.
If I was using some other type of programming, I would try a do while loop,
but I'm not sure how to do this in Access or where it should be done.

I'll probably botch the explanation, but here goes.

I have an input form that accepts parameters that it is passing to a query
and displaying in a new form. Inside this form are the results of my query
as expected. Now I need a total field on the result form, but the total is
based on sameness of some data within the form. It is also not a straight
calculation (1+1), I have to compare the field to get the higher value of the
field and that must be added to the total amount.

Here's an example of data that might make it easier to see what I'm trying
to say.

Bon 20060101 3
Bon 20060101 1
Bon 20060101 2
Bon 20061101 1
Bon 20061101 2
bon 20061201 1
bon 20061201 1

from the above, the total should be 6. I'm sure this can be done
programmatically, just not sure how to do it in VBA or where it should go.

Any help/direction you can provide would be greatly appreciated.

THANK YOU!

Bonnie

 

[Marshall Barton]

I'm very new to Access and VBA. I've been reading and reading, but I can't
seem to figure this one out. Any help you can provide would be appreciated.
If I was using some other type of programming, I would try a do while loop,
but I'm not sure how to do this in Access or where it should be done.

I'll probably botch the explanation, but here goes.

I have an input form that accepts parameters that it is passing to a query
and displaying in a new form. Inside this form are the results of my query
as expected. Now I need a total field on the result form, but the total is
based on sameness of some data within the form. It is also not a straight
calculation (1+1), I have to compare the field to get the higher value of the
field and that must be added to the total amount.

Here's an example of data that might make it easier to see what I'm trying
to say.

Bon 20060101 3
Bon 20060101 1
Bon 20060101 2
Bon 20061101 1
Bon 20061101 2
bon 20061201 1
bon 20061201 1

from the above, the total should be 6. I'm sure this can be done
programmatically, just not sure how to do it in VBA or where it should go.

This is a tricky problem. I think you should create another
query to do the calculation:

TotalQuery:
SELECT Sum(V) As Total
FROM (SELECT Max([the value field]) As V
FROM yourquery
GROUP BY [the other field])

Then the form can use a text box expression to retrieve and
display the total:
=DLookup("Total", "TotalQuery")

Don't forget to requery the text box whenever you add or
modify a record. Use the form's AfterUpdate event
procedure:
Me.thetextbox.Requery

 

[Guest]

I did get the results from another query that I was looking for using the
group by, but now they've added another problem to this issue. They want the
max of the number based upon whether a different field entry has a length of
4 or, if the length is 5, they just want to add '1' to the total. Can a
conditional statement be used in this type of query? I don't know if an IIF
statement would work in this situation or not. Thank you for your reply!

I'm very new to Access and VBA. I've been reading and reading, but I can't
seem to figure this one out. Any help you can provide would be appreciated.
If I was using some other type of programming, I would try a do while loop,
but I'm not sure how to do this in Access or where it should be done.

I'll probably botch the explanation, but here goes.

I have an input form that accepts parameters that it is passing to a query
and displaying in a new form. Inside this form are the results of my query
as expected. Now I need a total field on the result form, but the total is
based on sameness of some data within the form. It is also not a straight
calculation (1+1), I have to compare the field to get the higher value of the
field and that must be added to the total amount.

Here's an example of data that might make it easier to see what I'm trying
to say.

Bon 20060101 3
Bon 20060101 1
Bon 20060101 2
Bon 20061101 1
Bon 20061101 2
bon 20061201 1
bon 20061201 1

from the above, the total should be 6. I'm sure this can be done
programmatically, just not sure how to do it in VBA or where it should go.

This is a tricky problem. I think you should create another
query to do the calculation:

TotalQuery:
SELECT Sum(V) As Total
FROM (SELECT Max([the value field]) As V
FROM yourquery
GROUP BY [the other field])

Then the form can use a text box expression to retrieve and
display the total:
=DLookup("Total", "TotalQuery")

Don't forget to requery the text box whenever you add or
modify a record. Use the form's AfterUpdate event
procedure:
Me.thetextbox.Requery

 

[Marshall Barton]

I think that's too vague for me to follow. Could you use
real field names instead of "different field", etc? An
example of some data and the expected results would also
help explain what you are trying to do.

Since you want to modify a query, you should also post a
Copy/Paste of your current query's SQL view so we can see
what your are working with.
--
Marsh
MVP [MS Access]

I did get the results from another query that I was looking for using the
group by, but now they've added another problem to this issue. They want the
max of the number based upon whether a different field entry has a length of
4 or, if the length is 5, they just want to add '1' to the total. Can a
conditional statement be used in this type of query? I don't know if an IIF
statement would work in this situation or not. Thank you for your reply!

I'm very new to Access and VBA. I've been reading and reading, but I can't
seem to figure this one out. Any help you can provide would be appreciated.
If I was using some other type of programming, I would try a do while loop,
but I'm not sure how to do this in Access or where it should be done.

I'll probably botch the explanation, but here goes.

I have an input form that accepts parameters that it is passing to a query
and displaying in a new form. Inside this form are the results of my query
as expected. Now I need a total field on the result form, but the total is
based on sameness of some data within the form. It is also not a straight
calculation (1+1), I have to compare the field to get the higher value of the
field and that must be added to the total amount.

Here's an example of data that might make it easier to see what I'm trying
to say.

Bon 20060101 3
Bon 20060101 1
Bon 20060101 2
Bon 20061101 1
Bon 20061101 2
bon 20061201 1
bon 20061201 1

from the above, the total should be 6. I'm sure this can be done
programmatically, just not sure how to do it in VBA or where it should go.

This is a tricky problem. I think you should create another
query to do the calculation:

TotalQuery:
SELECT Sum(V) As Total
FROM (SELECT Max([the value field]) As V
FROM yourquery
GROUP BY [the other field])

Then the form can use a text box expression to retrieve and
display the total:
=DLookup("Total", "TotalQuery")

Don't forget to requery the text box whenever you add or
modify a record. Use the form's AfterUpdate event
procedure:
Me.thetextbox.Requery

 

[Guest]

Marshall:

It appears the user changed their mind again. I think the solution you
provided will work for this. Thank you so much for your time and advise!

Have a great day!

Bonnie

I think that's too vague for me to follow. Could you use
real field names instead of "different field", etc? An
example of some data and the expected results would also
help explain what you are trying to do.

Since you want to modify a query, you should also post a
Copy/Paste of your current query's SQL view so we can see
what your are working with.
--
Marsh
MVP [MS Access]

I did get the results from another query that I was looking for using the
group by, but now they've added another problem to this issue. They want the
max of the number based upon whether a different field entry has a length of
4 or, if the length is 5, they just want to add '1' to the total. Can a
conditional statement be used in this type of query? I don't know if an IIF
statement would work in this situation or not. Thank you for your reply!

AccessNewbie wrote:

I'm very new to Access and VBA. I've been reading and reading, but I can't
seem to figure this one out. Any help you can provide would be appreciated.
If I was using some other type of programming, I would try a do while loop,
but I'm not sure how to do this in Access or where it should be done.

I'll probably botch the explanation, but here goes.

I have an input form that accepts parameters that it is passing to a query
and displaying in a new form. Inside this form are the results of my query
as expected. Now I need a total field on the result form, but the total is
based on sameness of some data within the form. It is also not a straight
calculation (1+1), I have to compare the field to get the higher value of the
field and that must be added to the total amount.

Here's an example of data that might make it easier to see what I'm trying
to say.

Bon 20060101 3
Bon 20060101 1
Bon 20060101 2
Bon 20061101 1
Bon 20061101 2
bon 20061201 1
bon 20061201 1

from the above, the total should be 6. I'm sure this can be done
programmatically, just not sure how to do it in VBA or where it should go.


This is a tricky problem. I think you should create another
query to do the calculation:

TotalQuery:
SELECT Sum(V) As Total
FROM (SELECT Max([the value field]) As V
FROM yourquery
GROUP BY [the other field])

Then the form can use a text box expression to retrieve and
display the total:
=DLookup("Total", "TotalQuery")

Don't forget to requery the text box whenever you add or
modify a record. Use the form's AfterUpdate event
procedure:
Me.thetextbox.Requery