Transfer query result to a table field?

[katsup]

There's something fundamental I'm not understanding about how Access puts it
all together. I'm using Access 2003.

I'm designing a reservation/invoicing database for running a yoga retreat.
Fees are based on a combination of registrant-type (adult, family,student
etc.), duration of stay, and accomodation type. I've placed each pricing
factor in its own table. There are over 100 possible prices produced by the
combinations.

I've gotten all these tables into Access, including one for accumulating the
registrants' invoice charges (which include snack bar tabs, private
instruction etc. in addition to the fees). I've even gotten a parameter
query built that successfully returns the correct fee whenever the various
pricing parameter values are entered, so I've probably gotten the joins
between tables set up properly.

But how do I get that correct fee value returned by the parameter query into
the table that's accumulating invoice charges by participant, more
specifically, into the field in that table where the charge is recorded?

Should I have delayed defining the invoice charge table, i.e., built the
query first, then based a form on the query, then built a table based on the
form?

Or can AutoLookup somehow grab the query result and feed it into the invoice
charge table I've already built?

 

[John Vinson]

But how do I get that correct fee value returned by the parameter query into
the table that's accumulating invoice charges by participant, more
specifically, into the field in that table where the charge is recorded?

Generally, you don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

There is a possible (in this case even probable) exception to this
rule, though: if you want to invoice the cost as of the time that the
invoice was generated, and have that cost remain static even if the
underlying components change in the future, you need to "push" the
value into the table. This requires a little bit of VBA code. You
should - I'd say MUST - do your data entry on a Form, not a query
datasheet; in the Form's BeforeUpdate event you can set the value of a
bound control to the value of a calculated one:

Me!txtTotalCost = Me!txtCalcCost

for example.

John W. Vinson[MVP]

 

[katsup]

Thanks for your reply, John.

I understand from your explanation why I shouldn't be using the derived fee
value, but not the how of the right way to do it.

The fee value is "looked up" through cross-referencing several different
tables; so it's not a "calculated" value, as far as I understand that term.
(It probably couldn't be, given the political wrangling that went into
setting the individual numbers - it's a non-profit group.) And, perhaps
unfortunately, I'm not at the form or report stage of development yet (except
for having entered those 100+ prices into the fee schedule Table through a
form); thus your observation about how I'm doing it now with the control
source of a Form or Report mystifies me.

I do envision creating a data entry form that will supply data to the
table accmulating invoice charges as they occur at the time of the retreat; I
was using a parameter query to return the fee value only because I thought
that would be a component underlying the eventual data entry form, i.e., the
user will key in registrant ID, registrant type, and duration through a
dropdown list box, and the parameter query would look up and supply the fee.

I know this ends up with the fee being stored in 2 different places, the
"one" place in the fee schedule table and the "many" in the invoice charges
table, but disk space and access times won't be issues given the moderate
number of transactions compared to the power of my laptop.

