Fields in Query2 TestID from Table1 Select from Table 1
Run Query Can’t update “Select” field
So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
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.
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.
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!!
Currently, I'm taking my unread emails, storing them in a table then sending any Excel files to a specified folder I've modified some code I found while surfing and it works great but I'd really like to send the attachment name and file path with the email recordset. It's been a long day so maybe it's super simple. Below is my code. Office 2010 Windows 8.1
I want to be able to store 2 identical fields on the same table in the database. The copy will start off being the same as that input in the original field. 90% of the time the 2 fields will always be identical but the copy may be changed at a later stage. I looked into using the copy as a calculated field but found out these can not be updated. Is there another control I can use or do I have to code the population of the 2nd field specifically (in AFTER UPDATE EVENT?) myself. The added complication is that the original and copy must be selected from a combo box as all values are held in a separate table.
Also trying to make the second field = to the first field in the design stage of the table using default value
SO if I put default value = [table].[fieldname] I get an error stating could not find field 'table].[fieldname', and yes the opening and closing square brackets are missing from the error.
I searched without success for a solution to show small pictures (icons) in a table field to show up in a continous form.
Purpose is: a communication log table has comm codes such as - mail out - mail in - tel call in - tel call out
the comm log table has the fields CustomerID, datetime, CommCode
The CommCode table hast the fields CommID, CommCode, Icon
The form (subfrm) should show in continous form mode to each customer..Datetime, (commCode), and to visualize the Commcode the small image (icon) presented by an envelope with an arrow right, an envelope with an arrow left, etc.
I cannot find a solution for storing those little images and retrieving them from an OLE-Field.All my other pictures I do not have stored in the tables, but only the image path, but for that I would prefer to store them directly into a table field as they do not blow up the database.
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code: SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next FROM calls WHERE (((calls.firm_id)=[firms].[id])) ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.