 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006Transfer query result to a table field?
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? >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] 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... > >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 [quoted text clipped - 28 lines] > > John W. Vinson[MVP] >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] > >Thanks for your reply, John. > > [quoted text clipped - 10 lines] > 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. > >(It probably couldn't be, given the political wrangling that went into > >setting the individual numbers - it's a non-profit group.) And, perhaps [quoted text clipped - 9 lines] > 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... ;-) > > 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 [quoted text clipped - 38 lines] > 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... > Should they be billed at the rates current in July, as on the original > invoice? Or at the new rates? [quoted text clipped - 7 lines] > > John W. Vinson[MVP] >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 [quoted text clipped - 8 lines] >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] > >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 [quoted text clipped - 20 lines] > > 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. > > >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 [quoted text clipped - 32 lines] > 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. *** > Joined one-to-many to ScheduleRetreatFees are 2 pricing factor tables: > RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it - [quoted text clipped - 31 lines] > not when I'm passing those values from the form, i.e, it's not displaying in > the form. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.