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

struggling with a query

P: n/a
I need to produce a report based on a query.

Cost is a calculated field and its value is dependent on another field, in
the query, called Session.

There are 5 different Session codes each generating a different Cost.

What is the simplest way to set the correct Cost, in this calculated field,
when there are so many options for a Session ?

Any help will be genuinely appreciated

Geoff
Jan 9 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Geoff" <gf****@freenetname.co.uk> wrote in message
news:7L******************************@brightview.c om...
I need to produce a report based on a query.

Cost is a calculated field and its value is dependent on another field,
in the query, called Session.

There are 5 different Session codes each generating a different Cost.

What is the simplest way to set the correct Cost, in this calculated
field, when there are so many options for a Session ?

Any help will be genuinely appreciated

Geoff

We don't know much about the tables and fields in your database - so for all
we know the solution might be quite easy or be pretty complex.
It might be that you should have a table listing which session code has
which cost - then your query could join both tables and calculate the cost.
It might be that the cost calculations are very complex and cannot be done
without calling vba functions from your query.
You could let us know further details.

Jan 9 '06 #2

P: n/a
Many thanks for taking the trouble to reply. Yes it was obvious when you
suggested using a extra lookup table and incorporating this into the query -
I am a bit on the slow side really

However I don't understand your comment about calling VBA code from the
query. How do you do that?

Geoff


"Anthony England" <ae******@oops.co.uk> wrote in message
news:dp**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
"Geoff" <gf****@freenetname.co.uk> wrote in message
news:7L******************************@brightview.c om...
I need to produce a report based on a query.

Cost is a calculated field and its value is dependent on another field,
in the query, called Session.

There are 5 different Session codes each generating a different Cost.

What is the simplest way to set the correct Cost, in this calculated
field, when there are so many options for a Session ?

Any help will be genuinely appreciated

Geoff

We don't know much about the tables and fields in your database - so for
all we know the solution might be quite easy or be pretty complex.
It might be that you should have a table listing which session code has
which cost - then your query could join both tables and calculate the
cost. It might be that the cost calculations are very complex and cannot
be done without calling vba functions from your query.
You could let us know further details.

Jan 9 '06 #3

P: n/a
Br
Geoff wrote:
I need to produce a report based on a query.

Cost is a calculated field and its value is dependent on another
field, in the query, called Session.

There are 5 different Session codes each generating a different Cost.

What is the simplest way to set the correct Cost, in this calculated
field, when there are so many options for a Session ?

Any help will be genuinely appreciated

Geoff


Why not create a simple list query and do the summing etc on the report?
(ie. add an entry under Sorting and Grouping for Session, set the option
to have a Session footer, add a calculated field to sum the values).
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Jan 9 '06 #4

P: n/a
"Geoff" <gf****@freenetname.co.uk> wrote in message
news:n5********************@brightview.com...
Many thanks for taking the trouble to reply. Yes it was obvious when you
suggested using a extra lookup table and incorporating this into the
query - I am a bit on the slow side really

However I don't understand your comment about calling VBA code from the
query. How do you do that?

Geoff


"Anthony England" <ae******@oops.co.uk> wrote in message
news:dp**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
"Geoff" <gf****@freenetname.co.uk> wrote in message
news:7L******************************@brightview.c om...
I need to produce a report based on a query.

Cost is a calculated field and its value is dependent on another field,
in the query, called Session.

There are 5 different Session codes each generating a different Cost.

What is the simplest way to set the correct Cost, in this calculated
field, when there are so many options for a Session ?

Any help will be genuinely appreciated

Geoff

We don't know much about the tables and fields in your database - so for
all we know the solution might be quite easy or be pretty complex.
It might be that you should have a table listing which session code has
which cost - then your query could join both tables and calculate the
cost. It might be that the cost calculations are very complex and cannot
be done without calling vba functions from your query.
You could let us know further details.


There are some calculation which cannot be done with simple lookup tables.
Then you could create a new code module and write a function like this:

Silly example:

Public Function GetCost(strSessionCode As String) As Currency

Dim curCost As Currency

If strSessionCode = "CodeOne" Then
curCost = 1.42
Else
curCost = 1.94
End If

GetCost = curCost

End Function
Once the module is saved, you can call it directly from your query, eg:

SELECT MyTable.ID, MyTable.Session,
GetCost([Session]) AS Cost FROM MyTable
Jan 9 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.