Tables :: Update Table With Certain Parameters On Monthly Basis
Feb 25, 2014
I have a table with certain parameters in, and I need to update it in a monthly basis with new costs. Basically, its a list of component costs which change on a monthly basis, and the query/report needs to pull the latest version. But, the old costs need to be kept for legacy/comparison purposes.
TblMbM Layout (titles)
-Unique_ID
-title
-code
-Feb Cost
-March Cost
At the moment, there are no costs in the march column, but i know next week i will need to update tblMbM with the latest costs.
View Replies
ADVERTISEMENT
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Mar 10, 2015
I am looking automation through VBA for the calculation of HR employees turnover on monthly basis, i have a query showing fields ..
employee name
joining date
department
section
employee left (Yes/No)
Left date
Now I am looking for;
i) Opening strength: Total employees strength through joining date with criteria of department and section wise at month start
ii) Closing Strength: Total employees strength through joining date with criteria of department and section wise at month end (subtracting the employees left through employee left field y/n)
iii)Left Employees: number of employees left at the end of each month with the criteria of department and section wise
iv) Turnover: The number of left employees will be divided by the dividend of (sum of opening & closing employees divided by 2)
View 2 Replies
View Related
Jul 23, 2013
Me having a db which is having a student table which is keeping all student info, second i have a Van table which is keeping van info. I wanted to know how it is possible to calculate monthly van fee for each student on monthly basis which db do it itself based on system date/time and calculate monthly required fee for each student. I also would like to know that if a student do not pay in a month then checking the next month or checking unpaid student through date criteria it displays all due amount may be in sum.
View 2 Replies
View Related
Sep 12, 2013
I'm trying to make a graph of Monthly profit and loss
I've got 2 tables: Invoices and Purchases.
Invoices contains the fields:
InvoiceTotal and InvoiceDate
Purchases contains the fields:
PurchaseTotal and PurchaseDate
I know I need to take one from the other to create my profit figure but what I can't figure out is how to incorporate the dates.
InvoiceDate and PurchaseDate might be different but both occurred in the same month, so I would like to sum my PurchaseTotal and InvoiceTotal during this month.
so the query I'm looking for would look like:
| Month | SumofPurchaseTotal | SumofInvoiceTotal | Profit |
I would then graph month and profit so my company could compare it's monthly performance ...
View 2 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Feb 4, 2013
I am tracking extreme useage on account numbers. I've set up a table to add the information on a daily basis.
Currently the table has the following Fields:
Report_Date / Account / Usage / Sent / Received / Comments / Notes
(the Sent and Received fields are Y/N)
I've gotten a report to send working as I want, but I am running like 6 queries to put all the data together correctly. For example; if an account was flagged today and sent today I do not want to send another notice for the next 5 days; but I still want to log the data in the table for historical reasons... Again, this all works. Just that it is not pretty..
The part that I keep fighting is the responses. When I receive a response I want to log that. Right now, I check the Received flag, and add in any comments the customer sends back. This is easy. But I also want to print that information out on the report - just the latest information. For example, if I send a notice out on 11/1/12, 11/18/12, 12/14/12, and 1/10/13 and I have responses back from all of these logged in the table, when this account number gets printed again - say today - I want to add to that report JUST the comments from the 1/10/13 report.
View 4 Replies
View Related
Dec 29, 2014
I want to "lock" a record in a table so that it is read only and cannot be accessed/edited/deleted. Is it possible to lock a single record in this way on a permanent basis? It's actually the first record in the table.
View 5 Replies
View Related
Jun 11, 2013
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
View 2 Replies
View Related
Sep 23, 2007
I hope I can convey what I am trying to figure out. I am at a loss right now. I have a form where I input personnel information. One of the fields is for how many months experience they have with the program. What i would like is for this record to update itself every month. for example, if they initially had 2 months experience, I would enter 2. Every month therafter, that number with change to 3, 4, 5...etc. Not everyone comes to our section with the same experience, so most will have a different starting number.
This information would be seen on the personnel form, and in a report to show experience levels. Other than those two, it is not called upon.
Any suggestions would be so helpful!
Thank You!!
View 3 Replies
View Related
Jan 8, 2008
Hello;
I'm curious if this is possible:
I have a table set up with a list of items followed by a column for each month's quota that I manually update. I run a query from my production table and quota table that will list all my products I have a quota on followed by how many of each were produced in that particular month. In the query I point to the quota table's current month and the data from the production table is pulled by the following:
Produced: Sum(IIf([production]![status]=4 And [production]![fix date] Between #01-Jan-08# And #31-Jan-08#,1,0))
How can I write this same information to pull only Jan08 information without using the "Between #X# and #X#"?
In the same way, I would need to point to Jan08, Feb08, etc.. columns in my quota table automatically.
Any help would be GREATLY appriciated
View 8 Replies
View Related
May 21, 2013
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
View 1 Replies
View Related
Feb 25, 2014
I have a table called External Audit.
I want each record to get a unique sequential number on the basis of Type of Audit.
Type of Audit column has 3 options OB, INR, MINR to choose from
If i select type as OB then the notification number should start from 1 and carry on numbering it whenever I select the same value.
When I select different type eg: INR then it should again start number on the basis of type. INR1 INR2, INR3 etc.
Is sequential number is the right word to use for this function ?
View 10 Replies
View Related
Jun 8, 2005
Every month for my construction company, I receive a pay application that consists of the following:
1) about 100 "Line Items" (each one being a different type of work performed)
2) a Subcontractor associated with each line item (a line item may have only 1 Sub, but a Sub may be listed multiple times for different line items)
3) Amount of work performed (in $$) on that line item by that particular Sub during that pay period.
My boss wants a db to track all of this info. She thinks the best way would to create a new table for each Pay Application, every month. Basically, we would be starting from scratch every month with a blank table. But, I think it would work better if the months were linked to each other in some way.
I could have a field called "Pay_App" and enter 1 or 2, etc. on every row for every line item every month. But, then each month, I would be entering a 1 (or whatever the month is) for every single line item and that seems stupid. I want to be able to pull up the entire pay schedule for a particular Sub (all of his payments throughout the entire job). Is there a way to link the different pay application tables once the data has been entered? Or is there an easier way to do this? I would appreciate any help that can be provided. Thanks.
View 5 Replies
View Related
Mar 12, 2013
i have a table on my database from which i generate a monthly report by running a query. when i run the query (date from to date to) i have no problem. it shows me all the relevant records. when i want to generate a report, i input the start date and the closing date for the report. it then gives me an error (reserved error (-1524); there is no message for this error.) i have gone into the table and basically isolated all the corrupted entries. there are 359
what i need to know is how to repair these entries without losing the information. im new to access and this is a simple(kinda) database which i set up to keep track of what the staff in my department are doing on a daily basis.
im running access 2007.
View 7 Replies
View Related
Sep 14, 2012
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus
5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666
View 1 Replies
View Related
Oct 28, 2014
I am trying to update a record value in a table with a user supplied value.A user logs a test against a serial number (there are multiple tests per serial so the tests are numbered) and logs how many repetitions will be performed in that test. If a failure occurs and they have to abort the test, I want to change the number of repetitions from what was supposed to run, to what was actually run. So the user enters how many tests were actually run and the code is supposed to find the matching serial number and test number in my Test Runs table and replace the Run Scripts value with the user supplied update.
My code runs without error but the record doesn't update. I've changed my table value types from numbers to text without result, I'm thinking there might be a problem with quotation marks? So it's searching for serial "123-321" instead of 123-321?
My code is below:
serial = Forms("Enter Run Failure").Controls("Serial")
runNum = Forms("Enter Run Failure").Controls("Run Number")
abortNum = Forms("Enter Run Failure").Controls("txtAbort")
Dim SQL As String
[code]....
View 4 Replies
View Related
Apr 30, 2014
I need to be able to count how many fields per date. I've tried several ways to add this to my query, but nothing seems to combine the dates, it just gives me nothing or 1 as the count for every line even when it is the same date......
View 3 Replies
View Related
Dec 26, 2012
Warehouse inventory system in access 2003. I have set up two primary tbls: Equipment and ETO (Equipment transfer order) both have an autofill primary key and the equipmentID is part of the ETO tble. There is a one-to many realationship from the equipment to the ETO. There are other tables (4) in the system but they are used as look-up tables and i'm not concerned about those.
Equipment Table layout: EquipmentID, ModelNumber, SerialNumber, Mfg, ProductName, CurrentLocation, Category. This table is used as the repository for all of the equipment that needs to be tracked and inventoried. It will not change much with except the CurrentLocation changing as the stuff is shipped around (in Store, In Warehouse) and of course when new equipment is being added.
ETO table layout: ETOID, ETONumber, FromStore, ToStore, OriginStore, ETODate, ModelNumber, SerialNumber, MfgID, ProductName, LocationID, CategoryID, EquipmentID. This table will be used (I hope) to basicly track the equipment as it moves from store to store to warehouse and back out again.
Process: Locate a peice of equipment by either SerialNumber or ModelNumber in the Equipment Table, change the location depending on it's status (coming in, going out) then i need to somehow update the ETO table with that ModelNumber, SerialNumber, Mfg, category, etc..But put in the FromStore, Tostore, OriginStore, ETODate, etc..
I have tried both the update query and append query from both tables but I can't get the results i need.
View 14 Replies
View Related
Jan 2, 2014
I have a database with 7 related tables. Now the main table is called STUDENT. I am essentially creating a report card system and have broken down each part of the report into another table. Now to make it easy for teachers I have created a form which they can search for a class and in a datasheet all the students plus their ID numbers come up along with the spaces for them to enter their grades etc.
The problem I have is this. How can I get access to look for any updates from the STUDENT table and apply this to the other tables as it would not do this automatically, even though they are linked one-many I have copied the data from the main STUDENT table into each other table just so theres a reference for a teacher. Is there any magic button or something that I can make to do this so all extra student names and IDs are added to all other tables?
View 3 Replies
View Related
May 23, 2012
How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.
View 1 Replies
View Related
Nov 21, 2013
I have an Access 2007 file that is 'split' so the tables are centrally stored on the server.
How do I update one the linked tables stored in the back-end file with a table from another Access file?
If I import the 'new' table it is not 'linked' - it is embedded with the Access file I am working on.
What is the correct way of doing this without corrupting the data?
View 3 Replies
View Related
Aug 15, 2013
I have an Employee Records table with 29k records that I originally imported from an HRIS system. Lets call this table ORIGINAL table.Each week I export a new Employee Records table from the HRIS system as it contains the latest updated information. IE - New hires, terminations, rate changes, etc. Lets call this table NEW table. It likely contains new records as well as we hire 100+ people per week.
In the ORIGINAL table, I have added fields that are not included in the NEW table. I need to update the records / fields in the ORIGINAL table with the NEW table, but need to keep the ADDED fields.Originally my plan was just to overwrite the ORIGINAL table with the NEW table until I added the additional fields to the ORIGINAL table. I thought that I could export the added fields in the ORIGINAL table, import the NEW table, and run and update query based on the EE ID#.
View 2 Replies
View Related
Dec 26, 2006
Greetings all!!!
I have a database containing a table of employees and their rank. I want to record monthly details regarding these people: hours, performance, etc. Their rank can change dynamically from month to month. They may be a Supervisor for a month, and I want to change this value on a monthly basis. But I want to generate a Table which shows all employees belonging to each of the eight departments, but which has empty fields enabling me to record these monthly values. I then want to be able to pull up that data at any time to see these values for any given month in the past. I also want to add the values of each field and total them for the various departments providing monthly and yearly values on a departmental and organizational (grand total) level.
The employee names and their respective departments rarely change. I don't want to have to select their names (about 125 of them) every month to create a new blank form in which to put the monthly data. I want to have perhaps a button which creates the records and puts the names in for (1) department, (2) year, and (3) month, but that adds the fixed fields ready to put the data in. This data is required every month and the field names will never change. I'm sure the solution is programmatically simple, but I seem to have a mental block over the issue.
I hope that someone casts some light on this.
Do I use a Create Table query? (The data can remain in one table.) I would like to do it programmatically. My VB is pretty good, but, it seems, not good enough.
:confused:
View 7 Replies
View Related
Aug 8, 2013
I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].
But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?
View 3 Replies
View Related
Aug 6, 2014
Having issues with getting auto update of Yes/No field dependant on todays date.
When users open the form the code runs through and checks all entries and updates the records if either of the dates in two separate fields is less than today. The issue I have is when some users are already using this form a second user cannot open it. Error shows other user information as having the form locked and code halts at highlighted spot below. Is there another way of ensuring this field is updated to Yes or No if the date in the field is less than current date.
Code:
Private Sub Form_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
[Code] ....
View 3 Replies
View Related