It occurs to me that may DLookup is the answer here, or an append query.
I'm not a VBA adept (not even a tyro) but could probably manage a DLookup
stmt somewhere. Can I put one right in the invoice charges table field? Or
should it go in the data entry form (which I'll now create) supplying data to
that table?

I don't want to have the cost remain static even if the underlying
components change.

I'm fairly new to discussion groups, so please forgive me if I omit some
rule of etiquette here...

 

[John Vinson]

Thanks for your reply, John.

I understand from your explanation why I shouldn't be using the derived fee
value, but not the how of the right way to do it.

Well, there may be some debate about "the right way".

The fee value is "looked up" through cross-referencing several different
tables; so it's not a "calculated" value, as far as I understand that term.

Then it's not calculated, but it *IS* "derived". It exists in some
other table, and can be looked up on the basis of values in your
current table. Without knowing the specifics I don't even know if that
is an accurate statement though!

(It probably couldn't be, given the political wrangling that went into
setting the individual numbers - it's a non-profit group.) And, perhaps
unfortunately, I'm not at the form or report stage of development yet (except
for having entered those 100+ prices into the fee schedule Table through a
form); thus your observation about how I'm doing it now with the control
source of a Form or Report mystifies me.

Before you make the database available to your nonprofit
organization's users, I'd say that you MUST - *no option* - get to the
"form stage". Table or query datasheets are of VERY limited utility
and generally should not be exposed to users, even computer-savvy
users (I'd even say *particularly* not to computer-savvy users, who
are more likely to mess things up!)

You can do calculations very easily on a Form, by setting the "Control
Source" property of a form textbox to an expression.

I do envision creating a data entry form that will supply data to the
table accmulating invoice charges as they occur at the time of the retreat; I
was using a parameter query to return the fee value only because I thought
that would be a component underlying the eventual data entry form, i.e., the
user will key in registrant ID, registrant type, and duration through a
dropdown list box, and the parameter query would look up and supply the fee.

It very well may be, but again - you understand your fee algorithm and
table structures, and I do not. However, opening the query datasheet
and viewing it will NOT be part of the process.

I know this ends up with the fee being stored in 2 different places, the
"one" place in the fee schedule table and the "many" in the invoice charges
table, but disk space and access times won't be issues given the moderate
number of transactions compared to the power of my laptop.

I'm far less worried about the disk space and access times than I am
about the fact that if you store the "same" fee in two different
tables, then it might not be the "same" in the two places - you could
have $150 in the fee schedule table and $3 in the invoice table
(particularly if you make the field available for editing, say by
displaying it on a table datasheet).

It occurs to me that may DLookup is the answer here, or an append query.
I'm not a VBA adept (not even a tyro) but could probably manage a DLookup
stmt somewhere. Can I put one right in the invoice charges table field? Or
should it go in the data entry form (which I'll now create) supplying data to
that table?

Again... *I don't know your structure* so I can't say exactly how this
would be done. DLookUp is inefficient and should generally be avoided
but might be appropriate in some cases; a Combo Box bound to some
concealed ID field but displaying the amount might be better; there
may well be other solutions.

I don't want to have the cost remain static even if the underlying
components change.

Well... think about that. Suppose you send an Invoice on July 12. In a
contentious meeting in September, the Board votes to change the fee
schedule. In October the member who was billed in July apologetically
says they lost the bill and requests a new copy.

Should they be billed at the rates current in July, as on the original
invoice? Or at the new rates?

I'm fairly new to discussion groups, so please forgive me if I omit some
rule of etiquette here...

You're doing fine. Just remember we're all unpaid volunteers and don't
be perturbed if there are occasional delays or if you sometimes need
to repost a question.

John W. Vinson[MVP]

 

[katsup]

Well, there may be some debate about "the right way".


Then it's not calculated, but it *IS* "derived". It exists in some
other table, and can be looked up on the basis of values in your
current table. Without knowing the specifics I don't even know if that
is an accurate statement though!

Yes, that's accurate. It can be looked up on the basis of values in the
current, i.e. invoice charges table, by using those values as indices to
drill down through the pricing factor tables to the fee schedule table.

Before you make the database available to your nonprofit
organization's users, I'd say that you MUST - *no option* - get to the
"form stage". Table or query datasheets are of VERY limited utility
and generally should not be exposed to users, even computer-savvy
users (I'd even say *particularly* not to computer-savvy users, who
are more likely to mess things up!)

Gotcha. Actually, it was never my intention to let the users get at queries
or datasheets. I was trying to push the fee value into the invoice charges
datasheet only in the present development stage, to see how it was done
before I put the user interface together. I really am that new to Access.

I guess I thought of the query as a subroutine call, which would pass back
a value that would then have to be "put" somewhere, in a table, rather than
just displayed on the screen. (Old assembly language habits die hard.) But
now I know that if I want to get along with Access I shouldn't be pushing
values into new datasheets, when they can be derived, looked up, whenever I
want them.

You can do calculations very easily on a Form, by setting the "Control
Source" property of a form textbox to an expression.

OK. So, build the form for invoice charges data entry, then pop a textbox
onto it, and set that box's "Control Source" to an expression, which, as I
understand it, could be the query expression I've already got working to look
up fee values; or an expression built using DLookup, or Choose, or some other
of the functions I'm just discovering.

Or it occurs to me that I could squeeze all the pricing factors tables
together with the fee schedule table into one table, eliminate the separate
pricing factors tables, and then use your suggestion of a Combo box to
display the merged monster , getting the user to pick the correct fee from it
thereby. Nested flyouts, the way the Windows Start menu works, would be
nice, but I'll see if I can get the monster Combo box working first... ;-)

It very well may be, but again - you understand your fee algorithm and
table structures, and I do not. However, opening the query datasheet
and viewing it will NOT be part of the process.


I'm far less worried about the disk space and access times than I am
about the fact that if you store the "same" fee in two different
tables, then it might not be the "same" in the two places - you could
have $150 in the fee schedule table and $3 in the invoice table
(particularly if you make the field available for editing, say by
displaying it on a table datasheet).


Again... *I don't know your structure* so I can't say exactly how this
would be done. DLookUp is inefficient and should generally be avoided
but might be appropriate in some cases; a Combo Box bound to some
concealed ID field but displaying the amount might be better; there
may well be other solutions.


Well... think about that. Suppose you send an Invoice on July 12. In a
contentious meeting in September, the Board votes to change the fee
schedule. In October the member who was billed in July apologetically
says they lost the bill and requests a new copy.

Yes, the world does operate this way. We've been running retreats for 29
years now however, and have gotten expert at collaring our retreatants for
payment before feeding them breakfast on the last morning. Only this year,
we'll actually have printed invoices to hand them! (Touch wood..)

Thanks for your help, MVP John. Thanks to it I believe I'm on my way now...

 

[John Vinson]

OK. So, build the form for invoice charges data entry, then pop a textbox
onto it, and set that box's "Control Source" to an expression, which, as I
understand it, could be the query expression I've already got working to look
up fee values; or an expression built using DLookup, or Choose, or some other
of the functions I'm just discovering.

Or it occurs to me that I could squeeze all the pricing factors tables
together with the fee schedule table into one table, eliminate the separate
pricing factors tables, and then use your suggestion of a Combo box to
display the merged monster , getting the user to pick the correct fee from it
thereby. Nested flyouts, the way the Windows Start menu works, would be
nice, but I'll see if I can get the monster Combo box working first... ;-)

Well... a combo box, per se, is a fairly limited instrument. I still
have no idea what these multiple "pricing factors tables" might be or
how they work so I'm not certain, but IF you can construct a Query
using values entered into your existing main table (or tables) as
criteria, then you can base a Combo on that query - or, if you can
come up with the one unique price based on that information, simply
have that value displayed in a textbox. It sounds like your computer
logic can only narrow down the prices to a list which requires a human
to choose, though, right?

John W. Vinson[MVP]

 

[katsup]

Well... a combo box, per se, is a fairly limited instrument. I still
have no idea what these multiple "pricing factors tables" might be or
how they work so I'm not certain, but IF you can construct a Query
using values entered into your existing main table (or tables) as
criteria, then you can base a Combo on that query - or, if you can
come up with the one unique price based on that information, simply
have that value displayed in a textbox. It sounds like your computer
logic can only narrow down the prices to a list which requires a human
to choose, though, right?

John W. Vinson[MVP]

Hi John,

Wow, you've got staying power, to keep sticking with me on this.

I've just failed to get my query to display the looked-up Retreat Fee in the
newly added text box, so I'll describe one section of the db design; if I can
get this one section to work I believe I can get the rest to work.

A table called ScheduleRetreatFees holds the 100 individual prices. It has
four fields in it - FeeID, RegistrantTypeID, DurationID, and
RetreatFee(currency data type) - and, of course, 100 records.

Joined one-to-many to ScheduleRetreatFees are 2 pricing factor tables:
RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
RegistrantTypeID, and RegistrantType - and 24 records, with values like
"Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
"TwoAdult2kids1teen", etc. DurationTypes also has 2 fields in it -
DurationTypeID and DurationType - with values like "Full Retreat",
"HalfDay", "EveningProgram", etc., 6 records in all.

These 2 pricing factor tables are also joined one-to-many to the table which
will accumulated the fee charges for everyone who attends, called
InvoiceChargeRetreatFee. That gem has 4 fields in it - ICRetreatFeeID,
MainRegistrantID (a dropdown list box of peoples' names), RegistrantTypeID
(dropdown list), and DurationTypeID (ditto) - and no records as yet, except
my test data. One main registrant pays for himself/his family, but can also
opt to sponsor another individual; hence there could be more than 1 retreat
fee charge attributed to the same registrant.

I've based a data entry form on InvoiceChargeRetreatFee for the registrar to
use as people arrive at the desk to sign in. That's the form now sporting
the newly added text box which I'm hoping will display the correct RetreatFee
after the registrar has chosen the registrant's name, type, and duration (so
they don't have to look it up on the hardcopy schedule). And because the
RetreatFee value can be derived at any time ;-) the record generated at
sign-in will eventually be used to make up a printed invoice for that
registrant.

Does that give you a better idea? Should I be using DLookup in the text box
control source, or directing it to a query, with the query's criteria set to
values from the open form, or what?

If the query is the best option, can you give me a clue as to what the SQL
might look like? I can get my query to look up the RetreatFee correctly when
I type in the Duration and RegistrantType values directly into criteria, but
not when I'm passing those values from the form, i.e, it's not displaying in
the form.

 

[katsup]

Hi John,

Wow, you've got staying power, to keep sticking with me on this.

I've just failed to get my query to display the looked-up Retreat Fee in the
newly added text box, so I'll describe one section of the db design; if I can
get this one section to work I believe I can get the rest to work.

A table called ScheduleRetreatFees holds the 100 individual prices. It has
four fields in it - FeeID, RegistrantTypeID, DurationID, and
RetreatFee(currency data type) - and, of course, 100 records.

***Oops, the above paragraph should have read:

A table called ScheduleRetreatFees holds the 100 individual prices. It has
four fields in it - FeeID, RegistrantTypeID, DurationTypeID, and
RetreatFee(currency data type) - and, of course, 100 records. Perhaps I
should add that RegistrantTypeID and DurationTypeID are in the form of combo
boxes in this table.

***