Table For Storing Updates
Nov 21, 2004
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?
View Replies
ADVERTISEMENT
Mar 31, 2005
Hi all
I have a database that stores customer records and their associated insurance details. I need to be able to track any changes made to that record within a set time period ie if details are changed fine it is reported next time report is run. But if details are changed more than once in between reports then first detail change is lost.
I would like to work out a way that changes can be written to a ??seperate version of the table?? and stored in there to be reported seperately and not just the final change as is the snapshot nature of a report? Is this possible? Could I possible make another version of the main table appended onto it? I feel I 'know' what I need to do but not how to even begin it! Any help would be appreciated.
DDiver
View 1 Replies
View Related
Apr 7, 2006
I'm new at access and have added some expressions to a couple of my forms but the table is not being updated with the results. help!
View 5 Replies
View Related
Jun 3, 2013
In the attached DB I have a form that has a combo box which enables you to select an employee and all the employee info is then pulled based on the selection. I want to make changes to the info in the form and have the changes update the correct employee on the table. Currently the from will not let me make changes to the info and the table seems to add new lines rather than updating the corresponding line.
View 8 Replies
View Related
Sep 20, 2006
I've been working on this problme for several weeks, thinking about it for the last month or so, and am at a loss of what to try next. I'm working on a database for my office that tracks all of the permits we have issued. Every year our permit fees increase by the CPI (consumer price index) and the permit fee entry in the table needs to be updated (75 to 80 entries).
Rather than typing them in every year (too many chances for errors) I'd like to be able to update our fee schedule table (8 entries, look up table) and have that table update all of our fee entries in the permit table.
Any ideas of how to go about this? Thank you for your help.
Scott
View 1 Replies
View Related
Jan 6, 2015
Here is the set up. I have a primary school database.
There is a main form that shows the students name and class.
The main form contains tabs for each subject (maths, english etc.). Each tab has a subform that is supposed to allow me to enter a comment about the student's progress.
My tables are set up properly (as far as I can tell).
The tables (simplified)
tbl_Students (has StudentID, StudentName)
tbl_SubjectList (has SubjectID, SubjectName)
tbl_Comments (has CommentID, SubjectID (foreign key), StudentID (foreign key), Comment)
The forms
The Master and Child (main form and subform) are linked using StudentID.
I use a query for fields in the Master form. Each subform has its own query that shows results for a particular subject.
The problem
My subform allows me to edit an existing comment fine (if I enter dummy data directly in a table).
BUT, if there isn't an existing comment, and I attempt to enter one, I get an error - "YOU CANNOT CHANGE A RECORD BECAUSE A RELATED RECORD IS REQUIRED IN TABLE tbl_Subjects."
What I have tried
I understand this error means I am violating referential integrity rules. But I can't see why. The tbl_Subjects is populated with 10 subjects and I am only trying to put a comment against an existing SubjectID.
I've checked that my Form Master / Subform Child is ok. I experimented with having StudentID _and_ Subject_ID to link the master and subform to see if that worked. It only made things worse because my subforms wouldn't display correctly.
View 5 Replies
View Related
Aug 7, 2012
I have a split database, a front for data entry and of course the back end with all the tables. I have created several Append queries to move records from one table to another (from Applicants to Students, once they are Accepted). The queries work - when I go into the tables and look, there's the data right where I told it to go. But when I open the Students input form (which draws from the destination table), the new data doesn't show up. I double-checked the destination table, and yes, the appended data is all there.
I tried running the simply query that populates the input form, and it doesn't show up in the simple query, either. I've tried Refreshing everything numerous times, tried updating the Linked Table Manager numerous times, opened and closed the program, and no dice - the data is in the table but never shows up in the simple query that populates the data input form. There's no filters on the simple query, so everything in the table should show up - but doesn't.
View 5 Replies
View Related
Aug 13, 2007
ok i have this query.
Code:UPDATE [Claims Header] SET [Claims Header].status = 'I'WHERE ((([Claims Header].Claim_ID)=[claim id]));WHERE ((([Claims Header].Claim_ID)=[claim id]));
i want to have a button that runs this query but how do i pass over the claimid from my form? also when it's done running the query i want it to run a report.
so i was trying to make a macro, but how do i tell the macro to find the claim id from the form?
View 1 Replies
View Related
Feb 11, 2008
Hi,
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!
Thanks in advance...
View 3 Replies
View Related
Mar 13, 2006
Hi
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 hope I've explained myself:)
Thanks in advance
Cheesey
View 1 Replies
View Related
Nov 30, 2013
I have a table in my DB named TblStock and another named TblStock2..I also have a form in the DB named "frmStock" with the control source being "TblStock" . This form has a sub form "FrmStock2Subform" with control source being "tblStock2". The two forms are lined by "PurchaseID". This form is made to be used in entering products that have been bought for replenishment of stock.The problem I have is that when I enter data in the main form, by the time i click on the subform to start entering data, the table "TblStock" gets already updated.
Now lets assume somebody was about to enter data in the subform and later on changes his mind when the "TblStock" had already been updated, this will cause a waste of useful space because there is no corresponding record in the table "TblStock2". Secondly, if many users are entering data into the DB using that same form, I guest there will be some problems too.What i want is that the form should update both tables when the users validates the records.
View 9 Replies
View Related
Apr 20, 2007
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?
Thanx for any help!
View 2 Replies
View Related
Jan 9, 2007
i have a form with two combo boxes cboMth and cboYr
i also have three text boxes in the same form with computed results: txtA,txtB,txtC.
now how can i store txtA,txtB,txtC with reference to the (Mth and Yr) into a table?
e.g: the table will looks this way (below)
===== ==== ==== ====
Mth/Yr txtA txtB txtC
===== ==== ==== ====
01/2007 580 683 93
:
:
:
06/2012 312 510 80
:
:
how shall i write the codes?
View 1 Replies
View Related
Aug 10, 2007
Hi,
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.
Gary
View 13 Replies
View Related
Oct 16, 2007
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
View 2 Replies
View Related
Dec 4, 2013
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
View 14 Replies
View Related
Nov 17, 2006
Hi,
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.
View 6 Replies
View Related
Jun 27, 2006
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.
View 3 Replies
View Related
Jan 8, 2014
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"?
View 1 Replies
View Related
Jul 26, 2012
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:
=[fname] & " (" & [pname] & ") " & [lname]
=[lname] & ", " & [pname]
(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.
View 2 Replies
View Related
Jul 4, 2015
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.
View 1 Replies
View Related
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.
e.g
Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]
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.
How do I go about this?
TIA
View 6 Replies
View Related
Mar 19, 2005
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!!
View 2 Replies
View Related
Jun 13, 2015
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
PHP Code:
Function ReadInbox()
Dim TempRst As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim Atmt As Outlook.Attachment
Dim InboxItems As Outlook.Items
[Code] ....
View 1 Replies
View Related
Nov 5, 2013
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.
View 5 Replies
View Related
Nov 29, 2013
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.
View 1 Replies
View Related
