Storing A Calculation As A String In A Table And Executing It In A Query.
Jun 11, 2007
N.B. This is not your usual 'Bad practice to Store calculated values in a table' post
I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.
So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.
Hi, I try to execute an update command i.e.: UPDATE Results set Data = 'Heiner's key' where ValidationNumber = 1 and TestNumber = 2 and Line = 'E1'. Access does not let me do this because the “'” in Heiner's terminates the update string prematurely. Does anyone have a suggestion how to do this? Thank you.
I am trying to run a query from an ASP page, which also uses other queries. I get the following error message: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Expr1' as part of an aggregate function, this is in qrySessionsEverything.
The query name is qrySessionsEverything. It executes the line:
SELECT * FROM qrySessionsEverything
The queries are below:
qrySessionsEverything SELECT tblSessions.SessionID AS Expr1, tblSessions.CourseID AS Expr2, tblCourses.CourseName AS Expr3, tblSessions.SessionDate AS Expr4, tblSessions.StartTime AS Expr5, tblSessions.EndTime AS Expr6, tblVenues.VenueID AS Expr7, tblVenues.VenueName AS Expr8, tblVenues.Capacity AS Expr9, tblVenues.Capacity-[bytAttendees] AS bytAvailablePlaces, qrySessionsAccepted.bytAttendees AS Expr10, tblVenues.Link AS Expr11, qrySessionsPending.Pending AS Expr12, [TrainerFirstName] & " " & [TrainerSurname] AS strTrainer, tblSessions.TrainerID AS Expr13 FROM tblTrainers, tblCourses, qrySessionsAccepted, qrySessionsPending, tblVenues, tblSessions GROUP BY tblSessions.SessionID, tblSessions.CourseID, tblCourses.CourseName, tblSessions.SessionDate, tblSessions.StartTime, tblSessions.EndTime, tblVenues.VenueID, tblVenues.VenueName, tblVenues.Capacity, qrySessionsAccepted.bytAttendees, tblVenues.Link, qrySessionsPending.Pending, [TrainerFirstName] & " " & [TrainerSurname], tblSessions.TrainerID;
qrySessionsAccepted SELECT tblSessions.SessionID AS Expr1, tblSessions.CourseID AS Expr2, tblSessions.SessionDate AS Expr3, tblSessions.StartTime AS Expr4, tblSessions.EndTime AS Expr5, tblSessions.VenueID AS Expr6, tblVenues.VenueName AS Expr7, tblVenues.Capacity AS Expr8, [Capacity]-[bytAttendees] AS bytAvailablePlaces, Count(qryDelegatesAccepted.DelegateID) AS bytAttendees, tblVenues.Link AS Expr9 FROM tblVenues, tblSessions, qryDelegatesAccepted GROUP BY tblSessions.SessionID, tblSessions.CourseID, tblSessions.SessionDate, tblSessions.StartTime, tblSessions.EndTime, tblSessions.VenueID, tblVenues.VenueName, tblVenues.Capacity, tblVenues.Link ORDER BY tblSessions.SessionDate, tblSessions.StartTime;
qrySessionsPending SELECT tblSessions.SessionID AS Expr1, tblSessions.CourseID AS Expr2, tblSessions.SessionDate AS Expr3, tblSessions.StartTime AS Expr4, tblSessions.EndTime AS Expr5, tblSessions.VenueID AS Expr6, tblVenues.VenueName AS Expr7, tblVenues.Capacity AS Expr8, tblVenues.Link AS Expr9, Count(qryDelegatesPending.SessionID) AS bytPending FROM tblVenues, tblSessions, qryDelegatesPending GROUP BY tblSessions.SessionID, tblSessions.CourseID, tblSessions.SessionDate, tblSessions.StartTime, tblSessions.EndTime, tblSessions.VenueID, tblVenues.VenueName, tblVenues.Capacity, tblVenues.Link ORDER BY tblSessions.SessionDate, tblSessions.StartTime;
qryDelegatesAccepted SELECT tblDelegates.DelID AS Expr1, tblDelegates.SessionID AS Expr2, tblDelegates.DelTitle AS Expr3, tblDelegates.DelFirstName AS Expr4, tblDelegates.DelSurname AS Expr5, tblDelegates.DelHospital AS Expr6, tblDelegates.DelDepartment AS Expr7, tblDelegates.DelPhone AS Expr8, tblDelegates.DelBleeper AS Expr9, tblDelegates.DelEmail AS Expr10, tblDelegates.DateSubmitted AS Expr11, tblDelegates.Accepted AS Expr12, tblDelegates.Rejected AS Expr13 FROM tblDelegates GROUP BY tblDelegates.DelegateID, tblDelegates.SessionID, tblDelegates.DelTitle, tblDelegates.DelFirstName, tblDelegates.DelSurname, tblDelegates.DelHospitall, tblDelegates.DelDepartment, tblDelegates.DelPhone, tblDelegates.DelBleeper, tblDelegates.DelEmail, tblDelegates.DateSubmitted, tblDelegates.Accepted, tblDelegates.Rejected HAVING (((tblDelegates.Accepted)=True) And ((tblDelegates.Rejected)<>True));
qryDelegatesPending SELECT tblDelegates.DelegateID AS Expr1, tblDelegates.SessionID AS Expr2, tblDelegates.DelTitle AS Expr3, tblDelegates.DelFirstName AS Expr4, tblDelegates.DelSurname AS Expr5, tblDelegates.DelHospitall AS Expr6, tblDelegates.DelDepartment AS Expr7, tblDelegates.DelPhone AS Expr8, tblDelegates.DelBleeper AS Expr9, tblDelegates.DelEmail AS Expr10, tblDelegates.DateSubmitted AS Expr11, tblDelegates.Accepted AS Expr12, tblDelegates.Rejected AS Expr13 FROM tblDelegates GROUP BY tblDelegates.DelegateID, tblDelegates.SessionID, tblDelegates.DelTitle, tblDelegates.DelFirstName, tblDelegates.DelSurname, tblDelegates.DelHospital, tblDelegates.DelDepartment, tblDelegates.DelPhone, tblDelegates.strDelegateBleep, tblDelegates.DelEmail, tblDelegates.DateSubmitted, tblDelegates.Accepted, tblDelegates.Rejected HAVING (((tblDelegates.Accepted)<>True) And ((tblDelegates.Rejected)<>True));
Hi, I am a beginner in access. I am trying to execute the below query through VBA on a click event on a Command Button in a Form. I am taking input parameter from Form, based on which I want the query result( dynamic Query Parameter). I getting the attached error message
Please help me out in executing this query in VBA. An example would be of great help. Thanks in advance..
I am looking to check, whether i would be able to "add an Message box with a "Message : "Executes all queries all is well.mdb before executing this query ?" , if yes, continue executing the query, else, Exit out of the database?
I am trying to execute query every midnight at 00:05 and append data into a table. This query reads the data from a linked table and appends the records into the local table.
Access doesn't triggers so I have no clue which way to proceed.
Can anybody help me creating a logic to execute query at midnight?
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount]) From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table? Thank you (sorry if it is easy - but i dont have a clue!)
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.
I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "ç …5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
How do I create a table relationship where a secondary reltated table stores past dates, status code and customer ID. For example: I want to keep track of the past dates that an order was revised and why it was revised. So, if I have a customer who was due for installation on 10/21/04 and status is PEND for pending. THen on 10/18/94 I change the date of PEND to 10/25/04. I want to keep track of the times it changed and the reason why. HOw can I make the program automatically create a record in the revision table storing the past code, reason and date using customer ID as key and teh revision table would allow multiple records for each customer ID. DId I make sense?
Firstly this is my first post so please forgive me if i do not conform to the usual way of asking a question!
I have an access database set up and it is working well, so far!
What I wish to do is the following, I am creating this for a cycling club to store race results...
I wish to store the time that they completed the stage in, how do i do this using the date/time field option when all it wants to do is store a complete time.
for example i wish to store 1 hour 12 minutes 15 seconds in the table as 1:12:15 for stage 1.
Plus they may have received a penalty, so in another field I would like to say store a 10 second penalty...
anyway you get the idea.... please could somebody inform me on the best way to do this, it has to be stored as a time so I can add all the stages up later and create a report!
I have a database which picks up information from my accounts package. It has the following fields
ID,Name,Date,Ref1&Ref2
I need to extract the last ID number and store it in a table or somewhere and then extract the above information after that number into a csv file in the order above. It is used for my invoices and therefore when I do an import from my accounts package it updates all the transactions.
An example would be I say put 5 invoices on with a ID nr's of 1, 2, 3, 4 & 5 and output to a csv file. When I run the query/macro? it looks at the table with the last ID nr and starts from 6 and so on
I just need the ones I have done today. It has to be the ID number as each invoice is put on it gives it a sequential number, the dates could be different for each invoice.
I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.
I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.
For example:
Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).
So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.
Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?
I know it breaks all the rules to store a calculated field on a table, but I've painted myself into a corner with the way I built this particular database, not good, I have repeating groups and basically the whole database is built this way. I need one form to store a calculated value on a table to solve a problem on a report that encapsulates the total of all the repeating groups. Before you start the lecture, I know I messed up, but this is way to far down the road to start over. Any help would be greatly appreciated.
I have two tables one that lists accounting codes by county and one that a user will have an entry by county which needs to store the applicable acct code from the other table. I want the user to be limited to the use the accounting codes assigned to their county only when they enter data on the second table.
Table examples:
County Code Funds Spent Accounting Code Date Entered Aitkin 123 $20.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013 Aitkin 112 $23.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013 Becker 145 $12.00
Give the user the choice to select only the Aitkin Acct Codes from the Accounting Code table.
12/4/2013 County Accounting Code Aitkin 4872727001000000 Aitkin 4842727001000000 Becker 4872727005000000
I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.
Hello everyone. I am wanting to store the date the record was entered into the table. But I don't want the users to have to type this in; I thought that Access could grab the date from the system date.
I am new to this and I looked at the Date function but I am thinking it will just always update to the current system date.
Thank you very much if anyone could point me in the right direction.
I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?
I know that it isn't usually best practise to store calculated fields in a table, but in my situation it is necessary, and I haven't really found a good answer for this after searching these and other forums.
Currently my database has one "Name" field (I didn't make it like this) which might look like this "Jonathan (John) Smith" Now I also have reason to grab the name like this "Smith, John" for other purposes.
I've set up a form for inputting new people with separate fields for firstname, preferredname, and lastname. I have the calculated fields:
(Later I will add in all the iif's for if there is no preferred name)
Upon finishing the form, the user hits Submit, and I would like to store the concatenated values into the table in their appropriate fields.
I can do this with a SQL INSERT INTO statement but I'd have to add all 25 or so fields from the form, and injecting user input directly into a SQL statement is not best practice either.
I have a set of maybe 5 template word docs which I populate with info form our database using bookmarks , the word docs are currently sat in a shared network drive.
I have started wonder if it would be better to store them in a table and call them from there to keep things together.
I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!
