table contents to string

  • Thread starter Thread starter Jeroen Verdrengh
  • Start date Start date
J

Jeroen Verdrengh

Hi,

Is there some way to cycle trhough all records in a table?

I need to do sth. like "for(all records in table) show VAT percentage and
Total". A record looks like [VATpercentage; Total].

greets,

Jeroen
 
Jeroen

Use a query to do "set" calculations, rather than "cycle through all
records".

If you have a table with VATPercentage & Total fields, a simple query will
show all those. By the way, storing "calculated" values is unnecessary and
will force you to add synchronization code, in case any underlying values
used in the calculation change.
 
Jeff,

Thanks for your reply. The table I'm talking about actually is a query.
What I'm trying to do is showing the results of this query in a report. The
problem is that the report is based on another query. It is possible to
derive the results of the first query from the latter one within the report,
but due to grouping issues I don't know how. In my previous post "Report -
grouping - sum" from 21/2 I've already mentioned this problem. Duane Hookom
asked me in reply to my post to clarify some things so here I go:

record REPORTRECORD
date: Date
consultationID: Long Integer
VATpercentageOfMedication: percentage
priceOfMedication: someOtherTypes
end

The report is showing a query producing records of type REPORTRECORD
(simplified). 1 record represents the price a client (veterinary context)
has to pay for medication used in a consultation. There are different VAT
tarives for medication so 1 consultation (consultationID) can have multiple
records each showing the price for the medication under that VAT tarive.
The report is grouped firstly on date, then on consultationID and finally on
VATpercentageOfMedication.

As I've vaguely indicated in my previous post, I want to show the grand
totals of priceOfMedication PER VATpercentageOfMedication at the footer of
the report, but I don't know how to do it with this grouping. I doesn't
want te report to be sorted firstly on VATpercentageOfMedication because all
data of 1 consultation must be shown at the same place. If tehre is a
direct solution to this problem I'd like to hear it, but I haven't found it
yet so I created a query summing all the priceOfMedication fields grouped by
VATpercentageOfMedication (but I don't know how to show it in the report).

Tia,

Jeroen
 
Jeroen

I'm sorry, but I'm more confused than before.

For example, I don't understand how you plan to show one query's result in a
report that is based on another query? Or why?

Perhaps if you backed away from the HOW and described a bit more of the
WHAT?

Could you create a simplified mockup of what you want the report to show?
 
Ok,

Forget all I've said before. Here is an exemple:

records in query shown by report:

Consultation_id: Long Integer - DateOfConsultation: Date -
VATpercentageOfMedication: Percentage - priceOfMedication: currency
************************-**********************-****************************
*******-************************
1--------------------------------12/05/1997---------------------21%---------
-------------------------------84?---------------------------
1--------------------------------12/05/1997---------------------21%---------
-------------------------------52?---------------------------
1--------------------------------12/05/1997---------------------30%---------
-------------------------------15?---------------------------
2--------------------------------12/05/1997---------------------5%----------
-------------------------------7?-----------------------------
3--------------------------------13/05/1997---------------------5%----------
-------------------------------24?----------------------------

This is what the report should show:

Date:12/05/1997
Consultation ID: 1
VAT percentage: 21%
sum of medicationPrices: 156?
VAT percentage: 30%
sum of medicationPrices: 15?
Consultation ID: 2
VAT percentage: 5%
sum of medicationPrices: 7?

Date:12/05/1997
Consultation ID: 3
VAT percentage: 5%
sum of medicationPrices: 24?

Grand totals PER VAT percentage:
VAT percentage: 21%
total sum of medicationPrices: 156?
VAT percentage: 30%
total sum of medicationPrices: 15?
VAT percentage: 5%
sum of medicationPrices: 31?


I managed to show everything BUT the grand totals: due to the fact that I
want to show all the information of 1 consulatation at the same place I
cannot make VAT percenateg the first grouping field. But if I don't do
that, I don't know how I can calculate the grand totals. To solve this I
made another query calculating the grand totals but I don't know either how
I can display that query in the report.

Hope this is clarifying (again ;)). But you convinced me an example is
better to understand than my (vague) try-to-explanation :/

greets,

Jeroen
 
Jeroen

OK, I'll try again. If you are able to get the "detail" rows working, then
it sounds like the only issue is how to get grand totals into your report.

In the footer (report footer) of your report, add a new control, and set its
Control Source to something like (your syntax may vary):

=Sum([YourReportControl])

This will add up all values of [YourReportControl] in the Detail section.
Do the same for each field for which you wish a grand total.
 
The problem is that I want to calculate the totals of the medicationprices
per VAT tarive. If I just do sum(field), then it just adds up all the
prices without 'seperating' on VAT percentage. Btw the details section is
empty, the calculated fields "VAT percentage" and "total sum of
medicationPrices" are located in the the VAT percentage grouping-header.
 
from somewhere on the WWW:

------------------
You can add a text box to a group or report footer that uses syntax like:
=Abs(Sum( (Job=1 or Job=2)*Sales) )
This will sum Sales for only Job 1 or 2.

--
Duane Hookom
MS Access MVP
---------------------------

Jeroen Verdrengh said:
The problem is that I want to calculate the totals of the medicationprices
per VAT tarive. If I just do sum(field), then it just adds up all the
prices without 'seperating' on VAT percentage. Btw the details section is
empty, the calculated fields "VAT percentage" and "total sum of
medicationPrices" are located in the the VAT percentage grouping-header.


Jeff Boyce said:
Jeroen

OK, I'll try again. If you are able to get the "detail" rows working, then
it sounds like the only issue is how to get grand totals into your report.

In the footer (report footer) of your report, add a new control, and set its
Control Source to something like (your syntax may vary):

=Sum([YourReportControl])

This will add up all values of [YourReportControl] in the Detail section.
Do the same for each field for which you wish a grand total.
--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Back
Top